Microsoft Dynamics AX (Archived)

Trace Flag 4136 - Parameter Sniffing

(0) Share

Report

Posted on by refa

If you have enabled this trace flag,

then please share your experience.

Would like to know how many are using it

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Dick Wenning 8,705 Moderator on at

Like (0)

Report

Don't do it

it makes AX not predictable.

use 4199 and apply the latest kernel where dataareaid (and partion in R2) changes to a literal.

so it gets stable,

in general the query plan becomes company depended.

incase you don't have multiple companies there is not a really need for it. just 4199 is ok

Was this reply helpful? Yes No
refa on at

Like (0)

Report

Thanks, but i want to find out how many are still using it or tried it,

but removed again.

Btw Partition and dataareaid are not the only reason for bad plans.

With unevenly distributed data, Force literals for those two fields will not help.

The only solution without the flag is to add forceliterls to all queries which make problem...

Was this reply helpful? Yes No
Suggested answer

rudra 6,534 on at

Like (0)

Report

Hi Refa,

My suggestion is do not use in production environment, try in test environment alone if it is hosted in a separate server. This will leads to poor performance also.

Please refer the below link:-

blogs.msdn.com/.../overcoming-parameter-sniffing-issue-in-microsoft-dynamics-ax-2012-r2-cu6.aspx

support.microsoft.com/.../980653

Was this reply helpful? Yes No
refa on at

Like (0)

Report

Thanks, i already know all the blog entries to this :)

Just want to know if or how many are still using it in production.

Was this reply helpful? Yes No
rudra 6,534 on at

Like (0)

Report

Ok great then you can also wait and check with others feedback about the same in real time. I tried in test my application got hang for a while and took more time to response for my action.

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

If you went to the last technical convergence in Seattle.

then they told don't use....

yester day I removed it from my last SQL server. ad hock all queryplan start re compiling, there was no reason for it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Dick!

1) I am just experimenting with this TraceFlag... "incase you don't have multiple companies there is not a really need for it". Well, AFAIK this is exactly one reason to use it. We have 6 companies, but data distribution is extremly uneven (say 95% of the data belongs to company A). Of course TraceFlags are always "experimentell" but i will give it a try.

2) Why the recommendation for trace flag 4199? Is this a general recommendation or for whom companies this trace flag make sense?

Regards,

Frank

Was this reply helpful? Yes No
Guy Terry 28,924 Moderator on at

Like (0)

Report

Hi Frank,

Microsoft recently blogged about SQL Trace flags for Dynamics AX:

blogs.msdn.microsoft.com/.../microsoft-dynamics-ax-20092012-sql-server-2008-r220122014-configuration-check-list-for-go-live

It has links that explain what 4199 does. I think its a general recommendation. I often see it recommended to be enabled:

blogs.msdn.microsoft.com/.../sql-server-trace-flags-for-dynamics-ax

blogs.msdn.microsoft.com/.../dynamics-ax-and-sql-server-trace-flags-quick-and-dirty

blogs.msdn.microsoft.com/.../ax-performance-analyzing-key-sql-server-configuration-and-database-settings

However, note that not everyone recommends it!:

kaya-consulting.com/sql-trace-flag-dynamics-ax-need

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

thanks a lot for the links (much to read *sigh* ;))! I will study them and then make my decision...

Was this reply helpful? Yes No
Simon D 10 on at

Like (0)

Report

I know this question was asked a long, long, long time ago, but we've just started using multiple companies in AX 2012. We now have 2 very different sized companies and have found performance problems if a plan was created for a query against the new much-smaller company, and later run the same query against the larger company. The plan was fine for the small company, but totally unsuited for the larger (and much more often used) dataset of the larger company.

Initially I used plan guides for the specific queries, which worked, but as we found more and more queries with the same problem, it was threatening to become unmanageable.

After reading a couple of articles (Trace Flag 4136 - Fighting bad data modeling (kejser.org) & (+) Essential SQL Trace Flags: Dynamics AX - Microsoft Dynamics AX Community), we tried enabling 4136 in Test and removed the plan guides. This fixed the known problems and no new performance problems have been reported.

So, from our experience, 4136 has made a significant improvement, though we've not yet tried it in production. Having said that, the article Overcoming parameter sniffing issue in Microsoft Dynamics AX 2012-R2 – CU6 | Microsoft Docs suggest an alternative AX-based solution, which may be better. We'll try this in Test and I'll report back here on the results.

Was this reply helpful? Yes No