Query Performance Report

  • Comments posted to this topic are about the item Query Performance Report

  • Thanks, I'll give it a try.

  • Awesome script ... thanks.

    I've added support for dbid column when raw sql is executed which is not wrapped through a sp.

    Maybe you can change your script that way.

    I've also added 2 more (optional) columns which return database name and the inner sql command text, if needed.

    SELECT s.[sql_handle]

    , s.[statement_start_offset]

    , s.[statement_end_offset]

    , s.[plan_generation_num]

    , s.[plan_handle]

    , s.[creation_time]

    , s.[last_execution_time]

    , s.[execution_count]

    , s.[total_worker_time]

    , s.[last_worker_time]

    , s.[min_worker_time]

    , s.[max_worker_time]

    , s.[total_physical_reads]

    , s.[last_physical_reads]

    , s.[min_physical_reads]

    , s.[max_physical_reads]

    , s.[total_logical_writes]

    , s.[last_logical_writes]

    , s.[min_logical_writes]

    , s.[max_logical_writes]

    , s.[total_logical_reads]

    , s.[last_logical_reads]

    , s.[min_logical_reads]

    , s.[max_logical_reads]

    , s.[total_clr_time]

    , s.[last_clr_time]

    , s.[min_clr_time]

    , s.[max_clr_time]

    , s.[total_elapsed_time]

    , s.[last_elapsed_time]

    , s.[min_elapsed_time]

    , s.[max_elapsed_time]

    , s.[query_hash]

    , s.[query_plan_hash]

    , s.[total_rows]

    , s.[last_rows]

    , s.[min_rows]

    , s.[max_rows]

    , st.[text]

    , pa.value AS [dbid]

    , q.objectid

    , q.number

    , q.encrypted

    , q.query_plan

    -- Additional Columns: DBName & CmdText if needed

    --, COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)) + '*', 'Resource') AS DBNAME

    --, SUBSTRING(text,

    ---- starting value for substring

    -- CASE WHEN statement_start_offset = 0

    -- OR statement_start_offset IS NULL

    -- THEN 1

    -- ELSE statement_start_offset/2 + 1 END,

    ---- ending value for substring

    -- CASE WHEN statement_end_offset = 0

    -- OR statement_end_offset = -1

    -- OR statement_end_offset IS NULL

    -- THEN LEN(text)

    -- ELSE statement_end_offset/2 END -

    -- CASE WHEN statement_start_offset = 0

    -- OR statement_start_offset IS NULL

    -- THEN 1

    -- ELSE statement_start_offset / 2 END + 1

    --) AS TSQL

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

    CROSS APPLY sys.dm_exec_query_plan (plan_handle) q

    WHERE pa.attribute = 'dbid'

    And last_execution_time > GETDATE()-1

  • Thanks for the script.

  • This works out pretty nice, thanks again.

Viewing 5 posts - 1 through 4 (of 4 total)

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