Database Reference
In-Depth Information
Since the index is created on (
C1
,
C2
), the statistics on the index contain a histogram for the first column,
C1
, and
density values for the prefixed column combinations (
C1
and
C1
*
C2
). There are no histograms or density values alone
for column
C2
.
To understand how to identify missing statistics on a column with no index, execute the following
SELECT
statement. Since the auto create statistics feature is off, the optimizer won't be able to find the data distribution for the
column
C2
used in the
WHERE
clause. Before executing the query, ensure you have enabled “Include Actual Execution
Plan” by clicking the query toolbar or hitting CTRL+M.
SELECT *
FROM dbo.Test1
WHERE C2 = 1;
If you right-click the execution plan, you can take a look at the XML data behind it. As shown in Figure
12-31
, the
XML execution plan indicates missing statistics for a particular execution step under its
Warnings
element. This shows
that the statistics on column
Test1.C2
are missing.
Figure 12-31.
Missing statistics indication in an XML plan
The information on missing statistics is also provided by the graphical execution plan, as shown in Figure
12-32
.
Figure 12-32.
Missing statistics indication in a graphical plan
The graphical execution plan shows an operator with the yellow exclamation point. This indicates some problem
with the operator in question. You can obtain a detailed description of the warning by right-clicking the Table Scan
operator and then selecting Properties from the context menu. There's a warning section in the properties page that
you can drill into, as shown in Figure
12-33
.
Figure 12-33.
Property values from the warning in the Index Scan operator
