DMV queries for capturing most frequently used queries and worst performing queries Can anyone provide dmv query to achieve the following 


Query to capture frequently used stored procedure 
Query to capture frequently used ad-hoc queries
Query to capture worst performing stored procedures 
Query to capture worst performing queries 


Thanks in Advance.
'Worst performing' query depends on how you would define the word 'worst'. Is it by Duration? CPU? I/O?

For measuring highest CPU utilization of my cached query plans, I use the dm_exec_query_stats and dm_exec_sql_text DMVs.

The following query gets everything from the plan cache that was executed within the last day. It then calculates the percentage of overall CPU utilisation of that query and orders the results by that percentage.

It's an idea that I borrowed from an Itzik Ben-Gan post, although he used Extended Events to capture data

  SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext      

  FROM sys.dm_exec_query_stats AS deqs 
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time &gt; DATEADD(dd, -1, GETDATE()) 
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases  
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')  
  GROUP BY 
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, deqs.query_hash, dbid

  ORDER BY pctcpu DESC


Caveats

It does, however, rely on the plan being cached (not always the case), the CPU utilisation is the total from the PlanCreationTime not just the last day, and I only really use it as a rough guide for where to start looking for my issues.

However, when captured over time, it has enabled me to add/remove indexes and tweak queries to improve their performance.

Have a play with the queries and look up the definitions of the two DMVs I've mentioned above to try and get the results to your questions.