Execution plans of a Query

This query is useful when we see a particular stored procedure or query plans

showing null, Then we can run the below query to finout the execution plan of that

particular query

SELECT (SELECT TOP 1 SUBSTRING(sql_text.text,statement_start_offset / 2+1 ,

((CASE WHEN statement_end_offset = -1

THEN (LEN(CONVERT(nvarchar(max),sql_text.text)) * 2)

ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS text,

cast(txt_query_plan.query_plan as xml) query_plan

FROM sys.dm_exec_query_stats AS Query_Stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text

cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) txt_query_plan

SELECT*