Subscribe to this blog
Briefcase
Print
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 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.
Posted by Jason Brimhall on 20 June 2011
Thanks Grant -this is good info. Nice to learn and reinforce good procedures.
Posted by ianstirk on 22 June 2011
Nice article.
You might want to use the below SQL (or a variation of it) to obtain details of plans that have a "Timeout".
The article relating to the generic code that searches cached plans is here: www.sqlservercentral.com/.../66729
Thanks
Ian Stirk
SQL Server DMVs in Action
www.amazon.com/gp/product/1935182730/
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT --TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%StatementOptmEarlyAbortReason="TimeOut"%'
--ORDER BY cp.usecounts DESC