Share via

Menor rendimiento de las consultas después de la actualización de SQL Server 2012 o anterior a 2014 o posterior

  • Artículo

Después de actualizar SQL Server de 2012 o una versión anterior a 2014 o una versión posterior, puede encontrarse con 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 que contribuyen, una causa relativamente común son los cambios en el modelo de estimación de cardinalidad (CE) después de la actualización. A partir de SQL Server 2014 se introdujeron cambios significativos en los modelos ce.

En este artículo se proporcionan pasos y soluciones de solución de 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: identifique si los cambios de CE son el problema y descubra el motivo

Paso 1: Identificar si se usa el CE predeterminado

  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 ventana Propiedades del plan de ejecución, compruebe CardinalityEstimationModelVersion. Busque la versión del modelo CE en la ventana Propiedades del plan de ejecución.
  4. Un valor de 70 indica el CE heredado, y un valor de 120 o superior indica el uso del CE predeterminado.

Si se usa la CE heredada, los cambios de CE no son la causa del problema de rendimiento. Si se usa el CE predeterminado, 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 con 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 4137, 9472 y 4139 correspondientes para probar la consulta. Determine qué sugerencias o marcas de seguimiento afectan positivamente al rendimiento en función de estas pruebas.

Solución

Para resolver el problema, pruebe uno de los métodos siguientes:

  • Optimice la consulta.

    Es comprensible que no siempre sea 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 predeterminadas de CE. 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).

  • Forzar un buen plan.

    Se trata de una opción favorable y se puede usar para dirigirse a consultas específicas. La aplicación de un plan se puede realizar mediante una guía de plan o QDS. Por lo general, QDS es más fácil de usar.

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

    Se trata de un enfoque menos preferido, ya que es una configuración de 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. Sin duda 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 menos dirigido 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 Query Hint o QUERYTRACEON

  • Para SQL Server 2016 SP1 y versiones posteriores, use la sugerencia FORCE_LEGACY_CARDINALITY_ESTIMATION 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 CE heredado. Aquí le mostramos un ejemplo:

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

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

  • 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 al 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 él.

    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 una sugerencia de consulta reemplazadas. Las consultas que funcionan mejor debido a la CE predeterminada pueden retroceder.

Nivel de servidor: usar la marca de seguimiento

Use la marca de seguimiento 9481 para forzar la CE heredada de 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 una sugerencia de consulta reemplazantes. Las consultas que funcionan mejor debido a la CE predeterminada pueden retroceder.

Preguntas más frecuentes

Para 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 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 los movimientos 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 existentes, no se recomienda pasar a la ce predeterminada hasta que se hayan realizado pruebas de regresión suficientes. Si aún tiene dudas, le recomendamos que actualice SQL Server y pase al nivel de compatibilidad disponible más reciente. Como precaución, habilite también la marca de seguimiento 9481 para SQL Server 2014 o configure la configuración ONcon ámbito de base de datos de LEGACY_CARDINALITY_ESTIMATION para SQL Server 2016 y versiones posteriores hasta que tenga la oportunidad de probar.

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

Las mejoras y correcciones futuras relacionadas con el estimador de cardinalidad se centran en las versiones más recientes. La versión 70 es un estado intermedio aceptable. Sin embargo, después de realizar pruebas cuidadosas, se recomienda pasar finalmente 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 de código principal que recibirá inversiones futuras y una cobertura de pruebas más profunda 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 en 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: ¿La ejecución con la CE heredada impedirá que tenga 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 la base de datos asociada. Por ejemplo, una base de datos con LEGACY_CARDINALITY_ESTIMATION habilitada en ejecución 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 se quedará sin soporte técnico la CE heredada?

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

P7: Solo tengo algunas consultas que retrocede con la CE predeterminada, pero la mayoría del rendimiento de las consultas es el mismo o incluso mejorado. ¿Qué debo hacer?

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

Nota:

También hay una QUERYTRACEON opción con la marca de seguimiento 9481, pero debe considerar la posibilidad de usar en USE HINT 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 la opción Sugerencia de consulta o QUERYTRACEON.

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

CE es un problema complejo y los algoritmos se basan en los datos menos que perfectos disponibles para las estimaciones, como las estadísticas de tablas e índices. No hay información sobre algunas construcciones fuera de modelo, como funciones con valores de tabla (TVF) y modelos basados en muchas suposiciones (como la correlación o la independencia de los predicados y columnas, la distribución uniforme de datos, la 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 pasando de 70 a 120, incluyen muchas opciones diferentes para los modelos usados. Por ejemplo, al calcular filtros, suponga algún nivel de correlación entre los predicados porque, en la práctica, dicha correlación existe con frecuencia, y el modelo 70 de CE subestimaría los resultados en tales 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 lo 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 el ajuste de la consulta, al igual que tenía que hacer con la CE heredada si el rendimiento de la consulta no es aceptable, o forzar un modelo ce anterior o un plan de ejecución específico.

P9: ¿Hay algún recurso para obtener detalles sobre los cambios de cardinalidad en el CE predeterminado y el impacto en el rendimiento de la consulta?

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 "¿Qué ha cambiado en SQL Server 2014?".