Estimación de cardinalidad (SQL Server)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

El optimizador de consultas de SQL Server es un optimizador basado en el costo. Esto significa que selecciona los planes de consulta cuya ejecución tiene el menor costo de procesamiento estimado. El optimizador de consultas determina el costo de ejecución de un plan de consulta en función de dos factores principales:

• El número total de filas procesadas en cada nivel de un plan de consulta, denominado cardinalidad del plan.
• El modelo de costos del algoritmo determinado por los operadores que se utiliza en la consulta.

El primer factor, la cardinalidad, se utiliza como parámetro de entrada del segundo factor, el modelo de costos. Por lo tanto, una cardinalidad mejorada se traduce en menores costos y, a su vez, en planes de ejecución más rápidos.

La estimación de cardinalidad (CE) en SQL Server calcula la cardinalidad principalmente a partir de histogramas que se crean cuando se generan, manual o automáticamente, índices o estadísticas. En ocasiones, SQL Server también utiliza información de restricciones y nuevas versiones lógicas de consultas para determinar la cardinalidad.

En los siguientes casos, SQL Server no puede calcular con precisión las cardinalidades. Esto deriva en cálculos de costos inexactos que pueden provocar planes de consulta de menor calidad. Si evita estas construcciones en las consultas, el rendimiento de las mismas puede mejorar. En ocasiones es posible utilizar formulaciones de consulta alternativas u otras medidas; esto se indicará en esas ocasiones:

• Consultas con predicados que utilizan operadores de comparación entre distintas columnas de la misma tabla.
• Consultas con predicados que utilizan operadores y se cumple alguna de las siguientes condiciones:
  • No hay estadísticas en las columnas que se utilizan a uno u otro lado de los operadores.
  • La distribución de valores en las estadísticas no es uniforme, pero la consulta busca un conjunto de valores muy selectivos. Esta situación se cumple especialmente cuando el operador es distinto al operador de igualdad (=).
  • El predicado utiliza el operador de comparación No es igual a (!=) o el operador lógico NOT.
• Consultas que utilizan alguna de las funciones integradas de SQL Server o una función escalar definida por el usuario cuyo argumento no es un valor constante.
• Consultas que implican columnas de combinación por medio de operadores aritméticos o de concatenación de cadenas.
• Consultas que comparan variables cuyos valores no se conocen cuando la consulta se compila y optimiza.

En este artículo se explica cómo evaluar y elegir la mejor configuración de estimación de cardinalidad para su sistema. La mayoría de los sistemas se benefician de la última estimación de cardinalidad, porque es la más precisa. Con la estimación de cardinalidad se predice cuántas filas va a devolver la consulta casi con toda seguridad. El optimizador de consultas usa la predicción de cardinalidad para generar el mejor plan de consulta posible. Con estimaciones más precisas, el optimizador de consultas normalmente puede hacer mejor su trabajo a la hora de generar un plan de consulta óptimo.

Es bastante probable que el sistema de aplicaciones tenga una consulta importante cuyo plan cambie a un plan más lento debido a cambios en la estimación de cardinalidad a lo largo de las versiones. Existen diversas técnicas y herramientas para detectar una consulta que se ralentiza debido a problemas de la estimación de cardinalidad. También hay diversas opciones para abordar los problemas de rendimiento resultantes.

Versiones de la estimación de cardinalidad

En 1998, SQL Server 7.0 incorporó una actualización importante de la CE, para la que el nivel de compatibilidad fue 70. Esta versión del modelo de estimación de cardinalidad se establece sobre cuatro suposiciones básicas:

• Independencia: se supone que las distribuciones de datos en diferentes columnas de métodos son independiente entre sí, a menos que la información de correlación esté disponible y se pueda usar.

• Uniformidad: los distintos valores tienen un espaciado uniforme y todos tienen la misma frecuencia. Concretamente, dentro de cada paso del histograma, los distintos valores se distribuyen uniformemente y cada valor tiene la misma frecuencia.

• Contención (simple): los usuarios consultan datos que existen. Por ejemplo, para una combinación de igualdad entre dos tablas, tenga en cuenta la selectividad de predicados ¹ en cada histograma de entrada antes de unir histogramas para estimar la selectividad de combinación.

• Inclusión: para predicados de filtro donde Column = Constant, se supone que la constante existe realmente para la columna asociada. Si un paso del histograma correspondiente no está vacío, se supone que uno de los valores distintos de los pasos coincide con el valor del predicado.

  Recuento de ¹ filas que cumple el predicado.

Las actualizaciones posteriores empezaron por SQL Server 2014 (12.x), que se tradujo en los niveles de compatibilidad de 120 y posteriores. Las actualizaciones de estimación de cardinalidad correspondientes a los niveles 120 y posteriores incorporan suposiciones y algoritmos actualizados que funcionan bien en el almacenamiento de datos modernos y en las cargas de trabajo OLTP. Desde las suposiciones de estimación de cardinalidad de nivel 70, se cambiaron las siguientes suposiciones del modelo a partir de la estimación de cardinalidad de nivel 120:

• Independencia se convierte en Correlación: la combinación de los distintos valores de columna no es necesariamente independiente. Esto podría parecerse más a las consultas de datos reales.
• La contención simple se convierte en contención de base: los usuarios puede que consulten datos que no existen. Por ejemplo, para una combinación de igualdad entre dos tablas, usamos los histogramas de tablas base para calcular la selectividad de combinación y, después, el factor en la selectividad de predicados.

Uso del Almacén de consultas para evaluar la versión de estimación de cardinalidad

A partir de SQL Server 2016 (13.x), el almacén de consultas es una herramienta muy útil para examinar el rendimiento de las consultas. Una vez que el Almacén de consultas esté habilitado, comenzará a realizar un seguimiento del rendimiento de las consultas a lo largo del tiempo, incluso si los planes de ejecución cambian. Supervise el Almacén de consultas para detectar un rendimiento de las consultas con regresión o altos costos. Para obtener más información, consulte Supervisión del rendimiento mediante el Almacén de consultas

Si se está preparando para una actualización a SQL Server o quiere promover un nivel de compatibilidad de la base de datos en cualquier plataforma de SQL Server, considere la posibilidad de actualizar las bases de datos mediante el asistente para la optimización de consultas, ya que le puede ayudar a comparar el rendimiento de las consultas en dos niveles de compatibilidad diferentes.

Uso de eventos extendidos para evaluar la versión de estimación de cardinalidad

Otra opción para llevar un seguimiento del proceso de estimación de cardinalidad consiste en usar el evento extendido denominado query_optimizer_estimate_cardinality. El siguiente código de ejemplo de Transact-SQL se ejecuta en SQL Server. Escribe un archivo .xel en C:\Temp\ (aunque la ruta de acceso se puede cambiar). Cuando abra el archivo .xel en Management Studio, podrá consultar la información detallada de forma muy sencilla.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Para obtener más información sobre los eventos extendidos adaptados para SQL Database, consulte Eventos extendidos en SQL Database.

Pasos para evaluar la versión de estimación de cardinalidad

Estos son los pasos que se pueden realizar para saber si alguna de las consultas más importantes tiene un peor rendimiento tras la estimación de cardinalidad más reciente. Algunos de estos pasos se llevan a cabo ejecutando código de ejemplo incluido en una sección anterior.

1. Abra SQL Server Management Studio (SSMS). Asegúrese de que la base de datos de SQL Server esté establecida en el nivel de compatibilidad más alto disponible.

2. Realice los siguientes pasos preliminares:

   1. Abra SQL Server Management Studio (SSMS).

   2. Ejecute el código de Transact-SQL para asegurarse de que la base de datos de SQL Server está establecida en el nivel de compatibilidad más alto disponible.

   3. Asegúrese de que la configuración LEGACY_CARDINALITY_ESTIMATION de la base de datos está desactivada.

   4. Borre el contenido del almacén de consultas. En la base de datos, asegúrese de que el almacén de consultas está habilitado.

   5. Ejecute la instrucción SET NOCOUNT OFF;.

3. Ejecute la instrucción SET STATISTICS XML ON;.

4. Ejecute la consulta importante.

5. En la pestaña Mensajes del panel de resultados, anote el número real de filas afectadas.

6. En la pestaña Resultados del panel de resultados, haga doble clic en la celda que contiene las estadísticas en formato XML. Se abre un plan de consulta gráfico.

7. Haga clic con el botón derecho en el primer cuadro del plan de consulta gráfico y seleccione Propiedades.

8. A fin de poder compararlos posteriormente con otra configuración, anote los valores de las siguientes propiedades:

   • CardinalityEstimationModelVersion.

   • Número de filas estimado.

   • Costo de E/S estimadoy otras propiedades de estimación similares que tengan que ver con el rendimiento real más que con las predicciones de números de filas.

   • Operación lógica y Operación física. Paralelismo es un buen valor.

   • Modo de ejecución real. Lote es un buen valor, mejor que Fila.

9. Compare el número estimado de filas con el número real de filas. ¿Existe una imprecisión de la estimación de cardinalidad de un 1 % (por encima o por debajo) o de un 10 %?

10. Ejecute SET STATISTICS XML OFF;.

11. Ejecute el código de Transact-SQL para bajar el nivel de compatibilidad de la base de datos en un nivel (por ejemplo, de 130 a 120).

12. Vuelva a ejecutar todos los pasos no preliminares.

13. Compare los valores de propiedad de la estimación de cardinalidad de ambos procesos.

    • ¿Es el porcentaje de imprecisión con la nueva estimación de cardinalidad inferior que con la estimación de cardinalidad anterior?

14. Por último, compare los distintos valores de propiedad de rendimiento de ambos procesos.

    • ¿Usó la consulta un plan diferente en cada una de las estimaciones de cardinalidad?

    • ¿Tuvo la consulta un rendimiento inferior con la estimación de cardinalidad más reciente?

    • A menos que la consulta muestre un mejor rendimiento y con un plan distinto en la estimación de cardinalidad anterior, lo más probable es que la estimación de cardinalidad más reciente sea la que más le convenga.

    • Pero si la consulta se ejecuta con un plan más rápido en la estimación de cardinalidad anterior, considere la posibilidad de obligar al sistema a usar el plan más rápido y omitir la estimación de cardinalidad. De este modo, dispondrá de la estimación de cardinalidad más reciente para todo y, al mismo tiempo, mantendrá el plan más rápido para el caso inusual.

Cómo activar el mejor plan de consulta

Supongamos que, con la estimación de cardinalidad de nivel 120 o superior, se genera un plan de consulta menos eficaz para la consulta. Estas son algunas opciones de las que dispone para activar el mejor plan, ordenadas del ámbito más grande al más pequeño:

• El nivel de compatibilidad de la base de datos se podría establecer en un nivel inferior al último disponible para toda la base de datos.

  • Por ejemplo, al establecer el nivel de compatibilidad 110 o inferior, se activa la estimación de cardinalidad de nivel 70, pero todas las consultas quedan sujetas al modelo de estimación de cardinalidad anterior.

  • Además, si se ajusta un nivel de compatibilidad inferior, también falta una serie de mejoras en el optimizador de consultas para las versiones más recientes. Esto afecta a todas las consultas en la base de datos.

• Puede usar la opción de configuración con ámbito de base de datos LEGACY_CARDINALITY_ESTIMATION para que toda la base de datos use la CE anterior, a la vez que conserva otras mejoras en el optimizador de consultas.

• Puede usar la opción de consulta LEGACY_CARDINALITY_ESTIMATION para que una sola consulta use la estimación de cardinalidad anterior y conservar al mismo tiempo otras mejoras en el optimizador de consultas.

• Puede aplicar LEGACY_CARDINALITY_ESTIMATION mediante la característica Sugerencia del Almacén de consultas, para que una sola consulta use la estimación de cardinalidad anterior sin necesidad de cambiar la consulta.

• Fuerce un plan diferente con el Almacén de consultas.

Nivel de compatibilidad de la base de datos

Para asegurarse de que la base de datos esté en un nivel determinado, use el siguiente código de Transact-SQL para COMPATIBILITY_LEVEL.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

Para las bases de datos que ya existían y que se ejecutan con niveles de compatibilidad inferiores, siempre y cuando la aplicación no necesite usar las mejoras que solo están disponibles en un nivel de compatibilidad de base de datos superior, es un enfoque válido para mantener el nivel de compatibilidad de la base de datos anterior. Para los nuevos trabajos de desarrollo o en el caso de que una aplicación requiera el uso de características nuevas, como el procesamiento inteligente de consultas, así como algunos comportamientos nuevos de Transact-SQL, plantéese actualizar el nivel de compatibilidad de la base de datos a la versión más reciente disponible. Para obtener más información, consulte Actualizaciones del motor de base de datos y niveles de compatibilidad.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

En el caso de una base de datos SQL Server con un nivel de compatibilidad 120 o superior, la activación de la marca de seguimiento 9481 obliga al sistema a usar la versión 70 de la estimación de cardinalidad.

Estimador de cardinalidad heredada

Para una base de datos de SQL Server establecida en el nivel de compatibilidad 120 y posteriores, el estimador de cardinalidad heredado (versión 70 de CE) se puede activar en el nivel de base de datos mediante ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Modificación de la consulta para usar una sugerencia

A partir de SQL Server 2016 (13.x) SP1, modifique la consulta para usar la Sugerencia de consulta USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Establecimiento de una sugerencia del Almacén de consultas

Las consultas se pueden forzar a usar el estimador de cardinalidad heredado sin modificar la consulta, mediante sugerencias del almacén de consultas.

1. Identifique la consulta en las vistas de catálogo del Almacén de consultas sys.query_store_query_text y sys.query_store_query. Por ejemplo, busque una consulta ejecutada por fragmento de texto:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. En el ejemplo siguiente se aplica una sugerencia del Almacén de consultas para forzar el estimador de cardinalidad heredada en el elemento query_id 39, sin modificar la consulta:

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Cómo forzar un plan de consulta particular

Para lograr el mayor control, podría obligar al sistema a usar el plan que se generó con la estimación de cardinalidad de 70 durante las pruebas. Después de asignar un plan de su elección, puede configurar toda la base de datos de forma que use el nivel de compatibilidad y la estimación de cardinalidad más recientes. Pasemos a explicar esta opción.

El Almacén de consultas ofrece diferentes formas de obligar al sistema a usar un plan de consulta en particular:

• Ejecute sys.sp_query_store_force_plan.

• En SQL Server Management Studio (SSMS), expanda el nodo Almacén de consultas, haga clic con el botón derecho en Consultas que más recursos consumen y seleccione Ver consultas que más recursos consumen. La pantalla recoge los botones Forzar plan y No forzar plan.

Para obtener más información sobre el Almacén de consultas, vea Supervisión del rendimiento mediante el Almacén de consultas.

Doblado de constantes y evaluación de las expresiones durante la estimación de cardinalidad

El motor de base de datos evalúa algunas expresiones constantes con antelación para mejorar el rendimiento de las consultas. Es lo que se conoce como doblado de constantes. Una constante es un literal de Transact-SQL, como 3, 'ABC', '2005-12-31', 1.0e3 o 0x12345678. Para obtener más información, vea Doblado de constantes.

Además, durante la optimización, el estimador (de cardinalidad) del tamaño del conjunto de resultados que forma parte del optimizador de consultas evalúa algunas expresiones cuyas constantes no se doblan, pero cuyos argumentos se conocen en tiempo de compilación, tanto si se trata de parámetros como de constantes. Para obtener más información, consulte Evaluación de expresiones.

Prácticas recomendadas: uso del doblado de constantes y la evaluación de expresiones en tiempo de compilación para generar planes de consulta óptimos

Para asegurarse de que genera planes de consulta óptimos, lo mejor es diseñar las consultas, los procedimientos almacenados y los lotes de forma que el optimizador de consultas pueda estimar con exactitud la selectividad de las condiciones de la consulta, en función de las estadísticas sobre la distribución de los datos. De lo contrario, el optimizador de consultas usará una estimación predeterminada al calcular la selectividad.

Para asegurarse de que el estimador de cardinalidad del optimizador de consultas genera unas buenas estimaciones, primero debería asegurarse de que las opciones SET de base de datos AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS se han establecido en ON (valor predeterminado), o de que ha creado manualmente estadísticas en todas las columnas a las que se hace referencia en la condición de la consulta. A continuación, mientras diseña las condiciones de las consultas, realice lo siguiente cuando sea posible:

• Evite el uso de variables locales en las consultas. En su lugar, utilice parámetros, literales o expresiones.

• Limite el uso de operadores y funciones incrustados en una consulta que contenga un parámetro de los incluidos en Evaluación de la expresión en tiempo de compilación para la estimación de la cardinalidad.

• Asegúrese de que las expresiones formadas exclusivamente por constantes de la condición de la consulta admitan el doblado de constantes o se puedan evaluar en tiempo de compilación.

• Si tiene que utilizar una variable local para evaluar una expresión que se utilizará en una consulta, considere la posibilidad de evaluarla en un ámbito distinto al de la consulta. Por ejemplo, podría ser útil realizar una de las siguientes opciones:

  • Pase el valor de la variable a un procedimiento almacenado que contenga la consulta que desee evaluar y haga que la consulta utilice el parámetro del procedimiento, en lugar de una variable local.

  • Cree una cadena que contenga una consulta basada parcialmente en el valor de la variable local y, luego, ejecute la cadena mediante SQL dinámico (EXEC o preferiblemente sp_executesql).

  • Parametrice la consulta y ejecútela mediante sp_executesql. Después, pase el valor de la variable como un parámetro a la consulta.

Ejemplos de mejoras en la estimación de cardinalidad

En esta sección se muestran consultas de ejemplo en las que se saca partido de las mejoras implementadas en la estimación de cardinalidad en versiones recientes. Se trata de información general que no precisa de ninguna acción por su parte.

Ejemplo A. La estimación de cardinalidad entiende que el valor máximo podría ser superior al de las últimas estadísticas recopiladas

Imagine que las estadísticas para OrderTable se recopilaron por última vez en 2016-04-30, cuando el valor OrderAddedDate máximo era 2016-04-30. La estimación de cardinalidad de nivel 120 (y superior) entiende que las columnas de OrderTable, que tienen datos ascendentes, podrían tener valores mayores que el máximo registrado en las estadísticas. Este entendimiento mejora el plan de consulta para instrucciones SELECT de Transact-SQL como la siguiente.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

Ejemplo B. La estimación de cardinalidad entiende que, a menudo, los predicados filtrados en una misma tabla se correlacionan

En la siguiente instrucción SELECT vemos predicados filtrados en Model y ModelVariant. Deducimos de manera intuitiva que, cuando Model es "Xbox", hay una posibilidad de que ModelVariant sea "One", dado que Xbox tiene una variante denominada One.

A partir de la CE de nivel 120, SQL Server entiende que podría haber una correlación entre las dos columnas, Model y ModelVariant, en la misma tabla. La estimación de cardinalidad hace una estimación más precisa del número de filas que la consulta va a devolver, mientras que el optimizador de consultas genera un plan mucho mejor.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

Ejemplo C. La estimación de cardinalidad ya no da por hecho ninguna correlación entre los predicados filtrados de tablas distintas

Las nuevas investigaciones exhaustivas realizadas en cargas de trabajo y datos empresariales reales de hoy día han revelado que los filtros de predicados de tablas distintas no suelen estar correlacionados. En la siguiente consulta, la estimación de cardinalidad asume que no hay ninguna correlación entre s.type y r.date. Por tanto, hace una estimación más baja del número de filas devuelto.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';  

Contenido relacionado

• Comentarios de estimación de cardinalidad (CE)
• Supervisión y optimización del rendimiento
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
• Sugerencias (Transact-SQL): consulta
• Sugerencias de consulta USE HINT
• Actualización de bases de datos mediante el Asistente para la optimización de consultas
• Supervisión del rendimiento mediante el Almacén de consultas
• Guía de arquitectura de procesamiento de consultas
• Sugerencias del Almacén de consultas
• Procesamiento de consultas inteligente en bases de datos SQL
• DBCC TRACEON: marcas de seguimiento (Transact-SQL)
• sys.query_store_query_hints