Speeding up the Performance of Table Counts in SQL Server 2005

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.

Continues…

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |