Log et al – Peter Curd

An irreverent peek into the inner rumblings of Peter Curd

Jan

28

How to get SQL Server Table Row Counts fast and efficiently

By pcurd

SQL Server Central ran an article this morning about using a system DMV to get table row counts without doing a table scan (so it’s VERY fast and has no performance hit).

I decided to test this on my test server, a virtual machine running on a Dell R900. I ran the queries four times, discounting the first time to be sure all were in memory, and averaging the last three for the following values.

On a 74,000 row table the DMV technique took 4ms and 32 logical reads against 4ms and 403 logical reads for a count(*) – that’s 12 times less reads.

On a 410,000 row table the DMV technique took 3ms and 33 logical reads against 25ms and 1234 logical reads for a count(*) – that’s 37 times less reads and 8 times quicker!

In fact, getting the row count of every table in my database (296 tables) took only 14ms and 774 logical reads – faster and with less reads than a count(*) just on the 410,000 row table!

See the link below for the article but to summarise the article examples of implementation are shown below.

The following code gets the row count of every table in the current database:

SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY o.NAME

The following code is an example of a stored procedure where you could pass a table name:

CREATE PROCEDURE [dbo].[usp_DMVRowCount]
@objectToFind varchar(255)
as
SELECT ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
and o.name = @objectToFind

I hope this helps someone!

The original article, to whom all credit should go, is at http://www.sqlservercentral.com/articles/T-SQL/67624/ (you will need to be a member of SQL Server Central to read, but I strongly encourage that anyway – it’s free!)

Enhanced by Zemanta

Leave a comment