Skip to content

Trace Flag 2371 and Statistics

October 22, 2012

One of the issues that I see published often on forums like SQLServerCentral is the advice to update statistics on your tables if you have strange performance issues, or sudden changes in performance. Statistics are important for the query optimizer, and you should understand the basics of how they work.

However there’s a problem with statistics. They get out of date. SQL Server will automatically update statistics, but it doesn’t do this constantly. It does it after 20% of the table changes (by default). If you have 1000 rows, that means 200 rows changed (or added) can trigger the update. If your table has 50 changes every couple days, you’ll get statistics updated every week.

If you have 1mm rows (think large, historical data here), then those same 50 changes won’t trigger statistics updates for a long time. 200,000 changes will be needed then.

There’s a trace flag, 2371, that can help with the minimum needed to trigger a stats update (or you can do this with your own jobs). By choosing this, you can lower the minimum for triggering an update.

What you do really depends on your issues. If you find poor performance in queries, look for wildly incorrect estimates of rows in your query plans. If you find that your statistics aren’t being updated, or not updated enough, you might enable the trace flag, or create your own job to update statistics manually.

Note that if you are rebuilding indexes, you don’t need to also update statistics on the columns in the index. They are done as part of the index rebuild.

About these ads

From → Blog

4 Comments
  1. Nathaniel permalink

    Just to be clear, the recomputation of statistics is controlled via the STATISTICS_NORECOMPUTE clause in the alter index statement. I’ve been waging a personal campaign within my organization to help DBAs understand when to set this, and when not.

    • Good catch. I haven’t worked with partitioned tables, so haven’t seen this, but that’s something to be aware of.

  2. Hi Steve. Regarding this statement:

    “Note that if you are rebuilding indexes, you don’t need to also update statistics on the columns in the index. They are done as part of the index rebuild.”

    this is no longer always true. Starting in SQL Server 2012, partitioned indexes are not given a stats update when doing a rebuild. It is mentioned in the Remarks section, under “Rebuilding Indexes”, here:

    http://msdn.microsoft.com/en-us/library/ms188388(v=sql.110).aspx

    I have tested this on a 900 million row table and after doing rebuilds of both ONLINE = ON and OFF, the resulting sample rate was 0.14%.

    So, if you are on SQL Server 2012 and are rebuilding a partitioned index, then you need to run UPDATE STATISTICS after the rebuild to get a decent sample rate.

  3. I hadn’t used the STATISTICS_NORECOMPUTE clause, though the default is off (auto stats on). It’s no something I think I’ve ever seen in the questions and posts I’ve read. Do you have devs that are turning this off on an index by index basis?

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,315 other followers

%d bloggers like this: