A DMV a Day – Day 12

The DMV for Day 12 is sys.dm_db_partition_stats, which is described by BOL as:

Returns page and row-count information for every partition in the current database.

You can find some interesting information from sys.dm_db_partition_stats, including what is in the query shown today. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Table and row count information   
SELECT OBJECT_NAME(ps.[object_id]) AS [TableName], 
i.name AS [IndexName], SUM(ps.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i 
ON i.[object_id] = ps.[object_id] 
AND i.index_id = ps.index_id 
WHERE i.type_desc IN ('CLUSTERED','HEAP')
AND i.[object_id] > 100
AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'
GROUP BY ps.[object_id], i.name
ORDER BY SUM(ps.row_count) DESC;

This query as it is written above, will show you which tables in the current database have the most rows. This is useful information to know, especially if you are considering adding an index to a table, or simply doing index maintenance on a table. Knowing that a table has 500 million rows rather 500 thousand rows might cause you to take a different course of action.

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s