SQL Server Performance

Help on DMVs

Discussion in 'ALL SQL SERVER QUESTIONS' started by Frank.svs, Mar 11, 2014.

  1. Frank.svs New Member

    Hi ,

    Can anybody share dmv queries to achieve the following ?

    1. Query to capture frequently used stored procedure
    2. Query to capture frequently used ad-hoc queries
    3. Query to capture worst performing stored procedures
    4. Query to capture worst performing queries

    Thanks in Advance.
  2. melvinlusk Member

    Assuming you're on SQL 2005 or higher.

    1) select top 25 sp.[name], sq.[execution_count] from sys.procedures sp, sys.dm_exec_procedure_stats sq
    where sp.object_id = sq.object_id
    order by sq.execution_count desc

    2) This ones a bit tricky, since we can really only see ad-hoc queries that have a cached plan:
    select top 25 [text] as SQLText, execution_count from sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS SQLText
    where SQLText.text not like '%CREATE%'
    order by deqs.execution_count desc

    3) By "worst peforming" I'm going to assume it's based off of AVG CPU time. This also applies for 4.
    SELECT TOP 25 deqs.total_worker_time, execution_count, total_worker_time/execution_count [Avg CPU Time],
    CASE WHEN deqs.statement_start_offset = 0
    AND deqs.statement_end_offset = -1
    THEN '-- see objectText column--'
    ELSE '-- query --' + CHAR(13) + CHAR(10)
    + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
    ( ( CASE WHEN deqs.statement_end_offset = -1
    THEN DATALENGTH(execText.text)
    ELSE deqs.statement_end_offset
    END ) - deqs.statement_start_offset ) / 2)
    END AS queryText
    FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
    ORDER BY deqs.total_worker_time DESC ;
    Frank.svs likes this.
  3. Frank.svs New Member

    Thank you for the help melvinlusk.
  4. Trev256b Member

    check out the sql performance reports too - and check out sql profiler too and dbcc input buffer - that will keep u amused for a while :)

Share This Page