Nov 19 2010
Identifying Query Plans that are not good enough
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.
Richard Douglas
Richard is a Systems Consultant for a vendor of SQL Server solutions where he specialises in SQL Server providing solutions and system health checks to organisations across the EMEA region. Richard has recently taken on the responsibility of being Editor in Chief of the community site SQLServerPedia which provides articles on SQL Server and a blog syndication service, other duties see him regularly presenting webinars to both US and EMEA audiences. He is also a keen member of the SQL Server community, he founded and runs a PASS affiliated chapter in the UK (www.SQLStudy.org) and is on the organising committee for a national event called SQLRelay. Read more about Richard.
More Posts - Website
Follow Richard here:
