How to improve the performance of total records statistics in SQL Server 2005

When we want to count the total number of records in a datasheet, we use the T-SQL function count (*). If this function is executed in a large table containing millions of rows, it can take a long time to return the total number of records in the entire table, which results in a decrease in query performance.

First, the general approach: using the count () function

Each database administrator knows how to use the count (*) function. When SQL Server executes this function, a complete scan of the index/table is required in order to return the row count of the total table. It is therefore recommended that DBAs avoid using aggregate function count (*) for the entire table as it affects the performance of the database.

Let's look at an example from a AdventureWorks database.

Execute the following query statement in Query Analyzer:

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

After the Query Analyzer executes, there are 121317 rows displayed.

When we click the "Show Estimated execution plan" icon on the toolbar of SQL Server Management Studio, we can get the following chart:

Figure 1:count (*) function Execution plan

From right to left, we can see the execution of the SQL statement:

L An index scan of the entire table in the first step is a time-consuming process (accounting for 81%).

L The application of flow aggregation in the second step is also more time-consuming (19%).