Statistics and Cost Estimation - Inside the SQL Server Query Optimizer - page 86

sets, then they should be included only once. As indicated in the previous example, the
estimated number of rows for the predicate ProductID = 870 alone, is 4,688 rows, and
the estimated number of rows for the predicate OrderQty = 1 alone is 68,024 rows.
The estimated number of records that belong to both sets is the AND 'ed predicate we saw
previously: 2,628.62 rows. So, the estimated number of rows for the OR 'ed predicate is
4,688 + 68,024 - 2,628.62, or 70083.4, as shown in the execution plan in Figure 3-8.
Figure 3-8: Cardinality estimation example using an OR 'ed predicate.
Statistics Maintenance
As mentioned already, the Query Optimizer will, by default, automatically update
statistics when they are out of date. Statistics can also be updated with the UPDATE
STATISTICS statement which you can schedule to run as a job during your database
maintenance window. Another statement commonly used, sp_updatestats , also runs
UPDATE STATISTICS behind the scenes.
There are two important benefits of updating statistics in a maintenance job. The first
is that your queries will use updated statistics without having to wait for the automatic
update of statistics to be completed, avoiding delays in the optimization of your queries
(although asynchronous statistics updates can also be used to partially help with this
problem). The second benefit is that you can use a bigger sample than the Query
Optimizer will use, or you can even scan the entire table. This can give you better