Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trace flag 2371 Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 4:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 2,965, Visits: 2,985
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,
SQL Server developer at Seavus
www.seavus.com
Post #1423993
Posted Wednesday, February 27, 2013 3:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:46 AM
Points: 2,879, Visits: 3,229
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1424435
Posted Wednesday, February 12, 2014 8:02 AM This worked for the OP Answer marked as solution
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:17 AM
Points: 1,184, Visits: 1,220
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
Post #1540751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse