MSSQL 浅析查询表大小

一、概述

在SQL Server数据库中，我们常常需要查询某个表或者某个数据库的大小，以便更好地管理和优化数据库的性能。本文将介绍几个查询表大小的方法，以及一些常用的查询系统信息的SQL语句。

二、查询表大小

1. 使用系统存储过程sp_spaceused

系统存储过程sp_spaceused可以返回表的信息，包括表和索引占用的空间、未使用的空间和行数等。

EXEC sp_spaceused 'TableName';

其中，'TableName'是你要查询的表的名称。这个语句将返回如下的结果：

| 名称 | 行数 | 空间(total) | 空间(未使用) |索引空间 | 未使用空间（索引）|

| ---- | ---- | ---- | ---- | ---- | ---- |

|TableName | 100000 | 2048 KB | 8 KB | 400 KB | 200 KB|

通过这个表格，我们可以得知该表的行数、总空间、索引空间等信息，并且提醒我们有可能需要重新构建索引或者清理未使用的空间。

2. 查询sys.objects信息表

通过查询sys.objects信息表，我们可以得到数据库中每个表的大小。

SELECT 
    [TableName] = t.NAME, 
    [RowCount] = SUM(p.rows), 
    [TotalSpaceKB] = SUM(a.total_pages)*8, 
    [UsedSpaceKB] = SUM(a.used_pages)*8,
    [UnusedSpaceKB] = (SUM(a.total_pages) - SUM(a.used_pages))*8 
FROM 
    sys.tables t 
INNER JOIN 
    sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id 
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.NAME, 
    i.object_id, 
    i.index_id, 
    i.name 
ORDER BY 
    [TotalSpaceKB] DESC;

执行这个SQL语句，我们可以得到如下结果：

| TableName | RowCount | TotalSpaceKB | UsedSpaceKB | UnusedSpaceKB |

| ---- | ---- | ---- | ---- | ---- |

| SalesOrderHeader | 31,465 | 17,960 | 13,808 | 4,152 |

| SalesOrderDetail | 121,317 | 16,896 | 12,968 | 3,928 |

| AWBuildVersion | 1 | 16 | 8 | 8 |

| SalesPerson | 17 | 8 | 8 | 0 |

从这个表格中，我们可以看到每个表的行数和使用的空间、总空间、未使用的空间等信息。

3. 查询sys.dm_db_partition_stats DMV

DMV是动态管理视图，它提供了有关当前状态的信息。通过查询sys.dm_db_partition_stats DMV，我们可以得到表的大小、索引的大小以及行数等信息。

SELECT 
    OBJECT_NAME(object_id) TableName, 
    SUM(reserved_page_count) * 8.0 / 1024.0 AS TotalSizeMB,
    SUM(CASE WHEN (index_id < 2) 
        THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
         ELSE lob_used_page_count + row_overflow_used_page_count END) * 8.0 / 1024.0 AS DataSizeMB, 
    SUM(used_page_count - CASE WHEN (index_id < 2) 
          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 
          ELSE lob_used_page_count + row_overflow_used_page_count END) * 8.0 / 1024.0 AS IndexSizeMB, 
    SUM(row_count) AS [RowCount]
FROM 
    sys.dm_db_partition_stats 
GROUP BY 
    object_id 
ORDER BY 
    TotalSizeMB DESC;

执行这个SQL语句后，我们可以得到如下结果：

| TableName | TotalSizeMB | DataSizeMB | IndexSizeMB | RowCount |

| ---- | ---- | ---- | ---- | ---- |

| SalesOrderDetail | 244.96 | 231.141 | 13.819 | 121317 |

| SalesOrderHeader | 100.25 | 84.797 | 15.452 | 31465 |

| Product | 30.91 | 28.673 | 2.238 | 504 |

| SalesOrderHeaderSalesReason | 25.23 | 24.225 | 1.009 | 1,838 |

从这个表格中，我们可以看到表的总大小、行数和数据、索引的大小等信息。

三、查询系统信息

1. 查询数据库性能

在SQL Server中，我们可以使用sys.dm_os_performance_counters DMV查询数据库的性能。

SELECT 
    [object_name], 
    [counter_name], 
    [instance_name],
    [cntr_value] 
FROM 
    sys.dm_os_performance_counters 
WHERE 
    [object_name] LIKE '%:Transactions%'
    OR [object_name] LIKE '%Access Methods%'
    OR [object_name] LIKE '%Locks%'
    OR [object_name] LIKE '%Transactions%'
    OR [object_name] LIKE '%Latch%'
ORDER BY 
    [counter_name], 
    [instance_name];

这个SQL语句将返回数据库中每个事务、访问方式、锁、事务和闩锁的计数器。通过这些计数器，我们可以得到数据库的性能信息，例如：事务总数、锁等待时间、物理读取和逻辑读取等。

2. 查询数据库配置

我们可以使用sys.configurations信息表查询数据库的配置信息。

SELECT 
    [name], 
    [value] 
FROM 
    sys.configurations 
WHERE 
    [name] LIKE '%max%'
ORDER BY 
    [name];

这个SQL语句将返回数据库相关的配置信息，例如：最大工作线程数、最大并行度、锁超时时间等。

四、总结

本文介绍了几种查询表大小和系统信息的SQL语句，包括使用系统存储过程sp_spaceused、查询sys.objects信息表和查询sys.dm_db_partition_stats DMV。此外，我们还介绍了查询数据库性能和查询数据库配置等常用的SQL语句。通过这些SQL语句，我们可以更好地管理和优化SQL Server数据库的性能。