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.

SQL SERVER - Find High Frequency Queries HighFrequency-800x326

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.

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

SQL Cache, SQL DMV, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Execution Time of Stored Procedures
Next Post
SQL SERVER – Database Trigger for Index Can Prevent Index Rebuild

Related Posts

1 Comment. Leave new

  • Hi Pinal
    for me, there is a simple typo, in your nice and useful query above.

    Instead of
    ISNULL(s.execution_count / 1000 /
    NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec

    I would use

    ISNULL(s.execution_count / 1. /
    NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec

    or something similar.
    Am I wrong?

    Thanks anyway for ypur work

    P.

    Reply

Leave a Reply