SQL Server Statistics and Trace Flag 2371

— by Lori Brown @SQLSupahStah

Statistics are pretty important to SQL Server. Statistics are small objects that hold information of the distribution of data in a table. The SQL optimizer uses this information to create efficient ways to get data from the table. So, if statistics are not taken care of SQL may have a hard time running queries. It really needs this info to do its job.

I am a fan of updating statistics at least once a day. I prefer to use sp_updatestats (https://msdn.microsoft.com/en-us/library/ms173804.aspx ) since it will by default skip tables that have not had any changes. Sp_updatestats will however update stats if there have been ANY changes to a table. So some people seem to think that it can incur too much overhead (CPU, IO) but it has served most databases well. However, (here comes the “But, but, but…”) if you don’t have a long enough window to run sp_updatestats or if the default sample of 25% is not enough to give an accurate distro of data, then you need to come up with other solutions…especially for large tables. Large tables are always a problem when trying to set up maintenance.

If you have AUTO_UPDATE_STATISTICS enabled on your database (as you should unless you have a prevailing reason not too) statistics are updated automatically when enough data has changed and if a table is large enough. Here is what triggers an automatic update of stats:

  • The table size has gone from 0 to >0 rows
  • The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr (hidden system column) of the leading column of the statistics object has changed by more than 500 since then
  • The table has more than 500 rows when the statistics when the statistics were gathered, and the colmodctr (hidden system column) of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered

If you think about this a bit, you can see how this is pretty good for small tables but large tables….those might run into a sticky wicket. When you have a really large table it can take a significant amount of changes before statistics are automatically updated.

blog_20160921_1

While researching some things in SQL 2016, I found that I have been unaware of trace flag 2371. In SQL 2016, TF 2371 is now on by default. However, as far back as SQL 2008 R2, you can set this trace flag and get the benefits.

So, what is it? Trace flag 2371 is used to lower the threshold that tells SQL to automatically update statistics. This is especially important on larger tables where statistics updates are not triggered fast enough because of the high threshold of changes that normally need to happen on a table before statistics are considered to be old by SQL. Old stats and SQL do not get along well. Apparently this was a HUGE problem for SAP and it is the reason that the trace flag exists. With TF 2371, the threshold to trigger an automatic statistics update goes dynamically down meaning there will be more frequent stats updates for larger tables which should really help SQL create efficient execution plans. Yay!

blog_20160921_2

By now you must be thinking…I am gonna turn on TF 2371 everywhere!!!

blog_20160921_3

But, I really would not recommend it. If you have not been having performance problems without TF 2371 then you probably don’t need it. If it ain’t broke, don’t fix it! Turning on trace flags means that you are telling SQL to not use the regular paths through the SQL engine. When you turn on a trace flag it is a global setting and cannot be disabled for specific databases. And, if you have to open a ticket with Microsoft, they are likely going to be asking questions about why you have it turned on. Okay…so the risk on this one seems pretty low but you really should have a reason to enable it.

Sadly the only way to know if statistics are the cause for slow queries seems to be to run into performance issues and then notice that performance improves when statistics are update. You can take the time to catch execution plans both before and after the stats update so that you can verify but that is about it.

Don’t forget that in SQL 2016, TF 2371 is enabled by default. That could be something that helps you decide to move to it.

I have to give a trackback to Kendra Little for an excellent post on statistics. You can get more on whys and ways to take care of your statistics from http://www.littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Both comments and trackbacks are currently closed.
%d bloggers like this: