Resumen
Las consultas con una ejecución lenta son uno de los problemas y preocupaciones más comunes en todas las organizaciones que manejan grandes volúmenes y cantidades de datos. Y el desafío de resolver este problema más difícil, en casi todos los clientes, con los que trabajo, es fundamental el encontrar el motivo por el cual las consultas se ejecutan lentamente y descubrir cuál es la causa real detrás del problema de rendimiento. Afortunadamente, la solución, en la mayoría de los casos, es simple.
Por lo general siempre sugiero dedicar la mayor parte del tiempo a descubrir cuál es la causa real del problema, en lugar de desperdiciar tiempo en pensar en las posibles soluciones que podrían existir.
Afortunadamente, en su auxilio hay algunas herramientas y técnicas que un desarrollador o DBA siempre debe usar (o por lo menos) para tener una idea justa sobre las consultas que se ejecutan lentamente.
Previamente antes de entrar en detalles, me gustaría mencionar prioritariamente aquí que las herramientas y técnicas que mencionaré adelante aquí serán destinados a aquellos desarrolladores de SQL que no tienen un conocimiento experto de administración de bases de datos y para los administradores de bases de datos que se encuentra iniciándose y están en el comienzo de su carrera.
Nota: Es importante mencionar que utilizaré SQL Server 2016 para mis casos de prueba en este artículo. Tome en cuenta que, si tiene una versión anterior, el Almacén de consultas no está disponible para usted, pero todas las demás herramientas seguirán funcionando.
Herramientas y técnicas disponibles
Como todo tipo de trabajo en el mundo, pero muy especialmente en esta área de trabajo, se requieren herramientas especiales. Para esta instancia, el proceso de identificación de consultas debido a la ejecución lenta no es diferente. En este artículo, solo mencionaré las herramientas que ya existen en su sistema o que puede descargarlas gratis. No diría que las herramientas comerciales no ayudan, de hecho, por su importancia en las grandes organizaciones recomendaría encarecidamente utilizar aquellas herramientas para ahorrar tiempo que se puede utilizar al realizar inmersiones profundas en cada servidor para resaltar consultas de ejecución lenta. Pero para los fines de este artículo, queremos arremangarnos y ponernos manos a la obra para aprender los fundamentos utilizando las herramientas existentes que ya tenemos a nuestra disposición.
Por ello la primera herramienta que mencionaré aquí es una herramienta integrada para SQL Server Management Studio, “Monitor de actividad“. Esta herramienta puede ser visualizada haciendo clic en el botón derecho en el nombre de la instancia en SQL Server Management Studio y seleccionando “Monitor de actividad”.
El monitor de actividad permitirá mostrar cuáles son las actividades actuales y recientes en su instancia de SQL Server.
En la captura de pantalla anterior se despliega y muestra una ventana de resumen para el Monitor de actividad. Podrá usted entonces verificar que esta pantalla le mostrará los gráficos de tiempos de procesador, tareas de espera y solicitudes de lote. En general, usted podrá verificar que cuanto menor es el número de conteos, mejor es el rendimiento general de toda la actividad. En organizaciones grandes con una gran carga laboral institucional, puede haber una gran cantidad y volumen de solicitudes por lotes con tiempos de procesador altos, pero eso no necesariamente indica un problema de rendimiento.
Después un repaso y revisión general, usted debería centrarse en Procesos, esta actividad es la que le da acceso para ver todos los procesos que se ejecutan en su instancia de trabajo y tener una visión más profunda de cuántos procesos están le están esperando, bloqueando o de cuantos han sido bloqueados. De esta manera, puede hacerse una idea si tiene consultas que se están ejecutando lentamente debido a una espera específica o si las consultas que toman tiempo están siendo bloqueadas por otros que corresponden a otros procesos. En esta vista, puede hacer clic en el botón derecho en cualquier proceso y hacer clic en Detalles para ver el TSQL real que se ejecuta para esa sesión.
Las consultas que se están siendo bloqueadas son aquellas que están realmente suspendidas debido a cualquier otro proceso que se halla trabajando en los recursos de los que depende el proceso. Por ello, es importante que si encuentra consultas que están siendo bloqueadas por otros procesos, simplemente verifique la bloqueadora raíz que está causando todo el bloqueo analizando y mirando la columna Bloqueado por. Intente considerar solo esa consulta, no todos los procesos que están bloqueados.
Y las consultas que están esperando un recurso específico, las mimas que le brindaran información sobre el Recurso de Espera para que pueda verificar el Tipo de Espera e intentar encontrar la solución para ese problema. Algunas de las estadísticas de espera más comunes se enumeran en una sección completa de tipos de espera de SQL Server de SQL Shack. Ir a través de eso en los detalles para futuras y posteriores acciones.
Las consultas consideradas como Active Expensive y Recent Expensive le brindarán información sobre las consultas que tienen Lecturas lógicas o Tiempo transcurrido alto de CPU.
Puede ir a cada sección para consultas más actualizadas y costosas actuales o recientes. Ordénelos por Tiempo transcurrido, y de Lectura lógica en el Tiempo de CPU uno por uno y verifique el plan de ejecución. Si usted verifica el plan de ejecución, podrá darse cuenta y descubrir por qué estas costosas consultas estaban tomando una cantidad excesiva de tiempo para que pueda tomar todas las medidas adecuadas para resolverlas de manera oportuna y exitosa. Posteriormente le informaré cómo seguir con el Plan de ejecución de consultas de SQL Server más adelante en este artículo, así que esté atento a las instrucciones y directivas operativas de este tema.
La siguiente herramienta a ser utilizada es el “Almacén de consultas“. esta es una herramienta muy útil y podría salvar su vida en una situación extrema que podría ser usada cuando le llaman a media noche para verificar por qué SQL Server estaba lento 1 hora antes.
Generalmente, previamente a SQL Server 2016, no existía ninguna aplicación de terceros o soluciones personalizadas. Usted no podía ni era capaza de poder ver el historial de ejecución de consultas. Por lo tanto, Query Store le ofrecerá una gran cantidad de funcionalidad y adicionalmente de valor agregado por tanto respecto a este tema. Ed Pollack escribió sobre Query Store aquí, así que consulte este artículo, ya que esta discusión y desarrollo es un gran recurso para profundizar en el almacenamiento de consultas.
Si dispone a su alcance de SQL Server 2016 o superior, primero debe habilitarlo en las propiedades de su base de datos. Posteriormente lo que sigue es habilitar Query Store, para poder disponer del mismo y luego tendrá las propiedades de su base de datos como se muestra en la captura de pantalla a continuación:
Después de haber habilitado el Almacén de consultas, usted podrá expandir los objetos de la base de datos y luego ir a las “Consultas principales de consumo de recursos” como se muestra en la siguiente captura de pantalla:
Nota: Permítale al Almacén de consultas disponer de uno o dos días para capturar la carga de producción para que pueda trabajar fácilmente con carga real.
Haga clic con el botón derecho en Consultas de consumo de recursos importantes y principales y seleccione “Ver consultas de consumo de recursos principales”, esta acción lo direccionara a la ventana en la que se muestran estas consultas que consumen muchos recursos. Además, usted podrá personalizar la vista seleccionando una “métrica” apropiada como Duración, Tiempo de CPU, Lectura lógica o Consumo de memoria. A continuación, la segunda cosa que necesitara cambiar es “Estadística”. Usted podrá cambiarlo a Min, Max o Avg. Yo me permitiría recomendar usar la Estadística promedio con todas las métricas mencionadas anteriormente para obtener consultas.
El siguiente paso consiste en resaltar las consultas que consumen muchos recursos. Posteriormente luego de resaltar el valor del gráfico en la ventana del lado izquierdo (como se resalta en la captura de pantalla a continuación) usted obtendrá el plan de ejecución de la consulta en la ventana inferior.
Puede hacer clic en el botón mencionado anteriormente y resaltado a continuación en la ventana del Almacén de consultas para obtener el Texto de consulta real para un análisis más detallado.
Entonces, como se puede advertir a partir de ahora, usted tiene varias formas de obtener consultas y utilizar este recurso de utilización de una alta funcionalidad. Ahora veremos cómo podremos verificar por qué las consultas se ejecutan tan lentamente y fundamentalmente qué parte de la consulta debe repararse (si la misma es necesaria).
Entonces, de este ejemplo utilizare una consulta requerida en la base de datos de muestra de Microsoft “WideWorldImporters“. El TSQL ejecuta un procedimiento almacenado “[Integración]. [GetOrderUpdates]“.
La utilización y manejo del procedimiento almacenado toma alrededor de un segundo y no lo estaré optimizando. Esto es solo para darle un ejemplo de cómo puede saber, cómo se utiliza y emplea este segundo. También queremos saber qué parte de la consulta lleva más tiempo y en qué tabla debemos centrarnos.
Posteriormente en líneas abajo se muestra el procedimiento almacenado de las llamadas y los resultados del mismo.
Así que ahora tenemos esta ejecución y profundizaremos el tema relacionado.
Primeramente, deberemos habilitar las Estadísticas de consulta para esta sesión. Posteriormente, habilitaremos las estadísticas de CPU e IO para esta sesión de consulta incluyendo el TSQL “SET STATISTICS TIME, IO ON”.
Después posteriormente de ejecutar el TSQL mencionado anteriormente para habilitar las estadísticas, luego obtendremos el IO para cada tabla y fundamentalmente el costo total del CPU para las consultas que se ejecutan dentro del procedimiento almacenado en la pestaña Mensajes como se muestra en la siguiente captura de pantalla.
En la captura de pantalla anterior, nosotros podremos ver que la tabla OrderLines tomara a su vez la mayor cantidad de E/S y solo será posible que haya una consulta que se está ejecutando dentro del Procedimiento almacenado que lleva 672 ms de tiempo de CPU (1650 ms de tiempo transcurrido).
Nota: Recuerde que es posible que hayan múltiples consultas ejecutándose dentro de un procedimiento almacenado, así que deberá tener en cuenta que las Estadísticas le darán el tiempo requerido para cada consulta, así como el total de todas las consultas al final. Por lo tanto, en el caso de los procedimientos almacenados para el tiempo total de CPU, usted solo deberá considerar el último tiempo de CPU y para cada consulta solo deberá considerar su tiempo de CPU y especialmente excluir el último tiempo de CPU, ya que solo es un total para todos.
Por ello a partir de ahora, sabemos que la tabla OrderLines está tomando la mayoría de las lecturas lógicas.
Ahora a continuación, habilitaremos el Plan de Ejecución Actual para la consulta. Deberemos hacer clic en el icono 
(Ctrl +M) ) en SQL Server Management Studio e intentaremos responder la pregunta de por qué esta tabla estaba tomando este IO y qué componente del plan de ejecución es el responsable de que está tomando la mayor parte del tiempo.
Después de incluir el Plan de Ejecución Actual, volveremos a re-ejecutar la consulta y veremos el plan de ejecución.
Sin embargo, podemos obtener una información mucho más detallada sobre el Plan de Ejecución Actual de consultas dentro de SQL Server Management Studio, pero hay otra gran herramienta gratuita en la web que se puede utilizar para explorar el Plan de actual de Ejecución de una manera mucho más intuitiva que es el ApexSQL Plan.
Después de instalar esta herramienta, es posible que usted requiera reiniciar SQL Server Management Studio, así que es mejor que lo instale y vuelva a ejecutar la consulta para obtener un Plan de ejecución. Aquí se proporciona un recorrido de captura de pantalla para esta herramienta. Después de ejecutar la consulta, haga clic en el botón derecho en el plan de ejecución y tendrá la opción de “Ver con el ApexSQL Plan“.
Posteriormente luego de ver el Plan de ejecución en el Plan ApexSQL, puede ver los elementos resaltados en la siguiente captura de pantalla que se abrirá en el ApexSQL Plan.
Un par de puntos y consejos aquí para ver:
- Si la tabla está usando la búsqueda de claves, intente eliminar esa búsqueda agregando esas columnas en el índice que está siendo usada por la tabla
- Si el número de filas devueltas por la consulta está excedido muy lejos en comparación con el número de filas devueltas por los operadores de tabla (como se resalta en la sección inferior del plan de consulta), intente volver a escribir la consulta que filtra los datos utilizando más columnas para reducir el número de filas
- Si las filas estimadas y las filas reales tienen una gran diferencia, intente actualizar las estadísticas de las tablas subyacentes.
- Si hay una deficiencia y faltan índices indicados por la consulta, intente evaluar el índice y si ese índice le ayuda a efectuar la consulta, agréguelo a la tabla correspondiente.
Finalmente, si sus consultas funcionan bien en una sola ejecución y solo tienen problemas mientras se ejecutan en la carga de producción, es importante mencionar que para que pueda simular fácilmente la carga de producción para una sola consulta utilizando la Herramienta de Stress SQL de Adam Machanic, la misma que está disponible para descargar desde el enlace aquí. Luego aquí puede capturar esa consulta de ejecución lenta de las técnicas mencionadas anteriormente y ajustarla como corresponde.
Resumen
Las herramientas, técnicas y consejos mencionados anteriormente son las soluciones más comunes para solucionar problemas de consultas de ejecución lenta. Por ello, estas soluciones son comunes, genéricas y en su mayoría las consultas se benefician de ellas.
Es importante para usted tener en cuenta que no existe una regla básica para la optimización del rendimiento y la resolución de problemas de las consultas de ejecución lenta y este tema es demasiado amplio para cubrirlo en pocos artículos, por ello he intentado resumir en un solo artículo algunas de las herramientas y técnicas más importantes y comunes para que la solución de problemas básicos sea fácil.
He has 9+ Years of Database Development & Administration experience with Medical Billing, Startup & Financial Companies. He is a Microsoft Certified Expert for Data Platform (SQL Server 2012/2014).
Currently, performing duties as Senior SQL Server Consultant at multiple Saudi Private and Governmental Organizations.
View all posts by Musab Umair
- Cómo identificar las consultas con ejecución lenta en SQL Server - September 30, 2019