Databases Reference
In-Depth Information
Figure 3-12:
Cardinality estimate with filtered statistics.
Let us now inspect the filtered statistics object by running the following statement:
DBCC
SHOW_STATISTICS
(
'Person.Address'
,
california
)
WITH
STAT_HEADER
Listing 3-43.
This will show the following output, (edited here to fit the page):
Name Rows Rows Sampled Filter Expression Unfiltered Rows
----------- ----- ------------- ------------------------ ---------------
california 4564 4564 ([StateProvinceID]=(9)) 19614
Listing 3-44.
Notice that the filter definition is shown on the Filter Expression field, and that the
Unfiltered Rows field shows the total number of records on the table when the filtered
statistics were created. Also note that, this time, the
Rows
column number is
less
than the
total number of rows in the table, and corresponds to the number of records that satisfied
the filter predicate when the statistics object was created. The filter definition can also be
seen on the
filter_definition
column of the
sys.stats
catalog view.