¿Cómo identificar consultas de ejecución lenta en SQL Server?

Requisitos previos: Introducción a MS SQL Server , Introducción a SQL Server | Arquitectura
 

Microsoft SQL Server o MS SQL Server para abreviar es el lenguaje de consulta proporcionado para la definición y manipulación de datos. SQL Server se compone de: motor de base de datos, motor relacional y motor de almacenamiento. 

En este artículo, aprenderemos a identificar consultas lentas o prolongadas que terminan consumiendo una gran cantidad de activos de la máquina y consumen la mayor parte del día para generar resultados, ya sea debido al uso exorbitante de la CPU, la memoria o el disco.

La motivación detrás de este artículo es ayudarlo a identificar las consultas que tienen posibles problemas de ejecución. Cuando se identifican estas consultas, debe considerar si desea agregar más recursos a la máquina o comenzar ejercicios de ejecución y ajuste de consultas.

Eventos extendidos de SQL Server:

SQL Profiler ha sido reemplazado por SQL Server Extended Events. Los eventos ampliados funcionan a través del seguimiento de eventos (ETW). Esta ha sido la forma conocida de todos los avances relacionados con Microsoft para descubrir información analítica. ETW ofrece una adaptabilidad considerablemente mayor. 

Encuentre consultas lentas con DMV de SQL:

Uno de los puntos destacados extraordinarios de SQL Server son las Vistas de administración dinámica (DMV) que están todas incorporadas en él. Hay muchos de ellos y pueden proporcionar una gran cantidad de datos sobre una gran cantidad de temas.

Hay algunos DMV que brindan información sobre detalles de consultas, planes de ejecución, consultas en curso y mucho más. Estos se pueden utilizar juntos para dar algunas ideas asombrosas.

Por ejemplo, la consulta debajo se puede utilizar para rastrear las consultas que usan la mayor cantidad de lecturas, escrituras, tiempo de trabajo (CPU), etc.

Consulta:

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) query, 
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, 

Producción:

Aquí puede ver que una consulta específica (la de arriba) ocupa la mayor parte de la CPU.

Al observar esto, podemos duplicar esa consulta SQL y verificar si hay alguna forma de mejorarla aún más, agregar un índice, etc.

Analizador de SQL Server:

SQL Server Profiler existe desde hace bastante tiempo. Es excepcionalmente valioso suponiendo que está tratando de encontrar continuamente las consultas SQL que se ejecutan en su base de datos.

Note: Microsoft has announced that SQL Server Profiler is being deprecated!

SQL Profiler captura eventos extremadamente detallados sobre su interacción con SQL Server:

• Conexiones de inicio de sesión, desconexiones y fallas.
• Sentencias SQL básicas como SELECCIONAR, INSERTAR, ACTUALIZAR y ELIMINAR.
• Iniciar y finalizar procedimientos almacenados.
• Comienzo y final de declaraciones dentro de un procedimiento almacenado.
• Llamadas de estado de lotes de RPC.
• Comienzo y final de un lote de SQL.
• Los errores se escriben en el registro de errores de SQL Server.
• Un cursor abierto.
• Comprobaciones de consentimiento de seguridad.