Compartir a través de

Se ha reducido el rendimiento de las consultas después de la actualización de SQL Server 2012 (o versiones anteriores) a la versión 2014 o posteriores.

  • Artículo

Después de actualizar SQL Server de 2012 o una versión anterior a 2014 o una versión posterior, es posible que encuentre el siguiente problema: la mayoría de las consultas originales se ejecutan bien, pero algunas de las consultas se ejecutan más lentamente que en la versión anterior. Aunque hay muchas causas posibles y factores de contribución, una causa relativamente común es los cambios en el modelo de estimación de cardinalidad (CE) después de la actualización. Se introdujeron cambios significativos en los modelos de CE a partir de SQL Server 2014.

En este artículo se proporcionan pasos y soluciones para solucionar problemas de rendimiento de consultas que se producen al usar la CE predeterminada, pero que no se producen al usar la CE heredada.

Nota

Si todas las consultas se ejecutan más lentamente después de la actualización, es probable que los pasos de solución de problemas introducidos en este artículo no se apliquen a su situación.

Solución de problemas: identificar si los cambios de CE son el problema y averiguar el motivo

Paso 1: Identificar si se usa la ce predeterminada

  1. Elija una consulta que se ejecute más lentamente después de la actualización.
  2. Ejecute la consulta y recopile el plan de ejecución.
  3. En el plan de ejecución ventana Propiedades, compruebe CardinalityEstimationModelVersion. Busque la versión del modelo de CE desde el plan de ejecución ventana Propiedades.
  4. Un valor de 70 indica la CE heredada y un valor de 120 o superior indica el uso de la ce predeterminada.

Si se usa la CE heredada, los cambios de CE no son la causa del problema de rendimiento. Si se usa la ce predeterminada, vaya al paso siguiente.

Paso 2: Identificar si el optimizador de consultas puede generar un mejor plan mediante la ce heredada

Ejecute la consulta con la ce heredada. Si funciona mejor que usar la ce predeterminada, vaya al paso siguiente. Si el rendimiento no mejora, los cambios de CE no son la causa.

Paso 3: Averiguar por qué la consulta funciona mejor con la ce heredada

Pruebe las diversas sugerencias de consulta relacionadas con CE para la consulta. Para SQL Server 2014, use las marcas de seguimiento correspondientes 4137, 9472 y 4139 para probar la consulta. Determine qué sugerencias o marcas de seguimiento afectan positivamente al rendimiento en función de estas pruebas.

Solución

Para solucionar este problema, pruebe uno de los métodos siguientes:

  • Optimice la consulta.

    De manera comprensible, no siempre es posible volver a escribir consultas, pero especialmente cuando solo hay algunas consultas que se pueden volver a escribir, este enfoque debe ser la primera opción. Las consultas escritas de forma óptima funcionan mejor independientemente de las versiones de CE.

  • Use sugerencias de consulta identificadas en el paso 3.

    Este enfoque dirigido permite que otras cargas de trabajo se beneficien de las suposiciones y mejoras de CE predeterminadas. Además, es una opción más sólida que crear una guía de plan. Y no requiere Almacén de consultas (QDS), a diferencia de forzar un plan (la opción más sólida).

  • Fuerza un buen plan.

    Esta es una opción favorable y se puede usar para dirigirse a consultas específicas. Forzar un plan podría realizarse mediante una guía de plan o QDS. QDS suele ser más fácil de usar.

  • Use la configuración con ámbito de base de datos para forzar la ce heredada.

    Este es un enfoque menos preferido, ya que es una configuración para toda la base de datos y se aplica a todas las consultas en esta base de datos. Aun así, a veces es necesario cuando un enfoque dirigido no es factible. Ciertamente es la opción más fácil de implementar.

  • Use la marca de seguimiento 9841 para forzar la ce heredada globalmente. Para ello, use DBCC TRACEON o establezca la marca de seguimiento como parámetro de inicio.

    Este es el enfoque de destino mínimo y solo se debe usar como mitigación temporal cuando no se puede aplicar ninguna de las otras opciones.

Opciones para habilitar la ce heredada

Nivel de consulta: usar la opción Sugerencia de consulta o QUERYTRACEON

  • Para SQL Server 2016 SP1 y versiones posteriores, use FORCE_LEGACY_CARDINALITY_ESTIMATION sugerencias para la consulta, por ejemplo:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

  • Habilite la marca de seguimiento 9481 para forzar un plan de CE heredado. Este es un ejemplo:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (QUERYTRACEON 9481)

Nivel de base de datos: establecer la configuración con ámbito o el nivel de compatibilidad

  • Para SQL Server 2016 y versiones posteriores, modifique la configuración con ámbito de base de datos:

      --Force a specific database to use legacy CE
  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

  -- Validate what databases use legacy CE
  SELECT name, value
      FROM sys.database_scoped_configurations 
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

  • Modifique el nivel de compatibilidad de la base de datos. Es la única opción de nivel de base de datos disponible para SQL Server 2014. Tenga en cuenta que este cambio afecta más que solo a la CE. Para determinar el impacto de los cambios en el nivel de compatibilidad, vaya al nivel de compatibilidad de ALTER DATABASE (Transact-SQL) y examine las tablas "Diferencias" en ella.

    ALTER DATABASE <YourDatabase>
SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level

Nota

Este cambio afectará a todas las consultas que se ejecutan en el contexto de la base de datos para la que se cambia la configuración a menos que se use una marca de seguimiento o sugerencia de consulta invalidando. Las consultas que funcionan mejor debido a la ce predeterminada pueden volver.

Nivel de servidor: usar la marca de seguimiento

Use la marca de seguimiento 9481 para forzar la CE heredada en todo el servidor:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Nota

Este cambio afectará a todas las consultas que se ejecutan en el contexto de la instancia de SQL Server a menos que se use una marca de seguimiento o sugerencia de consulta invalidando. Las consultas que funcionan mejor debido a la ce predeterminada pueden volver.

Preguntas más frecuentes

En el caso de las bases de datos preexistentes que se ejecutan en niveles de compatibilidad inferiores, el flujo de trabajo recomendado para actualizar el procesador de consultas a un nivel de compatibilidad superior se detalla en Cambiar el modo de compatibilidad de la base de datos y Usar los escenarios de uso de Almacén de consultas y Almacén de consultas. La metodología introducida en el artículo se aplica a 130 o posteriores para SQL Server y Azure SQL Database.

P2: No tengo tiempo para probar los cambios de CE. ¿Qué puedo hacer en este caso?

En el caso de las aplicaciones y cargas de trabajo preexistentes, no se recomienda pasar a la ce predeterminada hasta que se hayan realizado pruebas de regresión suficientes. Si sigue teniendo dudas, le recomendamos que siga actualizando SQL Server y pase al nivel de compatibilidad más reciente disponible. Como medida de precaución, habilite también la marca de seguimiento 9481 para SQL Server 2014 o configure la configuración ON de ámbito de base de datos LEGACY_CARDINALITY_ESTIMATION para SQL Server 2016 y versiones posteriores hasta que tenga la oportunidad de probar.

P3: ¿Hay alguna desventaja de usar la CE heredada de forma permanente?

Las futuras mejoras y correcciones relacionadas con el estimador de cardinalidad se centran en versiones más recientes. La versión 70 es un estado intermedio aceptable. Sin embargo, después de realizar pruebas cuidadosas, se recomienda pasar a una versión de CE más reciente para beneficiarse de las correcciones de CE más recientes. Hay una alta probabilidad de cambios en el plan de consulta al pasar de la CE heredada, por lo que debe probar antes de realizar cambios en los sistemas de producción. Los cambios pueden mejorar el rendimiento de las consultas en muchos casos, pero en algunos casos, el rendimiento de las consultas puede degradarse.

Importante

La ce predeterminada es la ruta de acceso al código principal que recibirá una inversión futura y una mayor cobertura de pruebas a largo plazo, por lo que no planee usar la CE heredada indefinidamente.

P4: Tengo miles de bases de datos y no quiero activar manualmente LEGACY_CARDINALITY_ESTIMATION para cada una. ¿Hay un método alternativo?

Para SQL Server 2014, habilite la marca de seguimiento 9481 para usar la ce heredada para todas las bases de datos independientemente del nivel de compatibilidad. Para SQL Server 2016 y versiones posteriores, ejecute la siguiente consulta para recorrer en iteración las bases de datos. La configuración se habilitará incluso cuando la base de datos se restaure o se adjunte a otro servidor.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Para Azure SQL Database, puede crear una incidencia de soporte técnico para que esta marca de seguimiento esté habilitada en el nivel de suscripción, pero no en el nivel de servidor.

P5: ¿Se ejecutará con la ce heredada para evitar que obtenga acceso a nuevas características?

Incluso con LEGACY_CARDINALITY_ESTIMATION habilitado, seguirá teniendo acceso a la funcionalidad más reciente incluida con la versión de SQL Server y el nivel de compatibilidad de base de datos asociado. Por ejemplo, una base de datos con LEGACY_CARDINALITY_ESTIMATION habilitada para ejecutarse en el nivel de compatibilidad de la base de datos 140 en SQL Server 2017 todavía puede beneficiarse de la familia de características de procesamiento de consultas adaptables.

P6: ¿Cuándo dejará de ser compatible la ce heredada?

No tenemos planes para dejar de admitir la ce heredada en este momento. Sin embargo, las futuras mejoras y correcciones relacionadas con el estimador de cardinalidad se centran en versiones más recientes de la CE.

P7: Solo tengo algunas consultas con regresión con la ce predeterminada, pero la mayoría del rendimiento de las consultas es el mismo o incluso mejorado. ¿Cuál debo hacer?

Una alternativa más granular a la marca de seguimiento con ámbito de servidor 9481 o la configuración de ámbito de base de datos LEGACY_CARDINALITY_ESTIMATION es el uso de la construcción USE HINT con ámbito de consulta. Para obtener más información, vea USE HINT query hint argument in SQL Server 2016 and USE HINT (USAR sugerencia de consulta en SQL Server 2016 y USE HINT).

Nota

También hay una opción con la QUERYTRACEON marca de seguimiento 9481, pero debe considerar la posibilidad de usar USE HINT en su lugar, ya que es más limpia semánticamente y no requiere permisos especiales.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION permite establecer el modelo ce del optimizador de consultas en la versión 70, independientemente del nivel de compatibilidad de la base de datos. Consulte Nivel de consulta: Usar sugerencia de consulta o opción QUERYTRACEON.

Como alternativa, si solo hay una consulta problemática con la CE predeterminada, podría forzar un plan de CE heredado almacenado en Almacén de consultas o usarlo FORCE_LEGACY_CARDINALITY_ESTIMATION junto con una guía de plan.

CE es un problema complejo y los algoritmos dependen de los datos menos perfectos disponibles para las estimaciones, como estadísticas de tablas e índices. No hay información sobre algunas construcciones fuera del modelo, como funciones con valores de tabla (TVF) y modelos basados en muchas suposiciones (como correlación o independencia de los predicados y columnas, distribución uniforme de datos, contención, etc.).

Dadas las combinaciones ilimitadas de esquema de cliente, datos y cargas de trabajo, es casi imposible elegir modelos que funcionen para todos los casos. Aunque algunos cambios en la CE predeterminada pueden contener errores (como cualquier otro software) y se pueden corregir, otros problemas se deben a un cambio de modelo.

Los cambios en las versiones de CE, especialmente de 70 a 120, incluyen muchas opciones diferentes para los modelos usados. Por ejemplo, al calcular filtros, supongamos algún nivel de correlación entre los predicados porque, en la práctica, esta correlación existe con frecuencia y el modelo de CE 70 infravaloraría los resultados en estos casos. Aunque esos cambios se probaron para muchas cargas de trabajo y mejoraron muchas consultas, para algunas otras consultas, la CE heredada era una mejor coincidencia y, por tanto, con la ce predeterminada, se pueden observar regresiones de rendimiento.

Desafortunadamente, no se considera un error. En tales situaciones, use una solución alternativa, como ajustar la consulta, al igual que tenía que hacer con la ce heredada si el rendimiento de las consultas no es aceptable o forzar un modelo de CE anterior o un plan de ejecución específico.

P9: ¿Hay algún recurso para obtener información sobre los cambios de cardinalidad en la ce predeterminada y el impacto en el rendimiento de las consultas?

Consulte Optimización de los planes de consulta con el estimador de cardinalidad de SQL Server 2014 para obtener más información y lea la sección "What Changed in SQL Server 2014?" (¿Qué ha cambiado en SQL Server 2014?).