You should see 44,282 reads, which is far more than
the number of pages in the table. But if you update
the statistics, as the statement below does, the optimizer
will choose a better plan that uses a clustered
index scan.
UPDATE STATISTICS dbo.details
GO
SELECT * FROM dbo.details
WHERE SalesOrderID > 75000
GO
Trace Flags
SQL Server 2005 SP1 introduced the T-SQL trace
flag 2389, which detects when the statistics on a
particular index are repeatedly ascending. After
three consecutive statistics-updating operations
have noted ascending values exceeding the previous
upper boundary of key values, SQL Server internally
marks the index as an ascending index. You
can use the undocumented trace flag 2388 to show
you whether an index's leading column has been
determined to be ascending. This trace flag changes
the output of the DBCC SHOW_STATISTICS
command to give you a historical look at the most
recent statistics-updating operations. In this case, the
only column in the DBCC SHOW_STASTISTICS
output you'll be interested in is the last one, called
Leading column Type. As with all undocumented
trace flags, keep in mind that the behavior of trace
flag 2388 isn't guaranteed to continue to work as
described in any future version or service pack, and
no further information is available about what the
values mean or how they're generated.
Run the code in Listing 1 again to recreate the
dbo.details table and its indexes. Then run Listing
2 to turn on both trace flags, and perform three
UPDATE operations, three SELECT operations,
and three statistics updates. (If you look at the
plans for the SELECT statements, you should see
that they're all using an index seek inappropriately
because of bad statistics information.) Then run
DBCC SHOW_STATISTICS to examine the statistics
for the ASCENDING state. You should see
the values that Table 1 shows in the last column of
the DBCC SHOW_STATISTICS output.
Now that the index is branded as "ascending,"
when you run one more data update, as follows, the
statistics should be updated automatically:
UPDATE dbo.details
SET SalesOrderID = 82000
WHERE SalesOrderID < 56000
GO
If you run the following SELECT, you'll see that
the optimizer chooses the better plan even though
statistics aren't manually updated. The number of
reads should be 1,258, which indicates a clustered
index scan.
SELECT * FROM dbo.details
WHERE SalesOrderID > 81000
Another trace flag, 2390, introduced in SQL
Server 2005 SP1, enables the same behavior as trace
flag 2389 - but only in cases where the optimizer
doesn't know whether the leading index column is
ascending. So if you use both 2389 and 2390, your
statistics should be automatically updated much more
often. You should never use trace flag 2390 alone, since
doing so would mean that statistics would be updated
only when the ascending nature was unknown and not
when the column was known to be ascending.
If you're interested in exploring how to use trace
flags 2389 and 2390, see the Microsoft articles "FIX:
You may notice a large increase in compile time when
you enable trace flags 2389 and 2390 in SQL Server
2005 Service Pack 1" (http://support.microsoft.com/?kbid=922063) and "FIX: SQL Server 2005
may not perform histogram amendments when
you use trace flags 2389 and 2390" (http://support.microsoft.com/?kbid=929278), which discuss problems
with the flags and fixes for them. As with any
Microsoft hotfix, you should apply the fix only if
you can definitely establish that the bug is negatively
affecting your applications.
Manual Intervention Still
Required
SQL Server's ability to automatically update statistics
is useful, but you can't assume that just because the feature is enabled, you'll never need to
run the UPDATE STATISTICS command.
Two new trace flags in SQL Server 2005 SP1
can help the automatic statistics-updating
functionality gather more information about
your data distribution. Nonetheless, even
with the trace flags, a DBA still needs to
monitor query performance, spot when queries
are performing less than optimally due
to out-of-date statistics, and either manually
update the statistics or set up a job schedule
to update them more often than the auto update
feature allows.
Download the associated code.
End of Article
Prev. page
1
[2]
next page -->
