SQL Server / Index Usage Report Project / Most Expensive Queries

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments

Index Usage Report Project / Expensive Queries Table

I created this ETL process (SSIS) to populate my WH Expensive Queries table, so I can later join this table using plan_handle to determine if an index is used within those expensive queries.

Every time I run this ETL I am truncating this WH table and inserting new rows.
Control flow > SQL Task

 

SQL Task

 

After that under the Data Flow, I created data source and destination of my ETL process

 

For OLE DB Source I used SQL command

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’),
expensive_queries AS (
SELECT
ph.plan_handle [PlanHandle],
ISNULL(CAST(nodes.value(‘(@StatementSubTreeCost)[1]’, ‘VARCHAR(128)’) as float),0) AS SubTreeCost,
ph.usecounts [UseCounts],
ph.size_in_bytes [SizeInBytes]
FROM
sys.dm_exec_cached_plans AS ph
CROSS APPLY sys.dm_exec_query_plan(ph.plan_handle) AS eq
CROSS APPLY query_plan.nodes (‘//StmtSimple’) AS qn (nodes))
SELECT TOP 20
PlanHandle,
SubTreeCost,
UseCounts,
SubTreeCost * UseCounts [TotalCost],
SizeInBytes
FROM
expensive_queries
ORDER BY
TotalCost
DESC
Query notes:

  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; I used this to avoid shared locks issues and to avoid blocks by exclusive locks that would prevent the transaction from reading modified but not committed rows. You can expect dirty read.
  • With this query, we will check our server plan cache and we need to specify the XML schema for execution plans.
    WITH XMLNAMESPACES(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)

  • To determine how expensive our query is, we are looking for SubTreeCost statement within our cached execution plan XML schema. StatementSubTreeCost is the total cost for all the operations within that statement. We can find this attribute within STMTSIMPLE element, this is the full path: query_plan.nodes (‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS qn ( nodes))

If you want to read more detailed about SHOWPLAN XML Structure you should visit Jason Strate blog, there you can find what else is available within the SHOWPLAN XML for an execution plan.

 

 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *