Identificación de planes de consulta problemáticos

Completado

El enfoque típico que los DBA toman para solucionar problemas de rendimiento de las consultas implica identificar primero la consulta problemática, normalmente la que consume la mayoría de los recursos del sistema y, a continuación, recuperar su plan de ejecución. Hay dos escenarios principales. Un escenario es que la consulta funciona constantemente mal. Esto puede deberse a varios problemas, como las restricciones de recursos de hardware (aunque esto normalmente no afecta a una sola consulta que se ejecuta de forma aislada), la estructura de consulta no óptima, la configuración de compatibilidad de la base de datos, los índices que faltan o las opciones de plan deficientes por parte del optimizador de consultas. El segundo escenario es que la consulta funciona bien en algunas ejecuciones, pero mal en otras. Esta incoherencia puede deberse a factores como la asimetría de datos en una consulta con parámetros, que tiene un plan eficaz para algunas ejecuciones y una mala para otras. Otros factores comunes incluyen el bloqueo, donde una consulta espera a que otra consulta se complete para obtener acceso a una tabla, o la competencia de hardware.

Vamos a explorar cada uno de estos escenarios con más detalle.

Restricciones de hardware

Las restricciones de hardware normalmente no se manifiestan durante las ejecuciones de consultas únicas, pero se vuelven evidentes en la carga de producción cuando los subprocesos de CPU y la memoria están limitados. La contención de CPU se puede detectar mediante la observación del contador del monitor de rendimiento "% Tiempo de Procesador", que mide el uso del CPU en el servidor. En SQL Server, los tipos de espera SOS_SCHEDULER_YIELD y CXPACKET pueden indicar presión en la CPU. Un rendimiento deficiente del sistema de almacenamiento puede ralentizar incluso las ejecuciones de consultas únicas optimizadas. El rendimiento del almacenamiento se monitorea mejor a nivel de sistema operativo utilizando contadores de monitor de rendimiento del sistema Disk Seconds/Read y Disk Seconds/Write, que miden los tiempos de finalización de las operaciones de E/S. SQL Server registra un rendimiento de almacenamiento deficiente si una E/S tarda más de 15 segundos. Las esperas de PAGEIOLATCH_SH elevadas en SQL Server pueden indicar problemas de rendimiento del almacenamiento. El rendimiento del hardware se evalúa normalmente al principio del proceso de solución de problemas debido a su facilidad de evaluación.

La mayoría de los problemas de rendimiento de la base de datos se derivan de patrones de consulta poco óptimos, lo que puede poner presión innecesaria en el hardware. Por ejemplo, los índices que faltan pueden provocar una presión de CPU, almacenamiento y memoria recuperando más datos de los necesarios. Se recomienda solucionar y ajustar las consultas poco óptimas antes de abordar problemas de hardware. A continuación, analizaremos el ajuste de consultas.

Construcciones de consulta poco óptimas

Las bases de datos relacionales funcionan mejor al ejecutar operaciones basadas en conjuntos, que manipulan datos (INSERT, UPDATE, DELETEy SELECT) en conjuntos, lo que genera un valor único o un conjunto de resultados. La alternativa es el procesamiento basado en filas, mediante cursores o bucles while, que aumentan el costo linealmente con el número de filas afectadas, una escala problemática a medida que crecen los volúmenes de datos.

Detectar el uso poco óptimo de operaciones basadas en filas con cursores o bucles WHILE es importante, pero hay otros patrones ineficaces en SQL Server que también deben reconocerse. Las funciones con valores de tabla (TVF), especialmente las TVF de varias instrucciones, causaban patrones problemáticos en el plan de ejecución antes de SQL Server 2017. Los desarrolladores suelen usar TVF de varias instrucciones para ejecutar varias consultas dentro de una sola función y agregar resultados en una sola tabla. Sin embargo, el uso de TVF puede provocar penalizaciones de rendimiento.

SQL Server tiene dos tipos de TVF: inline y multi-statement. Las funciones de valor de tabla insertadas se tratan como vistas, mientras que las funciones de valor de tabla de múltiples declaraciones se tratan como tablas durante el procesamiento de consultas. Dado que las TVF son estadísticas dinámicas y carecen de estadísticas, SQL Server usa un recuento fijo de filas para calcular el costo del plan de consulta. Esto puede ser adecuado para recuentos de filas pequeños, pero ineficaz para miles o millones de filas.

Otro antipatrón es el uso de funciones escalares, que tienen problemas de estimación y ejecución similares. Microsoft ha realizado mejoras significativas en el rendimiento con el procesamiento inteligente de consultas, en niveles de compatibilidad 140 y 150.

SARGability

El término SARGable en las bases de datos relacionales hace referencia a un predicado (WHERE cláusula) con formato para usar un índice para acelerar la ejecución de consultas. Los predicados en el formato correcto se denominan "Argumentos de búsqueda" o SARG. En SQL Server, el uso de un SARG significa que el optimizador utiliza un índice no agrupado en la columna referenciada en el SARG para una operación SEEK, en vez de escanear todo el índice o la tabla para obtener un valor.

La presencia de un SARG no garantiza el uso de un índice para un SEEK. Los algoritmos de costo del optimizador todavía podrían determinar que el índice es demasiado caro, especialmente si un SARG hace referencia a un gran porcentaje de filas de una tabla. La ausencia de un SARG significa que el optimizador no evaluará un SEEK en un índice no clúster.

Algunos ejemplos de expresiones no SARGables incluyen aquellas que tienen una cláusula que utiliza un carácter comodín LIKE al principio de la cadena, como WHERE lastName LIKE '%SMITH%'. Otros predicados no SARGables se producen cuando se usan funciones en una columna, como WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'. Normalmente, estas consultas se identifican mediante el examen de los planes de ejecución de las exploraciones de índice o tabla donde se deberían realizar búsquedas.

Captura de pantalla del plan de ejecución de consulta mediante una función no SARGable.

Existe un índice en la columna City que se usa en la cláusula WHERE de la consulta y mientras se usa en este plan de ejecución anterior, puede ver que se examina el índice, lo que significa que se lee todo el índice. La función LEFT en el predicado hace que esta expresión no sea SARGable. El optimizador no evaluará el uso de una operación de búsqueda de índice en el índice de la columna City.

Esta consulta se puede escribir para que use un predicado SARGable. A continuación, el optimizador evaluaría un SEEK en el índice de la columna City. Un operador index seek, en este caso, leería un conjunto más pequeño de filas.

Captura de pantalla de una consulta y un plan de ejecución con un predicado SARGable.

Cambiar la función LEFT en una LIKE consigue una búsqueda de índice.

Nota:

La LIKE palabra clave, en este ejemplo, no tiene un carácter comodín a la izquierda, por lo que busca ciudades que comiencen por M. Si estuviera "en ambos lados" o comenzara con un carácter comodín ("%M%" o "%M"), no sería SARGable. Se estima que la operación de búsqueda devuelve 1.267 filas o aproximadamente el 15% de la estimación de la consulta con el predicado no SARGable.

Otros antipatrones de desarrollo de bases de datos tratan la base de datos como servicio en lugar de un almacén de datos. El uso de una base de datos para convertir datos en JSON, manipular cadenas o realizar cálculos complejos puede provocar un uso excesivo de la CPU y una mayor latencia. Las consultas que intentan recuperar todos los registros y, a continuación, realizar cálculos en la base de datos pueden provocar un uso excesivo de E/S y CPU. Lo ideal es usar la base de datos para las operaciones de acceso a datos y construcciones de base de datos optimizadas, como la agregación.

Faltan índices

Los problemas de rendimiento más comunes para los administradores de bases de datos proceden de la falta de índices útiles, lo que hace que el motor lea más páginas de las necesarias para devolver los resultados de la consulta. Aunque los índices consumen recursos (lo que afecta al rendimiento de escritura y al espacio que consume), sus ganancias de rendimiento suelen superar los costos de recursos adicionales. Los planes de ejecución con estos problemas se pueden identificar mediante el operador de consulta Clustered Index Scan o la combinación de Búsqueda de índices no agrupados y Búsqueda de claves, lo que indica que faltan columnas en un índice existente.

El motor de base de datos ayuda mediante la notificación de índices que faltan en los planes de ejecución. Los nombres y detalles de los índices recomendados están disponibles a través de la vista sys.dm_db_missing_index_detailsde administración dinámica . Otras DMV como sys.dm_db_index_usage_stats y sys.dm_db_index_operational_stats resaltan el uso de índices existentes.

Quitar un índice sin usar puede ser razonable. Las DMV de índice que faltan y las advertencias de plan deben ser puntos de partida para las consultas de optimización. Es fundamental comprender las preguntas clave y crear índices para apoyarlas. No se recomienda crear todos los índices que faltan sin evaluarlos en contexto.

Estadísticas que faltan y no están actualizadas

Comprender la importancia de las estadísticas de columna e índice en el optimizador de consultas es fundamental. También es esencial reconocer las condiciones que pueden provocar estadísticas obsoletas y cómo este problema puede manifestarse en SQL Server. Las ofertas de Azure SQL tienen de forma predeterminada estadísticas de actualización automática establecidas en ON. Antes de SQL Server 2016, el comportamiento predeterminado de las estadísticas de actualización automática no era actualizar las estadísticas hasta que el número de modificaciones en las columnas del índice equivale a aproximadamente 20% del número de filas de una tabla. Este comportamiento podría dar lugar a modificaciones de datos significativas que cambian el rendimiento de las consultas sin actualizar las estadísticas, lo que conduce a planes poco óptimos basados en estadísticas obsoletas.

Antes de SQL Server 2016, se podría usar la marca de seguimiento 2371 para cambiar el número necesario de modificaciones a un valor dinámico, por lo que a medida que la tabla creció, el porcentaje de modificaciones de fila necesarias para desencadenar una actualización de estadísticas disminuyó. Las versiones más recientes de SQL Server, Azure SQL Database e Instancia administrada de Azure SQL admiten este comportamiento de forma predeterminada. La función sys.dm_db_stats_properties de administración dinámica muestra las estadísticas de la última vez que se actualizaron y el número de modificaciones desde la última actualización, lo que le permite identificar rápidamente las estadísticas que podrían necesitar actualizaciones manuales.

Opciones de optimizador deficientes

Aunque el optimizador de consultas realiza un buen trabajo para optimizar la mayoría de las consultas, hay algunos casos perimetrales en los que el optimizador basado en costos puede tomar decisiones impactantes que no se entienden por completo. Hay muchas maneras de abordar esto, como el uso de sugerencias de consulta, marcas de seguimiento, forzar el plan de ejecución y otros ajustes para alcanzar un plan de consulta estable y óptimo. Microsoft tiene un equipo de soporte técnico que puede ayudar a solucionar estos escenarios.

En el ejemplo siguiente de la base de datos de AdventureWorks2017, se está utilizando un indicador de consulta para indicar al optimizador de la base de datos que use siempre Seattle como nombre de ciudad. Esta sugerencia no garantizará el mejor plan de ejecución para todos los valores de ciudad, pero es predecible. El valor de "Seattle" para @city_name solo se usará durante la optimización. Durante la ejecución, se usa el valor (‘Ascheim’) proporcionado real.

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

Como se muestra en el ejemplo, la consulta usa una sugerencia (la cláusula #D0) para indicar al optimizador que use un valor de variable específico para compilar su plan de ejecución.

Examen de parámetros

SQL Server almacena en caché los planes de ejecución de consultas para su uso futuro. Dado que el proceso de recuperación del plan de ejecución se basa en el valor hash de una consulta, el texto de la consulta debe ser idéntico para cada ejecución de la consulta del plan almacenado en caché que se va a usar. Para admitir varios valores en la misma consulta, muchos desarrolladores usan parámetros, que se pasan a través de procedimientos almacenados, como se muestra en el ejemplo siguiente:

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

Las consultas también se pueden parametrar explícitamente mediante el procedimiento sp_executesql. Sin embargo, la parametrización explícita de consultas individuales se realiza a través de la aplicación con algún formulario (dependiendo de la API) de PREPARE y EXECUTE. Cuando el motor de base de datos ejecuta esa consulta por primera vez, optimiza la consulta en función del valor inicial del parámetro, en este caso, 42. Este comportamiento, denominado examen de parámetros, permite reducir la carga de trabajo general de compilar consultas en el servidor. Sin embargo, si hay asimetría de datos, el rendimiento de las consultas podría variar ampliamente.

Por ejemplo, una tabla que tenía 10 millones de registros y 99% de esos registros tienen un identificador de 1 y las otras 1% son números únicos, el rendimiento se basa en qué identificador se usó inicialmente para optimizar la consulta. Este rendimiento que fluctúa salvajemente es indicativo de sesgo de datos y no es un problema inherente de la detección de parámetros. Este comportamiento es un problema de rendimiento bastante común que debe tener en cuenta. Debe comprender las opciones para aliviar el problema. Hay algunas maneras de solucionar este problema, pero cada una de ellas incluye desventajas:

• Use la sugerencia en la RECOMPILE consulta o la WITH RECOMPILE opción de ejecución en los procedimientos almacenados. Esta sugerencia hace que la consulta o el procedimiento se vuelvan a compilar cada vez que se ejecute, lo que aumentará el uso de la CPU en el servidor, pero siempre usará el valor del parámetro actual.
• Puede usar la sugerencia de consulta OPTIMIZE FOR UNKNOWN. Esta sugerencia hace que el optimizador elija no examinar parámetros y comparar el valor con el histograma de datos de columna. Esta opción no le dará el mejor plan posible, pero permitirá un plan de ejecución coherente.
• Reescriba su procedimiento o consultas agregando lógica en torno a los valores de los parámetros para recompilar solo los parámetros problemáticos conocidos. En el ejemplo siguiente, si el parámetro SalesPersonID es NULL, la consulta se ejecuta con .OPTION (RECOMPILE)

CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

Este ejemplo es una buena solución, pero requiere un esfuerzo de desarrollo bastante grande y una comprensión firme de la distribución de datos. Requiere mantenimiento a medida que cambian los datos.