Select count(*) Performance analysis with NonCluster Index

There is a common perception that count(*) is not a optimized way of getting the number of records in a table. In this article we will be examining the same and finally we will conclude with our finding.

For analysis lets take the sales.Store table of AdventureWork database, lets see first how many indexes are there in place for Sales.Store table.

select OBJECT_NAME(object_id),name,index_id,type_desc from sys.indexes where object_id= OBJECT_ID('Sales.Store')

      name                      index_id    type_desc

------------------------------------------- -------------

Store PK_Store_BusinessEntityID 1           CLUSTERED

Store AK_Store_rowguid          2           NONCLUSTERED

Store IX_Store_SalesPersonID    3           NONCLUSTERED

Store PXML_Store_Demographics   256000      XML

(4 row(s) affected)

 Here we can see there are total of four index available on Sales.Store table.

Let’s now fire DBCC IND command for each index to find the number of page these indexes are span.

--Index id 1. PK_Store_BusinessEntityID

DBCC IND ('Adventureworks2008', '[Sales].[Store]', 1);

  Page Count : 103

 

--Index Id 2. AK_Store_rowguid

DBCC IND ('Adventureworks2008', '[Sales].[Store]', 2);

  Page Count : 4 

 

--Index Id 3. IX_Store_SalesPersonID

DBCC IND ('Adventureworks2008', '[Sales].[Store]', 3);

  Page Count : 4 

 

--Index Id 256000. PXML_Store_Demographics

DBCC IND ('Adventureworks2008', '[Sales].[Store]', 256000);

  Page Count : 66 

Since IX_Store_SalesPersonID has lowest pagecount, smallest index among all, if it gets picked up for finding total row count, it would be optimized one.

Let see which index count(*) will pick for getting the total number of rows in Sales.Store table.

select COUNT(*) from Sales.Store

So here we saw for getting count(*) optimizer picked IX_Store_SalesPersonID index.

Conclusion: Thus, when using COUNT(*) verify whether correct indexes are being used and do not believe that COUNT(*) will use Clustered index.   

If you punish a programmer with code he will reach to dreamland, dare to punish

Post Reference: Vikram Aristocratic Elfin Share