articles >> performance tuning >> Speeding up the Performance of Table Counts ...

Speeding up the Performance of Table Counts in SQL Server 2005

By : Mohamed Hassan Sep 10, 2008

Introduction
When we want to get the row count of a table, we use the transact-SQL function count (*).  In the case of large table which may contain millions of rows, it can take a long time to return the row count of the whole table, and that leads to slow performance for the query.

Count () function
Every DBA know how to use count (*) and know how suffering the performance.  SQL server makes a full index/ table scan to return the total table's row count. There was advice for the DBA to avoid using the aggregate function count () for the whole table because it affects the database performance.  When the following query is executed in query analyzer for the demo database AdventureWorks:

use AdventureWorks
go
select count (*) from Sales.SalesOrderDetail

The Query analyzer returns 121317 rows.

When we click the icon "display estimated execution plan" in the toolbar of the management studio for SQL server 2005, we get the following graph:


 
As we see in the figure from right to left, the following actions are applied:  

• Index scan for the whole table, which is time consuming process.
• Apply stream aggregate in the next step.

The New Method row_count ()
The information included in Object Catalog Views of SQL Server 2005: sys.partitions and  sys.allocation_units  are used to get the count of the rows of the whole table. This function can be used in SQL Server 2005. 

sys.partitions  View
sys.partitions View contains a row for each partition of all the tables and indexes in the database.  All tables and indexes in SQL Server 2005 contain at least one partition in that view, even if they are not explicitly partitioned.

The View includes the following fields which are used in the new method:

Field Name	Data Type	Description
partition_id	bigint	ID of the partition. Is unique within a database.
object_id	int	ID of the table to which this partition belongs. Every table is composed from  one partition at least.
index_id		ID of the index within the object to which this partition belongs. 0 : heap table 1: with clustered index
rows	bigint	number of rows in the partition.

 

sys.allocation_units
The sys.allocation_units view contains a row for each allocation unit in the database.

The View includes the following fields which are used in the new method:

Field Name	Data Type	Description
container_id	bigint	The container_id = sys.partitions.partition_id.
Type	tinyint	Type of allocation unit: 0 = Dropped 1 = In-row data (all data types, except LOB data types) 2 = Large object (LOB) data (text, ntext, image, xml) 3 = Row-overflow data

In the new UDF row_count,  [sys.partitions] view is joined with [sys.allocation_units] view. The filter of selection based on the following criteria: 

 

 

• [sys.allocation_units].type = 1 which retrieve only the in-row data except the large object (LOB) of type like text, ntext, image.
• [sys.partitions].index_id in (0,1): 0 is the heap table , 1 : Clustered table
• [sys.partitions].rows is not null

The UDF row_count is executed in every database , and is given a permission public. The UDF row_count is described in the next section.