rendimiento - tuning base de datos sql server

¿Cómo encontrar las consultas de peor rendimiento en SQL Server 2008?

Question

¿Cómo encontrar las consultas de peor rendimiento en SQL Server 2008?

Encontré el siguiente ejemplo pero parece que no funciona:

SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY max_logical_reads DESC

Tomado de:

http://www.sqlservercurry.com/2010/03/top-5-costly-stored-procedures-in-sql.html

Answer 1

Encontrar consultas de ejecución lenta con SQL Profiler

• Inicie el Analizador de SQL (preferiblemente en la base de datos en vivo).
• Archivo -> Nuevo rastro
• Elija el servidor SQL
• Filtro de pestaña
• Opcionalmente establecer un filtro en el nombre de la base de datos
• Inicie el perfilador (RUN)
• Guarde el resultado en una tabla, por ejemplo: _Mytrace, preferiblemente en un servidor de base de datos que no tiene mucho que hacer ya
• Filtra las consultas de selección
• ordenarlos por duración
• Verifique el plan de ejecución para estas consultas

Answer 2

Las 10 peores consultas basadas en ...:

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 

--pick your criteria

ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC

Answer 3

Este artículo de MSDN Magazine brinda información excelente sobre este tema.

Answer 4

Si quieres encontrar las consultas de peor rendimiento por tiempo, usaría esto:

SELECT *
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
ORDER BY total_elapsed_time/execution_count DESC

Sin embargo, encontrar las "peores" consultas a menudo requiere un poco más de exec_query_stats en el DMV exec_query_stats . Hay muchas cosas para considerar:

1. Peores consultas individuales por tiempo tomadas que arrojará la consulta anterior.
2. Peor CPU cerdos (si se está ejecutando alto en la CPU) que ordenaría por total_worker_time/execution_count
3. Consultas que realizan la mayor cantidad de lecturas, que a menudo son consultas que tardan más.

Ahora, estas consultas resaltarán las consultas que tienen un rendimiento deficiente, pero a menudo es posible que tenga consultas con un rendimiento "justo", pero que se llamen con frecuencia, lo que reduce el rendimiento general de su aplicación. Para encontrarlos, ordene la consulta anterior por total_elapsed time (o total_[whatever metric you are interested in] ) y no divida por execution_count .