SQL SERVER – Find High Frequency Queries

Knowing SQL Server is one thing but knowing what users needs is another thing. I keep on learning about SQL Server from my clients of Comprehensive Database Performance Health Check . Recently during consulting engagement, we had to Find High Frequency Queries in SQL Server, let us learn that today.

Here is the script which I had original written here. I have modified it to Find High Frequency Queries in SQL Server.

SELECT
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 s,
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 FrequencyPerSec DESC

When you run the query above, you will get results of frequently run queries with the frequency per  second column. Well, that’s it. A simple query for today’s blog post. Let me know what you think about this blog post in the comment area.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)