Database Reference
In-Depth Information
smart software is impacting the economics of this equitation. We will discuss in
more detail later in this chapter how using flash storage local to the server can
allow you to consolidate more databases per host with less memory per database
without degrading performance to an unacceptable level.
Updating Database Statistics
The SQL Server Query Plan Optimizer uses statistics compiled from tables to try and
estimate the lowest cost execution path for a given query. By default, statistics are
updated automatically at defined thresholds (refer to
http://msdn.microsoft.com/en-
since statistics were last gathered.
The Query Optimizer's cost-based algorithm takes into account system resources such
as CPU and IO to calculate the most efficient query, and overall table size and
distribution of data. For example, it is better to join a three-row table to a million-row
table, than to join a million-row table to a three-row table.
The cost to performance if the statistics are outdated and the impact on your storage can
be high. Outdated statistics cause suboptimal query execution paths that can result in
many more full table scans and therefore higher IO than would otherwise be required.
For large databases that have hundreds of millions or billions of rows in a particular
table, which can be common with SAP systems, the impact can be very severe.
Therefore, it is important that you have up-to-date statistics.
Caution
When you upgrade an existing database to SQL Server 2012, the statistics may
become out of date and result in degraded performance. To avoid this, we
recommend you update statistics immediately after upgrading your database. To
do this manually, you can execute sp_updatestats. Refer to
http://www.confio.com/logicalread/sql-server-post-upgrade-poor-query-
Server 2012 Administration
, published by John Wiley & Sons.
There are two primary methods to deal with the problem of outdated statistics impacting
your database and storage IO performance.
Trace Flag 2371—Dynamic Threshold for Automatic Statistics Update
The first method involves using trace flag 2371 by setting startup option -T2371 or
DBCC TRACEON (2371, -1). This is documented in Microsoft KB 2754171
(
http://support.microsoft.com/kb/2754171
). This trace flag tells SQL Server to
