Querying the Execution Plan XML for Subtree Cost

There are quite a lot of great tools and queries available to the DBA that aid in troubleshooting and performance tuning. The list is incredibly long, but I mainly use RedGate's SQL Monitor (which has come on in the last 5 years in leaps and bounds), Glenn Berry's DMV queries, Adam Machanic's sp_whoisactive and Brent Ozar Unlimited's sp_Blitz suite (predominantly sp_Blitz and sp_BlitzIndex). In writing this, I see that Brent Ozar has expanded his offering, so it looks like I have a weekend project ahead of me!


The one thing I had trouble finding was a way to query execution plans based on an Estimated Subtree Cost value. There is a missing index DMV and Glenn Berry has a good query that finds execution plans with a missing index warning.

The trouble with this is that these warnings and missing index suggestions:

1. Aren't always raised when a query needs an index; 
2. They are a bit of a blunt instrument, i.e. other similar indexes tend not to be considered. For example, if there is another similar query that is just missing an included column, SQL Server will suggest a brand new index; 
3. Most relevant to this blog post, SQL Server will claim an index is missing for a query that has an extremely low Estimated Subtree Cost. While it may be technically true that an index may help, the performance gain for the query will be negligible and the index maintenance overhead may outweigh that gain anyway.

RedGate SQL Monitor now allows you to find expensive queries for a particular time period and you can now display and download individual execution plans. While this is very helpful for identifying certain problem areas, it is very slow if you have a longer list of queries to sift through.

I was looking for a way to query the XML of the execution plans held in sys.dm_exec_query_plan DMV (this one is really a DMF). Part of my problem has been that my XML querying skills are very poor. It's due to equal measures of laziness and lack of understanding. But I finally put together the proper search string and the Internet revealed to me a question and answer about querying execution plans: How to filter XML execution plan data in a WHERE clause using TSQL on Stack Overflow.

declare @IndexName nvarchar(100) = '[MyIndex]';

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select object_name(qp.objectid), 
       cp.usecounts,
       qp.query_plan
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where cp.objtype = 'Proc' and
      qp.query_plan.exist('//RelOp[
                                  @PhysicalOp = "Index Seek" and 
                                  IndexScan/Object/@Index = sql:variable("@IndexName")
                                  ]') = 1;

The above code needed to be modified a little to achieve what I needed. Basically, the parameter needed to be changed to an INT, but I also changed its name for clarity's sake as well as adding a few columns to provide some additional information. Here's my version:

--Find execution plans with high Estimated subtree cost 
declare @Cost int 
set @Cost = 100;
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 10 qp.query_plan, cp.usecounts,objectid, object_name(objectid) as objectname, db_name([dbid]) as DBName
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where /*cp.objtype = 'Proc' 
--and dbid = db_id()
and*/ qp.query_plan.exist('//RelOp[
                          @EstimatedTotalSubtreeCost > sql:variable("@Cost")
                                  ]') = 1
ORDER BY cp.usecounts desc