Find worst queries in MSSQL with system dmv's

While working on performance monitoring , one of my forum member Cadavre, introduced me about DMV's. These are really handy. You can extract system stats directly from dmv. Remember that dmv's scope is limited to instance. If you will restart instance , system will wipe older and collects stats after restart.

Microsoft introduced dynamic management views (DMV) and functions (DMF) with SQL Server 2005. So what are DMV's and DMF's? How do you use these dynamic management objects? DMVs and DMFs are a mechanism to allow you to look at the internal workings of SQL Server using TSQL. They allow you an easy method for monitoring what SQL Server is doing and how it is performing. They replace the need to query the system tables or using other awkward methods of retrieving system information that you had to use with SQL Server 2000.

Some examples

=========================================
--–Queries taking longest elapsed time:
==========================================
SELECT TOP 10
        qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds ,
        qs.total_elapsed_time / 1000000.0 AS total_seconds ,
        qs.execution_count ,
        SUBSTRING(qt.text, qs.statement_start_offset / 2,
                  ( 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 individual_query ,
        o.name AS object_name ,
        DB_NAME(qt.dbid) AS database_name
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
        LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE   qt.dbid = DB_ID()
ORDER BY average_seconds DESC ;

===============================
---–Queries doing most I/O:
===============================
SELECT TOP 10
        ( total_logical_reads + total_logical_writes ) / qs.execution_count AS average_IO ,
        ( total_logical_reads + total_logical_writes ) AS total_IO ,
        qs.execution_count AS execution_count ,
        SUBSTRING(qt.text, qs.statement_start_offset / 2,
                  ( 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 indivudual_query ,
        o.name AS object_name ,
        DB_NAME(qt.dbid) AS database_name
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
        LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE   qt.dbid = DB_ID()
ORDER BY average_IO DESC ;

====================================
SELECT TOP 20
        SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.TEXT)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) ,
        qs.execution_count ,
        qs.total_logical_reads ,
        qs.last_logical_reads ,
        qs.total_logical_writes ,
        qs.last_logical_writes ,
        qs.total_worker_time ,
        qs.last_worker_time ,
        qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
        qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
        qs.last_execution_time ,
        qp.query_plan
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
 --ORDER BY qs.total_logical_writes DESC -- logical writes
--ORDER BY qs.total_worker_time DESC -- CPU time
--ORDER BY  total_elapsed_time_in_S DESC --  time