February 26, 2013 at 4:16 am
Hi All
I want to enable trace flag 2371 for update statistics.
The application generates a lot of queries. As enabling the trace flag withdraws recompilation of the queries, is this a good thing?
Share your experiences please.
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
February 27, 2013 at 3:28 am
I had never heard of TF 2371 until seeing your post. From looking at http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx it seems to me that TF 2371 would be useful in the following situations:
a) You have some large tables of over 1 million rows
and
b) The indexes relied on by your queries contain columns whose value is time-dependant (in most situations this means dates, but it could be a derived column such as partition number that is dependant on a date value)
Because TF 2371 triggers more frequent statistics updates, it means the queries that use date-dependant indexes are more likely to get the optimum plan.
Some sites do a weekly update of indexes on all tables, partly to overcome the long lag between automatic updates using the standard 20% change figure. For these people TF 2371 could become an alternative to a weekly stats update.
If you do use TF 2371 I suggest you also turn on asynchronous stats update, as this will avoid a query being stalled while the stats update completes. WARNING: Async stats updates causes a memory leak in some old fix levels - check you have the fix for this problem installed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 12, 2014 at 8:02 am
Igor Micev (2/26/2013)
I want to enable trace flag 2371 for update statistics.The application generates a lot of queries. As enabling the trace flag withdraws recompilation of the queries, is this a good thing?
Share your experiences please.
This thread is a year old, but I'm looking into the same thing.
I asked Grant Fritchey in his presentation at SQL Saturday Cleveland last Saturday (2/8/2014) if there was a scenario where you wouldn't use trace flag 2371 and he said "No". He then clarified and said there could be a couple of scenarios, but he generally recommended it. I'm going to push to get this implemented in our environment.
HTH,
Rob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply