Nov 19 2010
After doing my usual daily checks I noticed that I had an unread blog entry – Reason for Early Termination of Statement by Grant Fritchey (Blog|Twitter) in my RSS Feeds, Grant’s posts are always very good so I put a few moments aside to take a look through and I’m glad that I did!
If you haven’t read Grant’s post then you really need to for two reasons:
- It’s really good
- The following will make no sense if you don’t as it’s a directly linked.
Grant has done an excellent job of reminding us all that the optimizer is fallible and on occasions will not find the best plan. What we need is a way to find these plans …….
The first thing you need to do is to open SSMS and connect to the database that you wish to check for procedure plans that aren’t good enough, then we’re ready to go.
Run the following code, preferably in your development environment:
Set the type of Early Termination you wish to check for.
DECLARE @ReasonForEarlyTermination VARCHAR(30)
SET @ReasonForEarlyTermination = 'TimeOut' --'MemoryLimitExceeded'--'GoodEnoughPlanFound'
Create a temp table to store our initial results
and build XML Indices on
CREATE TABLE #ProcPlans
ProcPlanID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SchemaName varchar(100) not null,
ProcName varchar(100) not null,
Populate the temporary table with all cached procedure plans for this database
INSERT INTO #ProcPlans(SchemaName, ProcName, ProcPlan)
FROM sys.dm_exec_procedure_stats eps
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) eqp
WHERE database_id = DB_ID()
AND eqp.query_plan IS NOT NULL
ORDER BY 1,2;
Create primary XML Index
CREATE PRIMARY XML INDEX PK_XML_ProcPlan
ON #ProcPlans (ProcPlan);
This can be changed to an inequality query by changing the "=" to a "!="
where ProcPlan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//StmtSimple/@StatementOptmEarlyAbortReason[. = sql:variable("@ReasonForEarlyTermination")]') = 1;
DROP TABLE #ProcPlans;
If you have some bad plans, you will have an output similar to the one below:
Now you’re left with the task of simplifying the queries so the optimizer can do a better job.