Statistics - Pro SQL Server Internals - page 63

Figure 3-9. Execution plan for the query selecting rows with the 999 prefix after a statistics update
As you can see, incorrect cardinality estimations can lead to highly inefficient execution plans. Outdated statistics
are, perhaps, one of the most common reasons for incorrect cardinality estimations. You can pinpoint some of these
cases by examining the estimated and actual number of rows in the execution plans. The big discrepancy between
these two values often indicates that statistics are incorrect. Updating statistics can solve this problem and generate
more efficient execution plans.
Statistics and Query Memory Grants
SQL Server queries need memory for execution. Different operators in the execution plans have different memory
requirements. Some of them do not need a lot of memory. For example, the Index Scan operator fetches rows one by
one and does not need to store multiple rows in memory. Other operators, for example the Sort operator, need access
to the entire rowset before it starts execution.
SQL Server tries to estimate the amount of memory (memory grant) required for a query and its operators
based on row size and cardinality estimation. It is important that the memory grant is correct. Underestimations and
overestimations both introduce inefficiencies. Overestimations waste SQL Server memory. Moreover, it may take
longer to allocate a large memory grant on busy servers.
Underestimations, on the other hand, can lead to a situation where some operators in the execution plan do not
have enough memory. If the Sort operator does not have enough memory for an in-memory sort, SQL Server spills the
rowset to tempdb and sorts the data there. A similar situation occurs with hash tables. SQL Server uses tempdb if needed.
In either case, using tempdb can significantly decrease the performance of an operation and a query in general.
Let's look at an example and create a table and populate it with some data. Listing 3-8 creates the table
dbo.MemoryGrantDemo and populates it with 65,536 rows. The Col column stores the values from 0 to 99, with either
655 or 656 rows per value. There is a nonclustered index on the Col column, which is created at the end of the script.
As a result, statistics on that index are accurate, and SQL Server would be able to estimate correctly the number of
rows per each Col value in the table.
Listing 3-8. Cardinality Estimation and Memory Grants: Table creation
create table dbo.MemoryGrantDemo
(
ID int not null,
Col int not null,
Placeholder char(8000)
);