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 4136 vs Trace Flag 2371 Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:00 PM
Points: 8, Visits: 88
Hi Friends
I have 5 TB DB for AX.
Auto create stat and auto update stat are ON
We have update stat job for few identified table .
If I turn on 4136 ,do we really need to turn on 2371 ?
Regards,
Anjan
Post #1569128
Posted Thursday, May 8, 2014 9:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 860, Visits: 2,410
You posted the this question in SQL Server 2000 forum, are you running that version?

Trace flag 2371 did not exist until SQL Server 2008.

Either way came across a few articles that deal directly with AX:
SQL Server Trace Flags for Dynamics AX

SQL Server Trace Flag 2371 for Dynamics AX


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1569132
Posted Friday, May 9, 2014 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:00 PM
Points: 8, Visits: 88
Thanks Shawn for sharing this link.
I could not found correct category to put this post.
SQL Server 2008 R2 SP2
Post #1569337
Posted Friday, May 9, 2014 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:00 PM
Points: 8, Visits: 88
@Shawn,I have gone through this link before also,but could not find my answer there.
Post #1569339
Posted Friday, May 9, 2014 9:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 860, Visits: 2,410
Reading up on what both of these trace flags are for, they do not seem to be related so enabling them both has their own advantages, specifically with AX. I do not see why you would not enable 2371 if needed, regardless if 4136 is or not.

4136 - deals with the parameter sniffing process.
2371 - deals with auto update stats and when the query optimizer will attempt to update stats which just basically throws out the default 20% change rate.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1569354
Posted Wednesday, June 25, 2014 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:12 PM
Points: 1, Visits: 46
Hi,

With Trace Flag 4136 enabled SQL Server ignores the supplied parameter values for the SQL statement and uses the statistical average for that column in the Statistics Header for the index when it compiles the execution plan. At this point the detail portion of the statistics, the Histogram is no longer used. Trace Flag 2371 was really meant to fine tune through more periodic update the Histogram portion of the statistics. With this being said Trace Flag 2371 will provide little to no benefit for a customer already utilizing Trace Flag 4136.

So If 2371 is updating the not needed detail of the statistics, is the net result a negative one ( over head, etc) and should 2371 be turned off?
Jefferson
Post #1586193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse