How to Find Longest Running Query With Execution Plan – Interview Question of the Week #098

Question: How to find the longest running query in SQL Server with Execution Plan?

Answer: This is a very popular question I see in SQL Server Interview Questions and Answers. SQL Server stores all the data for query execution in DMVs inside it. The DMVs are reset when SQL Server services restart. Let us see a quick query which returns us longest run query with its execution plan.

SELECT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
ORDER BY MaxElapsedTime DESC

If you run above query it will return us resultset in the SSMS. Scroll all the way to the right side and you will notice that there is a column called Query_Plan. This is the column which displays the execution plan of the query. Click on it and it will bring up SQL Server Query Plan in SSMS. Once you have an access Query Plan, you can also send that via email to your friend. Here is a quick tutorial about how you can send execution plan via email. SQL SERVER – Save and Send Execution Plan Via Email

Reference: Pinal Dave (https://blog.sqlauthority.com)