Database Reference
In-Depth Information
Figure 3-11.
Cardinality Estimation and Memory Grants: Sort Warning
■
the new Cardinality estimator introduced in SQL Server 2014 uses a different algorithm when estimating
cardinality for values outside of the histogram. We will discuss this later in the chapter.
Note
The execution time of the queries on my computer is as follows:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 88 ms.
As you can see, the second query with the incorrect memory grant and
tempdb
spill is about five times slower
than the first one, which performs an in-memory sort.
■
Note
You can monitor tempdb spills with SQL profiler by capturing the
Sort Warning and Hash Warning
events.
Statistics Maintenance
As I already mentioned, SQL Server updates statistics automatically by default. This behavior is usually acceptable for
small tables; however, you should not rely on automatic statistics updates in the case of large tables with millions or
billions of rows. The number of changes that triggers a statistics update would be very high and, as a result, it would
not be triggered often enough.
