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?

How to Find Longest Running Query With Execution Plan - Interview Question of the Week #098 longrunningqueryexecution-800x315

Solarwinds

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)

Solarwinds
, ,
Previous Post
How to Find Size of All the Indexes on the Database – Interview Question of the Week #097
Next Post
How to Downgrade Database from SQL Server 2014 to SQL Server 2012 – Interview Question of the Week #099

Related Posts

2 Comments. Leave new

Leave a Reply

Menu