Trace Flag 4136

This week, I have been tuning a Dynamics C5 database. What a horrible mess that is. However, I ran into something that might come in handy in a lot of situations.

I have long known about Trace flag 4136 – but until now, I did not realise this flag is officially documented in a Microsoft source:

Dynamics AX in the Field

This flag is immensely useful and not just for old versions of Dynamics C5. In this blog, I will provide an example of its use and some details about what the flag does.

What is the problem With Dynamics?

If you look at the data model and queries of Dynamics, you quickly realise it has been designed by someone who, to put it mildly, is not very knowledgeable about databases. Among the many problems with the data model is that every key (which is not really a key in Dynamics, but just a unique cluster index) and most indexes are prefixed with the DATASET column, a varchar(3) that is never bigger than 3 chars. The column typically takes only a few values and it is highly skewed.

As should be obvious to anyone familiar with parameter sniffing and cost based optimisation – such a design is extremely dangerous and predictably leads to  inefficient query plans.

What does The Trace Flag do?

Most trace flags in the 4000 range affect the optimiser. Flag 4136 is no exception. What this flag basically does is to force OPTION (OPTIMIZE FOR UNKNOWN) on all queries. There is an entire range of flags that force different OPTION hints on all queries – please go bother Conor to make them public.

This downside of Trace Flag 4136 is that it prevents the optimiser from picking a plan that is optimal for a skewed dataset. Because OPTIMISE FOR UNKNOWN does not take as much information from the histogram – specific value for a query no longer get plans tuned for that value. But the upside is really something: It makes workloads on skewed dataset (the norm, not the exception) behave consistently, even when there is skew. Let me say that again: Not necessarily 0ptimal performance, but consistent performance.

When given the choice between “mostly” optimal performance with the occasional disastrous execution time and consistent but slightly lower performance – most DBAs would pick consistent. Interestingly, this seems to be a fact that has so far eluded most database vendors (except for Oracle’s flirtation with rule based optimisation)

Trace flag 4136 gives you the consistent option. On a highly skewed workload, you are more likely to get consistent plans, particularly if you combine it with forced parameterization…

A Workaround For The Ascending Key Problem

I have previously blogged about the Ascending Key problem and how it causes all sorts of trouble for data warehouses.  I have also provided a non supported workaround.

With trace flag 4136, we finally have a supported solution.

If you set up my repro for the ascending key problem, try to run this:

/* Fix the optimiser */

ALTER DATABASE {db} SET PARAMETERIZATION FORCED
DBCC TRACEON(4136, -1)

DBCC FREEPROCCACHE

/* Observe the magic */

SELECT C.shoe_size, SUM(Measure)
FROM Fact F
JOIN DimCustomer C ON F.customer_sk = C.customer_sk
WHERE F.date_sk = 20010111
AND C.shoe_size = 43
GROUP BY C.shoe_size

Isn’t that nice? By forcing auto parameterization, we turn the date_sk filter into a parameter. Trace flag 4136 then makes the optimiser treat this parameter as unknown and we get exactly the parallel query plan we want. All without modifying the query, which might well have been generated by a BI tool.

Summary

In the hands of an expert, trace flag 4136  is very powerful. Some tricky workloads are almost impossible to fix without it. I am happy to see it out there.

Data warehousing is not the only workload where this flag comes in handy. For example, consider the case of EAV models. Typically, you get horrible plans on those models because they have highly skewed data. With trace flag 4136, you have hope of survival(though you are likely still a fool for designing an EAV in the first place).

  6Comments

  1. calin   •  

    4199… Was told by MS to implement that no matter what…

    • Thomas Kejser   •     Author

      Really? One wonders why they make it a trace flag.

  2. Calin OPREA   •  

    Hey Thomas, quick question: “Most trace flags in the 4000 range affect the optimiser”. Is there any documentation about trace flag ranges that I might look into?
    Thank you.

    • Thomas Kejser   •     Author

      Hi Calin

      I wish there was. Unfortunately, this is not something that MS talks about. It is the old “lower common denominator” vs. “I trust you to think for yourself” argument.

      Whenever a trace flag is made “official” a lot of consideration has to be into whether it will cause someone to shoot themselves in the foot (and then go blame MS). It is a sad state of affairs – but as the SQL Community, we only have ourselves to blame. I would encourage people to step up and ask the hard questions about what you NEED to control (and why) in the engine vs. what people think would be theoretically nice. I DO know that Microsoft listens, if you speak with a unified voice.

  3. Kevin Boles   •  

    Thanks for this Thomas. Do you have any guidelines you can post or email me about tuning Dynamics? I just picked up a client with a very large installation they are desperately needing help with! Any nuggets you can provide would be welcome. I definitely agree that Dynamics is just an UGLY mess!!

    Best,

    Kevin

    • Thomas Kejser   •     Author

      Hi Kevin

      I actually have another blog post coming up soon about some of my Dynamics C5 tuning experiences. I believe the guidance might also apply to Dynamics AX – as the data models are quite similar.

Leave a Reply

Your email address will not be published. Required fields are marked *