Identify most expensive queries from SQL Server plan cache

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

WITH XMLNAMESPACES (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’),
core AS (
 SELECT
  eqp.query_plan AS [QueryPlan],
  ecp.plan_handle [PlanHandle],
  q.[Text] AS [Statement],
  n.value(‘(@StatementOptmLevel)[1]’, ‘VARCHAR(25)’) AS OptimizationLevel ,
  ISNULL(CAST(n.value(‘(@StatementSubTreeCost)[1]’, ‘VARCHAR(128)’) as float),0) AS SubTreeCost ,
  ecp.usecounts [UseCounts],
  ecp.size_in_bytes [SizeInBytes]
 FROM
  sys.dm_exec_cached_plans AS ecp
  CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
  CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q
  CROSS APPLY query_plan.nodes (‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS qn ( n )
)

SELECT TOP 100
 QueryPlan,
 PlanHandle,
 [Statement],
 OptimizationLevel,
 SubTreeCost,
 UseCounts,
 SubTreeCost * UseCounts [GrossCost],
 SizeInBytes
FROM
 core
ORDER BY
 GrossCost DESC
 –SubTreeCost DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s