How to improve the speed of count(*) with MSSQL

Using count(*) on large table take over a minute. On an extremely large table, it took over 7 minutes!

The standard way is to issue:
select count(*) from XYZ;
go

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE adventureworks;
  2. go
  3.  
  4. DBCC dropcleanbuffers;
  5. SELECT COUNT(*) AS 'Sequential count' FROM Sales.SalesOrderDetail;
  6. go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Sequential count
----------------
121317

(1 row(s) affected)

Table 'SalesOrderDetail'. Scan count 1, logical reads 229, physical reads 1, read-ahead reads 227, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

The MS SQL Server output gives:

  1. DBCC execution completed is the dbcc dropcleanbuffers;. Removes all the clean buffers from the buffer pool.
  2. Sequential count, select count(*) found 121,317 rows:
    1. 229 logical reads.
    2. Only 1 physical read because the 227 blocks are contiguous.
  3. The last (1 row(s) affected) is from displaying the stats.

Alternative to select count(*)

  1. SELECT ROWS AS 'Rows from stats'
  2. FROM sysindexes
  3. WHERE id = OBJECT_ID('Sales.SalesOrderDetail') AND indid = 1;
  4. go
Rows from stats
---------------
121317

(1 row(s) affected)

Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


(1 row(s) affected)

The MS SQL Server output gives:

  1. Looking up the number of rows from sysindex gave 121,317 rows. The same number as count(*).
    1. 2 logical reads.
    2. 0 physical read, because it's already in cache.
  2. The last (1 row(s) affected) is from displaying the stats.
  1. You need to have a primary key on the table.
  2. There is no guarantee that the number of rows from sysindexes will give you an accurate number of rows.
  3. DBCC UPDATEUSAGE databasename will update the statistics.
  4. Auto Update Statistics as a database option will also do the job.
  5. This will give you a good ball park figure.
  6. This will give you a bad ball park figure in a high volume environment.
  • If you need an exact/accurate number of rows, you will need to use: select count(*) from
  • If you need a selection criteria, you will need to use: select count(*) from XYZ where ...