Database Reference
In-Depth Information
dynamically change the percentage a table needs to change before the statistics are
automatically updated. In very large tables, an automatic update of statistics can now be
triggered by a change of less than 1%. Using this option could result in significantly
improved performance for situations where you have very large tables.
Tip
Information with regard to trace flag 2371 in SAP environments can be found in
the following articles:
http://scn.sap.com/docs/DOC-29222
and
automatic-update-statistics-in-sql-server-traceflag-2371.aspx
.
Caution
Database statistics are complied against each table in your database. When SQL
Server updates statistics, this information is recompiled. Automatic statistics
update and trace flag 2371 may cause statistics to be updated more frequently
than necessary. So there is a tradeoff between the performance benefit of doing
statistics updates regularly and the cost of recompiling the statistics. The cost of
doing this operation is not free, and in rare cases it can have a detrimental impact
on performance. If you find any performance problems correlating to the periods
of time where statistics are being updated, then you may wish to control when
statistics updates occur. For the majority of customers we deal with, around 80%
experience positive performance improvements and no detrimental impact by
using the dynamic automatic updates for database statistics. Refer to
Updating Database Statistics Using a Maintenance Plan
The second method for addressing out-of-date statistics is by using a maintenance plan.
If you need more control over when database statistics updates occur, you can schedule
a maintenance plan task for your databases. Ideally, the maintenance plan would be
scheduled to happen when it would have the least impact on the database, and run only
as frequently as needed. To determine when and how often it should run requires you to
know your database workload patterns and to monitor query plan execution efficiency.
Depending on your database, you may wish to schedule it to initially happen daily and
adjust the schedule based on observed performance.
Figure 6.10
shows the Update
Statistics option in the Maintenance Plan Wizard. A full step-by-step example is
provided in
Chapter 11
.
