I was recently reviewing some of the Dynamic Management Views (DMVs) available since SQL Server 2005 and came across the interesting and useful sys.dm_db_partition_stats DMV. The documentation states that this DMV will return “page and row-count information for every partition in the current database”. In the Space Used by Filegroup and Log Space Used post I had already mentioned how using the sys.allocation_units and sys.data_spaces DMVs and the DBCC SQLPERF(LOGSPACE) command you can obtain some very useful information. Using the sys.dm_db_partition_stats DMV you can capture the same information, but for each table and index structure.
The query shown below will return information about user objects (object_id > 100), limiting this information to tables only (i.e. Heap and Clustered Indexes). The actual object name is retrieved by creating INNER JOINs with the sys.tables and sys.indexes DMVs. You will observe that in the case of a Heap the value of the name column (i.e. where index_id = 0) is NULL, which in the result set is replaced by an empty string. On the other hand, when the result set includes a row for index_id = 1 this means that the table contains a Clustered index. But you can read more about the topic in the MSDN article Table and Index Organization.
SELECT t.name as table_name, ISNULL(i.name, '') as index_name, i.index_id, i.type_desc, ps.row_count, ps.used_page_count, ps.in_row_used_page_count, ps.lob_used_page_count, ps.row_overflow_used_page_count FROM sys.dm_db_partition_stats ps INNER JOIN sys.tables t ON t.object_id = ps.object_id INNER JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE ps.object_id > 100 AND i.type_desc IN ('CLUSTERED', 'HEAP') ORDER BY t.name, i.index_id;
If you want to return information about NonClustered Indexes just replace the filter with the one below:
AND i.type_desc = 'NONCLUSTERED'
The values returned by the used_page_count, in_row_used_page_count, lob_used_page_count, row_overflow_used_page_count columns indicate the number of pages. A quick reminder; the size for each page in SQL Server is 8KB so in order to obtain the actual sizes you’d have to modify the query as shown in the next sample.
SELECT t.name as table_name, ISNULL(i.name, '') as index_name, i.index_id, i.type_desc, ps.row_count, ((ps.used_page_count* 8)/1024) AS [used_page_count_MB], ((ps.in_row_used_page_count * 8)/1024) AS [in_row_used_page_count_MB], ((ps.lob_used_page_count * 8)/1024) AS [lob_used_page_count_MB], ((ps.row_overflow_used_page_count * 8)/1024) AS [row_overflow_used_page_count_MB] FROM sys.dm_db_partition_stats ps INNER JOIN sys.tables t ON t.object_id = ps.object_id INNER JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE ps.object_id > 100 AND i.type_desc IN ('CLUSTERED', 'HEAP') ORDER BY t.name, i.index_id;
As you can see, this information can provide an insight into how much data is being stored by each use table structure and the respective indexes. This, if included in a data collection solution which stores the results in a DBA Data Warehouse (let’s call it that…), over time can show patterns of data growth. Such information can then be plotted into graphs and, for example, can be use to forecast storage requirements. A little thing that can help reduce the strain on a DBAs life!