Post reply

Find Top 5 executed queries ordered by execution count

  • October 7, 2013 at 9:57 pm

    #299020

    Comments posted to this topic are about the item Find Top 5 executed queries ordered by execution count

  • March 4, 2015 at 10:57 am

    #1781705

    Very interesting! I really should spend some time exploring DMVs, I used to love rummaging around in system tables in older versions of SQL Server.

    Having said that, I'm getting zero rows returned. I copied and ran two queries from Books Online under sys.dm_exec_query_stats , and they ran fine. I wonder if there's something slightly different under SQL 2014? My server isn't in production right now, but my development project is providing enough traffic to give me information with these two views. Unfortunately I don't know enough about DMVs right now to try to find the issue in your script.

    Here's the code that I ran from BOL:

    SELECT TOP 5 query_stats.query_hash AS "Query Hash",

    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

    MIN(query_stats.statement_text) AS "Statement Text"

    FROM

    (SELECT QS.*,

    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

    WHEN -1 THEN DATALENGTH(ST.text)

    ELSE QS.statement_end_offset END

    - QS.statement_start_offset)/2) + 1) AS statement_text

    FROM sys.dm_exec_query_stats AS QS

    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

    GROUP BY query_stats.query_hash

    ORDER BY 2 DESC;

    and

    SELECT qs.execution_count,

    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

    (CASE WHEN qs.statement_end_offset = -1

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

    ELSE qs.statement_end_offset end -

    qs.statement_start_offset

    )/2

    ) AS query_text,

    qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,

    qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    WHERE qt.text like '%SELECT%'

    ORDER BY qs.execution_count DESC;

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply