Make the Optimizer Work Harder

20Jun 2011 by Grant Fritchey 9 Comments

One of my favorite indicators for whether or not you have a good execution plan is when you see the “Reason for Early Termination” property in the TSQL operator like this:

image

The optimizer considered this particular plan “Good Enough.” which is what you want to see. When you see “Timeout” as the reason, that’s an indication that the plan you have may be sub-optimal. The question is, can you make the optimizer spend more time on your queries. Well, actually, the question is, should you make the optimizer spend more time on queries. During my session on SQL Cruise I answered the original phrasing of that question, no. As usual when I present in front of people smarter than I am, I was wrong. Brent Ozar (blog|twitter) pointed out that there was a trace flag for forcing the optimizer to spend more time on queries, 2301.

According to Microsoft you can set this trace flag on your system or per user session. Either way, it doesn’t simply make the optimizer spend more time. In fact, what it does is turn on a whole new set of possible optimizations, which causes the optimizer to spend more time. What optimizations you ask? Here’s an excellent article by Ian Jose (blog) outlining exactly what you’re enabling by turning on this traceflag.

Do I recommend that you enable this trace flag if you’re looking at Timeout as the early terminator for your execution plan? Nope. Not at all. I recommend you spend time tuning that query. Break it down into smaller pieces. Not that I like hints, but see if a query hint will solve the issue. If none of those approaches work, I’d at least consider testing trace flag 2301. But even before you do that, I’d validate that any of the additional optimizations outlined by Ian Jose are applicable to your issue. If you are not facing those specific situations, setting this trace flag could hurt your performance.

There’s surprisingly little documentation on this out there. The one story on it I found comes from Brent (which is why he evidently knew about it). He turned it on which solved a problem and then had to turn it back off because it created others.

This particular trace flag definitely sounds like you need to apply primum non nocere as your guiding principle.

9 thoughts on “Make the Optimizer Work Harder”

  • When you say you presented in front of people smarter than you are, I assume you’re talking about Buck Woody, because I got NOTHIN’ on you, man. Great presentation as always, by the way.

    Reply
  • Chris Adkin

    Hi Grant,

    A semi random question, but I’ve been trying to find this out without any success. Does the SQL Server optimizer have a query transformation phase, by this I mean the re-writing of a query into an equivalent but more efficient form:-

    1. Turning sub queries into joins
    2. Merging the sql of a view into the parent query
    3. Turning not in sub queries into anti joins.
    4. Turning exist sub queries into semi joins
    5. Turning re-writing statements with NOT INs to statements with UNION ALLs in.

    Regards,

    Chris

    Reply

  • Yeah, it does. It’s called query simplification. It doesn’t do all the stuff you’re suggesting, but it does a bunch of it and some more,

    * eliminating duplicate objects and references where it can
    * eliminating tables not necessary to return the data set

    Stuff like that in addition to rearranging the query structure. I don’t know all the details of what it does, but parts of it are available out there on many of the Microsoft blogs. Also, Benjamin Nevarez has an excellent book on the topic that’s worth a read.

    Reply

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.