Encontrar los queries que consumen mas recursos

Los DMVs (Dynamic Management Views) son una magnifica forma de encontrar información de performance a partir de SQL Server 2005 en adelante (2008/2008R2/2012).

En este query de Pinal Dave se utilizan DMVs para encontrar los queries que mas consumen en un server SQL Server 2005/2008 (recordar que este script funciona solo en bases compatibles 2005/2008).

 
SELECT TOP 10 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

Obviamente se puede cambiar el ordenamiento del script para destacar otro criterio de busqueda.

No está de más destacar un conjunto de queries, los DMVs AllStars, que se puede descargar aquí y que es una interesante herramienta para todo DBA en busca de detectar problemas de performance.