20Jun 2011 by Grant Fritchey 9 Comments

Make the Optimizer Work Harder

SQL Server
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: 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…
Read More
12Jan 2010 by Grant Fritchey 4 Comments

TSQL Tuesday #2

SQL Server, T-SQL
I've wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2 and I finally came up with a bit of something. When you're looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you're looking at? Is this a trivial plan, meaning it's the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it's prescribed cycles and simply take the best plan it had generated up to that point? These questions can be answered directly from information…
Read More