Background:
Sometimes you need a count of rows on a table , I typically find myself looking for row counts to verify replication is caught up or to confirm the size of a table before I use SSIS to move a lot of data from one table to another so I can get an idea of the time it will take.
Sometimes when you have a lot of activity on a table and you try to use the standard count queries shown below the result never comes back.
This got me to think, is there a better way? After some searching I found a query that seems to work.
Setup and Code:
The typical way to get a count of rows in the table.
SELECT COUNT(1) FROM tbl WITH (NOLOCK) ... SELECT COUNT (*) FROM tbl WITH (NOLOCK)...
The Plan in this case looks like this:
You can see it is very serial and does an index Scan of the entire Primary Key on the table for 99% of the batch.
Resolution:
I have found this code to be much faster since it uses system management views and not the table directly to derive the count.
Some people have noted that this method may not be 100% accurate all the time but from my testing it has done well so far. You should try it and see if it works for you.
SELECT CAST(p.rows AS float) FROM sys.tables AS tbl INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2 INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id WHERE ((tbl.name=N'tbl' AND SCHEMA_NAME(tbl.schema_id)='dbo'))
In this case the execution plan is much different from the previous select. You will notice it is branched much more, it also returns quickly since it uses system views which should already have aggregate data on the table.
Information on Terms: