.Maui Blog

Find Most executed queries in SQL Server

SELECT
    TOP 100 QueryState.execution_count,
    OBJECT_NAME(objectid),
    query_text = SUBSTRING(
        qt.text,
        QueryState.statement_start_offset / 2,
        (
            CASE
                WHEN QueryState.statement_end_offset = -1 THEN len(convert(nvarchar(max), qt.text)) * 2
                ELSE QueryState.statement_end_offset
            END - QueryState.statement_start_offset
        ) / 2
    ),
    qt.dbid,
    dbname = db_name(qt.dbid),
    qt.objectid
FROM
    sys.dm_exec_query_stats QueryState
    CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) as qt
ORDER BY
    QueryState.execution_count DESC
Exit mobile version