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, ProcPlan XML ); /* Populate the temporary table with all cached procedure plans for this database */ INSERT INTO #ProcPlans(SchemaName, ProcName, ProcPlan) SELECT OBJECT_SCHEMA_NAME(Object_ID) [Schema] ,OBJECT_NAME(Object_ID) [Procedure] ,eqp.query_plan 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); /* View results. This can be changed to an inequality query by changing the "=" to a "!=" */ SELECT * FROM #ProcPlans 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.