Databases Reference
In-Depth Information
Some SQL Server features, such as table variables, do not use statistics, so you might
want to consider using a similar feature like temporary tables if you're having perform-
ance problems related to cardinality estimation errors. In addition, for complex queries
that are not performing well because of cardinality estimation errors, you may want to
consider partitioning the query into several steps while storing the intermediate results
in temporary tables. This will allow SQL Server to create statistics on the intermediate
results, which will help the Query Optimizer to produce a better execution plan.
UPDATE STATISTICS with ROWCOUNT,
PAGECOUNT
In this section I will show you the undocumented
ROWCOUNT
and
PAGECOUNT
options of
the
UPDATE STATISTICS
statement, which can help you in cases where you want to see
which execution plans would be generated for huge tables (with millions of records), but
then test those plans in small, or even empty, tables. As you can imagine, these options
can be helpful for testing in some scenarios where you may not want to spent time or disk
space creating big tables.
By using this method you are essentially tricking the Query Optimizer, as it will generate
execution plans using cardinality estimations which are made as if the table really had
millions of records, even if your table is actually tiny. Note that this option, available
since SQL Server 2005, only helps in creating the
execution plan
for your queries. Actually
running the query will use the real data in your test table which will, of course, execute
faster than a table with millions of records.
Using these
UPDATE
STATISTICS
options does not change the table statistics, only the
counters for the numbers of rows and pages of a table and, as I will show shortly, the
Query Optimizer uses this information to estimate the cardinality of queries. Finally,
before we look at examples, keep in mind that these are undocumented and unsupported
options, and should not be used in a production environment.
