Getting the Execution Plan for a given sproc

When you already know which stored procedure is causing you pain, and you need to know the existing execution plan run this fella:

SELECT TOP 10
  D.name,
  ST.text AS TSQL_Text,
  QS.creation_time,
  QS.execution_count,
  QS.total_worker_time AS total_cpu_time,
  QS.total_elapsed_time,
  QS.total_logical_reads,
  QS.last_logical_reads,
  QS.total_physical_reads,
  PH.query_plan
FROM
  sys.dm_exec_query_stats QS
  CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
  CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) PH
  INNER JOIN sys.databases D
    ON ST.dbid = D.database_id
WHERE
  ST.text LIKE '%spYourStoredProc%'
ORDER BY
  total_elapsed_time desc

Just so you’re aware; the above query will build it’s own execution plan and will be included in the result-set the second time you run it.

I’d recommend having the free Sentry One Plan Explorer installed, so you can open the execution plan in there for better analysis.

Popular posts from this blog

Taking a memory dump of a w3wp process

sp_blitzIndex

GitLab Badges