Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trace flag 4136 vs Trace Flag 2371


Trace flag 4136 vs Trace Flag 2371

Author
Message
Anjan Bhowmick
Anjan Bhowmick
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 92
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
Shawn Melton
Shawn Melton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 3495
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
Anjan Bhowmick
Anjan Bhowmick
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 92
Thanks Shawn for sharing this link.
I could not found correct category to put this post.
SQL Server 2008 R2 SP2
Anjan Bhowmick
Anjan Bhowmick
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 92
@Shawn,I have gone through this link before also,but could not find my answer there.
Shawn Melton
Shawn Melton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 3495
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
jgallaway 80208
jgallaway 80208
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 149
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search