Encontrar los queries que consumen mas recursos

Los DMVs (Dynamic Management Views) son una magnífica 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 compatibilidad 2005/2008)

Query: 

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offsetEND - 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_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER 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 búsqueda.

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