Statistics

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBase de datos de Azure SQL en Microsoft Fabric

El Optimizador de consultas utiliza estadísticas para crear planes de consulta que mejoren el rendimiento de las consultas. Para la mayoría de las consultas, el optimizador de consultas ya genera las estadísticas necesarias para un plan de consulta de alta calidad. En algunos casos, debe crear estadísticas adicionales o modificar el diseño de la consulta para obtener los mejores resultados. En este artículo se explican los conceptos relativos a la estadística y se proporcionan directrices para emplear la estadística de optimización de consultas de forma eficaz.

Componentes y conceptos

Statistics

Las estadísticas para la optimización de consulta son objetos binarios grandes (BLOB) que contienen información estadística sobre la distribución de valores en una o más columnas de una tabla o vista indizada. El Optimizador de consultas utiliza estas estadísticas para estimar la cardinalidad, es decir, el número de filas, en el resultado de la consulta. Estas estimaciones de cardinalidad permiten al Optimizador de consultas crear un plan de consulta de alta calidad. Por ejemplo, en función de los predicados, el Optimizador de consultas podría usar las estimaciones de cardinalidad para elegir el operador Index Seek en lugar del operador Index Scan, que requiere un uso intensivo de los recursos, si eso mejora el rendimiento de la consulta.

Cada objeto de estadísticas se crea en una lista de una o más columnas de la tabla e incluye un histograma que muestra la distribución de valores en la primera columna. Los objetos de estadísticas en varias columnas también almacenan la información estadística relativa a la correlación de valores entre las columnas. Estas estadísticas de la correlación, o densidades, derivan del número de filas distintas de valores de columna.

Histogram

Un histograma mide la frecuencia de aparición de cada valor distinto en un conjunto de datos. El optimizador de consultas calcula un histograma sobre los valores de la primera columna de clave del objeto de estadísticas; para ello, selecciona los valores de la columna tomando una muestra estadística de las filas o realizando un examen completo de todas las filas de la tabla o vista. Si el histograma se crea a partir de muestras de un conjunto de filas, los totales almacenados para el número de filas y el número de valores distintos son las estimaciones y no es necesario que sean números enteros.

Note

SQL Server compila histogramas solo para una sola columna: la primera columna del conjunto de columnas clave del objeto statistics.

Para crear el histograma, el optimizador de consultas ordena los valores de la columna, calcula el número de valores que coinciden con cada valor distinto de esta y, luego, agrega los valores de la columna en 200 pasos contiguos del histograma como máximo. Cada paso del histograma incluye un rango de valores de columna seguido de un valor de columna de límite superior. El intervalo incluye todos los valores de columna posibles comprendidos entre los valores límite (sin incluir los propios valores límite). El valor de columna ordenado más pequeño es el valor del límite superior del primer paso del histograma.

Más concretamente, SQL Server crea el histograma del conjunto ordenado de valores de columna en tres pasos:

• Inicialización del histograma: en el primer paso, se procesa una secuencia de valores desde el principio del conjunto ordenado y se recopila un máximo de 200 valores de range_high_key, equal_rows, range_rows y distinct_range_rows (range_rows y distinct_range_rows son siempre cero durante este paso). El primer paso finaliza cuando se agota toda la entrada o cuando se encuentran 200 valores.
• Examen con combinación de cubos: cada valor adicional de la columna inicial de la clave de estadísticas se procesa en el segundo paso, en orden de clasificación. Cada valor sucesivo se agrega al último intervalo o se crea un nuevo intervalo al final (esta ordenación es posible porque se ordenan los valores de entrada). Si se crea un nuevo intervalo, el proceso fusiona un par de intervalos vecinos existentes en un solo intervalo. Este par de rangos se selecciona para minimizar la pérdida de información. Este método usa un algoritmo de diferencias máximas para minimizar el número de pasos del histograma a la vez que maximiza las diferencias entre los valores de límite. El número de pasos después de contraer los rangos permanece en 200 a lo largo de este paso.
• Consolidación del histograma: en el tercer paso, se pueden contraer más intervalos si no se pierde una cantidad significativa de información. El número de pasos del histograma puede ser menor que el número de valores distintos, incluso para las columnas con menos de 200 puntos de límite. Por lo tanto, incluso aunque la columna tenga más de 200 valores únicos, es posible que el histograma tenga menos de 200 pasos. Para una columna que consta de solo valores únicos, el histograma consolidado tiene un mínimo de tres pasos.

Note

Si el histograma se crea con un ejemplo en lugar de fullscan, los valores de equal_rows, range_rows, distinct_range_rows y average_range_rows son estimaciones y, por lo tanto, no necesitan ser enteros enteros.

En el diagrama siguiente se muestra un histograma con seis pasos. El área a la izquierda del primer valor límite superior es el primer paso.

Para cada paso del histograma del ejemplo anterior:

• La línea en negrita representa el valor del límite superior (range_high_key) y el número de veces que aparece (equal_rows).

• El área sólida a la izquierda de range_high_key representa el intervalo de valores de columna y el número medio de veces que se produce cada valor de columna (average_range_rows). El valor de average_range_rows en el primer paso del histograma siempre es 0.

• Las líneas de puntos representan los valores muestreados usados para calcular el número total de valores distintos en el intervalo (distinct_range_rows) y el número total de valores del intervalo (range_rows). El optimizador de consultas usa range_rows y distinct_range_rows para calcular average_range_rows y no almacena los valores muestreados.

Vector de densidad

La densidad es información sobre el número de duplicados en una determinada columna o combinación de columnas y se calcula como 1/(número de valores distintos). El optimizador de consultas utiliza las densidades para mejorar las estimaciones de cardinalidad de las consultas que devuelven varias columnas de la misma tabla o vista indexada. A medida que disminuye la densidad, aumenta la selectividad de un valor. Por ejemplo, en una tabla que representa automóviles, muchos automóviles tienen el mismo fabricante, pero cada uno dispone de un único número de identificación de vehículo (NIV). Un índice del NIV es más selectivo que un índice del fabricante, porque NIV tiene una densidad inferior a la del fabricante.

Note

Frecuencia es la información sobre la aparición de cada valor distinto en la primera columna de clave del objeto de estadísticas y se calcula como el row count * density. Puede encontrarse una frecuencia máxima de 1 en las columnas con valores únicos.

El vector de densidad contiene una densidad para cada prefijo de columnas del objeto de estadísticas. Por ejemplo, si un objeto de estadísticas tiene las columnas CustomerIdde clave , ItemIdy Price, la densidad se calcula en cada uno de los siguientes prefijos de columna.

Prefijo de columna	Densidad calculada en
(CustomerId)	Filas con valores que se corresponden con CustomerId
(CustomerId, ItemId)	Filas con valores que se corresponden con CustomerId y ItemId
(CustomerId, ItemId, Price)	Filas con valores que se corresponden con CustomerId, ItemId y Price

Estadísticas filtradas

Las estadísticas filtradas pueden mejorar el rendimiento de las consultas que se seleccionan desde subconjuntos de datos bien definidos. Las estadísticas filtradas utilizan un predicado de filtro para seleccionar el subconjunto de datos que se incluye en las estadísticas. Las estadísticas filtradas bien diseñadas pueden mejorar el plan de ejecución de la consulta en comparación con las estadísticas de tabla completa. Para obtener más información sobre el predicado de filtro, vea CREATE STATISTICS. Para obtener más información sobre los casos en los que conviene crear estadísticas filtradas, consulte la sección Cuándo crear las estadísticas de este artículo.

Opciones de estadísticas

Puede configurar opciones que afectan a cuándo y cómo el sistema crea y actualiza las estadísticas. Solo puede establecer estas opciones en el nivel de base de datos.

opción AUTO_CREATE_STATISTICS

Al activar la opción de creación automática de estadísticas, AUTO_CREATE_STATISTICS, el optimizador de consultas crea estadísticas en columnas individuales del predicado de consulta, según sea necesario, para mejorar las estimaciones de cardinalidad del plan de consulta. Estas estadísticas de columna única se crean en las columnas que aún no tienen un histograma en un objeto de estadísticas existente. La AUTO_CREATE_STATISTICS opción no determina si la base de datos crea estadísticas para índices. Esta opción tampoco genera estadísticas filtradas. Se aplica estrictamente a estadísticas de columna única para la tabla completa.

Cuando el Optimizador de consultas crea las estadísticas como resultado de usar la opción AUTO_CREATE_STATISTICS, el nombre de las estadísticas comienza con _WA. Puede usar la consulta siguiente para determinar si el optimizador de consultas creó estadísticas para una columna de predicado de consulta.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc
        ON s.stats_id = sc.stats_id
        AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;

Opción AUTO_UPDATE_STATISTICS

Al activar la opción de estadísticas de actualización automática, AUTO_UPDATE_STATISTICS, el optimizador de consultas determina cuándo las estadísticas pueden estar obsoletas y las actualiza cuando una consulta las usa. Esta acción también se conoce como recompilación de estadísticas. Las estadísticas se vuelven obsoletas después de que operaciones de inserción, actualización, eliminación o combinación cambien la distribución de los datos en la tabla o la vista indexada. El optimizador de consultas cuenta el número de modificaciones de fila desde la última actualización de estadísticas y compara ese número con un umbral para determinar si las estadísticas podrían estar obsoletas. El umbral se basa en la cardinalidad de la tabla, que es el número de filas de la tabla o vista indizada.

Las estadísticas se marcan como obsoletas a raíz de las modificaciones de las filas incluso cuando la opción AUTO_UPDATE_STATISTICS es OFF. Cuando la AUTO_UPDATE_STATISTICS opción es OFF, el sistema no actualiza las estadísticas, incluso cuando las marca como obsoletas. Los planes siguen usando los objetos de estadísticas obsoletos. Establecer AUTO_UPDATE_STATISTICS en OFF puede provocar planes de consulta poco óptimos y un rendimiento de consultas degradado. Establezca la AUTO_UPDATE STATISTICS opción en ON.

• Hasta SQL Server 2014 (12.x), el motor de base de datos usa un umbral de recompilación basado en el número de filas de la tabla o la vista indexada en el momento en que se evaluaron las estadísticas. El umbral es diferente en función de si una tabla es temporal o permanente.

  Tipo de tabla	Cardinalidad de la tabla (n)	Umbral de recompilación (n.º de modificaciones)
  Temporary	n< 6	6
  Temporary	6 <= n<= 500	500
  Permanent	n<= 500	500
  Temporal o permanente	n> 500	500 + (0,20 * n)

  Por ejemplo, si la tabla contiene 20 000 filas, el cálculo es 500 + (0.2 * 20,000) = 4,500 y las estadísticas se actualizan cada 4500 modificaciones.

• A partir de SQL Server 2016 (13.x) y con el nivel de compatibilidad de la base de datos 130, el Motor de base de datos usa un umbral de recompilación de estadísticas dinámicas decreciente que se ajusta según la cardinalidad de la tabla en el momento en que se evalúan las estadísticas. Con este cambio, las estadísticas de tablas grandes se actualizan con más frecuencia. Sin embargo, si una base de datos tiene un nivel de compatibilidad inferior a 130, se aplican los umbrales de SQL Server 2014 (12.x).

  Tipo de tabla	Cardinalidad de la tabla (n)	Umbral de recompilación (n.º de modificaciones)
  Temporary	n < 6	6
  Temporary	6 <= n <= 500	500
  Permanent	n <= 500	500
  Temporal o permanente	n > 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  Por ejemplo, si la tabla contiene 2 millones de filas, el cálculo es el mínimo de 500 + (0.20 * 2,000,000) = 400,500 y SQRT(1,000 * 2,000,000) = 44,721. Esto significa que las estadísticas se actualizan cada 44.721 modificaciones.

Important

En SQL Server 2008 R2 (10.50.x) a SQL Server 2014 (12.x) o en SQL Server 2016 (13.x) y versiones posteriores en el nivel de compatibilidad de la base de datos 120 y versiones posteriores, habilite la marca de seguimiento 2371 para que SQL Server use un umbral de actualización de estadísticas dinámicas decreciente.

Aunque se recomienda en todos los escenarios, la habilitación de la marca de seguimiento 2371 es opcional. Sin embargo, puede usar la siguiente guía para habilitar la marca de seguimiento 2371 en su ambiente anterior a SQL Server 2016 (13.x):

• Si está en un sistema SAP, habilite este seguimiento. Para más información, consulte este blog sobre la marca de seguimiento 2371.
• Si tiene que depender de una tarea nocturna para actualizar las estadísticas porque la actualización automática actual no se activa con suficiente frecuencia, considere habilitar el indicador de seguimiento 2371 para ajustar el umbral en función de la cardinalidad de la tabla.

El Optimizador de consultas comprueba que hay estadísticas obsoletas antes de compilar una consulta y antes de ejecutar un plan de consulta almacenado en la memoria caché. Antes de compilar una consulta, el optimizador de consultas usa las columnas, las tablas y las vistas indizadas del predicado de consulta para determinar qué estadísticas podrían estar obsoletas. Antes de ejecutar un plan de consulta almacenado en caché, Motor de base de datos comprueba que el plan de consulta haga referencia a las estadísticas actualizadas.

La AUTO_UPDATE_STATISTICS opción se aplica a los objetos de estadísticas creados para índices, columnas únicas en predicados de consulta y estadísticas creadas con la CREATE STATISTICS instrucción . Esta opción también se aplica a las estadísticas filtradas.

Puede usar el sys.dm_db_stats_properties para realizar un seguimiento preciso del número de filas cambiadas en una tabla y decidir si desea actualizar las estadísticas manualmente.

AUTO_UPDATE_STATISTICS siempre es OFF para las tablas optimizadas para memoria.

AUTO_UPDATE_STATISTICS_ASYNC

La opción de actualización asincrónica de estadísticas AUTO_UPDATE_STATISTICS_ASYNC determina si el optimizador de consultas usa actualizaciones sincrónicas o asincrónicas de las estadísticas. De forma predeterminada, la opción de actualización asincrónica de estadísticas es OFFy el optimizador de consultas actualiza las estadísticas de forma sincrónica. La AUTO_UPDATE_STATISTICS_ASYNC opción se aplica a los objetos de estadísticas creados para índices, columnas únicas en predicados de consulta y estadísticas creadas con la CREATE STATISTICS instrucción .

Note

Para establecer la opción de actualización asincrónica de estadísticas en SQL Server Management Studio, en la página Opciones de la ventana Propiedades de la base de datos, establezca Estadísticas de actualización automática y Estadísticas de actualización automática de forma asincrónica en True.

Las actualizaciones de las estadísticas pueden ser sincrónicas (el valor predeterminado) o asincrónicas.

• Con las actualizaciones sincrónicas de las estadísticas, las consultas siempre se compilan y ejecutan con estadísticas actualizadas. Si las estadísticas no están actualizadas, el optimizador de consultas esperará a que lo estén antes de compilar y ejecutar la consulta.

• En el caso de las actualizaciones asincrónicas de las estadísticas, las consultas se compilan con estadísticas existentes, incluso aunque estas no estén actualizadas. Si, al compilar la consulta, las estadísticas no están actualizadas, el optimizador de consultas podría elegir un plan de consultas poco óptimo. Las estadísticas suelen actualizarse poco después. Las consultas que se compilan después de que las actualizaciones de estadísticas se completen se benefician del uso de las estadísticas actualizadas.

Considere la posibilidad de usar las estadísticas sincrónicas al realizar las operaciones que cambian la distribución de los datos, como truncar una tabla o realizar una actualización masiva de un gran porcentaje de las filas. Si no actualiza manualmente las estadísticas después de completar la operación, el uso de estadísticas síncronas garantiza que las estadísticas estén actualizadas antes de que las consultas las necesiten.

Considere el uso de estadísticas asincrónicas para lograr tiempos de respuesta a la consulta más predecibles en los escenarios siguientes:

• Su aplicación ejecuta frecuentemente la misma consulta, consultas similares o los planes de consulta almacenados en memoria caché similares. Sus tiempos de respuesta a la consulta podrían ser más predecibles con actualizaciones asincrónicas de las estadísticas que con actualizaciones sincrónicas, porque el Optimizador de consultas puede ejecutar las consultas de entrada sin esperar a que las estadísticas se actualicen. Esto evita que se retrasen algunas consultas, pero no otras.

• La aplicación ha experimentado tiempos de espera de solicitud de cliente causados por una o varias consultas que esperan estadísticas actualizadas. En algunos casos, esperar estadísticas sincrónicas podría provocar un error en las aplicaciones con tiempos de espera agresivos.

Note

Las estadísticas de las tablas temporales locales siempre se actualizan sincrónicamente independientemente de la AUTO_UPDATE_STATISTICS_ASYNC opción. Las estadísticas de las tablas temporales globales se actualizan de forma sincrónica o asincrónica según la AUTO_UPDATE_STATISTICS_ASYNC opción establecida para la base de datos de usuario.

La actualización asincrónica de las estadísticas se realiza mediante una solicitud en segundo plano. Cuando la solicitud está lista para escribir estadísticas actualizadas en la base de datos, intenta adquirir un bloqueo de modificación del esquema en el objeto de metadatos de estadísticas. Si una sesión diferente ya mantiene un bloqueo en el mismo objeto, la actualización asincrónica de las estadísticas se bloqueará hasta que se pueda adquirir el bloqueo de modificación del esquema. Del mismo modo, las sesiones que deban adquirir un bloqueo de estabilidad de esquema (Sch-S) en el objeto de metadatos de estadísticas para compilar una consulta pueden quedar bloqueadas por la sesión en segundo plano de actualización de estadísticas, que ya contiene el bloqueo de modificación de esquema o está a la espera de adquirirlo. Por lo tanto, para las cargas de trabajo con compilaciones de consultas muy frecuentes y las actualizaciones frecuentes de las estadísticas, el uso de estadísticas asincrónicas puede aumentar la probabilidad de que se produzcan incidencias de simultaneidad debido a los bloqueos.

En Azure SQL Database, Azure SQL Managed Instance y a partir de SQL Server 2022 (16.x), puede evitar posibles problemas de simultaneidad mediante la actualización asincrónica de estadísticas si habilita la ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYconfiguración con ámbito de base de datos. Con esta configuración habilitada, la solicitud en segundo plano espera a adquirir el bloqueo de modificación del esquema (Sch-M) y conserva las estadísticas actualizadas en una cola de prioridad baja independiente, lo que permite que otras solicitudes sigan compilando consultas con estadísticas existentes. Una vez que ninguna otra sesión mantiene un bloqueo sobre el objeto de metadatos de estadísticas, la solicitud en segundo plano adquiere un bloqueo de modificación del esquema y actualiza las estadísticas. En el improbable caso de que la solicitud en segundo plano no pueda adquirir el bloqueo dentro de un período de tiempo de espera de varios minutos, se anula la actualización asincrónica de estadísticas y las estadísticas no se actualizan hasta que se desencadene otra actualización automática de estadísticas o hasta que las estadísticas se actualicen manualmente.

Note

La ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY opción de configuración con ámbito de base de datos está disponible en Azure SQL Database, Azure SQL Managed Instance y en SQL Server a partir de SQL Server 2022 (16.x).

opción AUTO_DROP

Se aplica a: Azure SQL Database, Azure SQL Managed Instance y a partir de SQL Server 2022 (16.x)

En SQL Server antes de SQL Server 2022 (16.x), si crea manualmente estadísticas o usa una herramienta de terceros en una base de datos de usuario, esos objetos de estadísticas pueden bloquear o interferir con los cambios de esquema.

A partir de SQL Server 2022 (16.x), la opción auto drop está habilitada de manera predeterminada en todas las bases de datos nuevas y migradas. Si habilita la AUTO_DROP propiedad , la base de datos crea objetos de estadísticas en un modo de modo que el objeto estadístico no bloquee un cambio de esquema posterior, sino que las estadísticas se quitan según sea necesario. De esta manera, las estadísticas creadas manualmente con auto drop habilitado se comportan como las estadísticas creadas automáticamente.

En Azure SQL Database, Azure SQL Managed Instance y SQL Server 2022 (16.x) y versiones posteriores, las estadísticas creadas automáticamente siempre se comportan como si la AUTO_DROP estuviera habilitada.

Note

Si se intenta establecer o anular la propiedad auto drop en las estadísticas creadas automáticamente, se pueden producir errores. Las estadísticas creadas automáticamente siempre usan la eliminación automática. Algunas copias de seguridad, cuando se restauran, pueden tener esta propiedad establecida incorrectamente hasta la próxima vez que se actualice el objeto de estadísticas (manual o automáticamente). Sin embargo, las estadísticas creadas automáticamente siempre se comportan como estadísticas de eliminación automática. Al restaurar una base de datos a SQL Server 2022 (16.x) desde una versión anterior, se recomienda ejecutar sp_updatestats en la base de datos, estableciendo los metadatos adecuados para la característica de eliminación automática de estadísticas.

Por ejemplo, para crear manualmente un objeto de estadísticas en la tabla dbo.DatabaseLog:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Por ejemplo, para actualizar una configuración de colocación automática del objeto de estadísticas en la tabla dbo.DatabaseLog:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Para evaluar la configuración de eliminación automática en las estadísticas existentes, use la columna auto_drop en sys.stats:

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Para obtener más información, consulte AUTO_DROP.

INCREMENTAL

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

Al establecer la INCREMENTAL opción de CREATE STATISTICS en ON, se crean estadísticas por partición. Cuando se establece en OFF, la base de datos elimina el árbol de estadísticas y recalcula las estadísticas. El valor predeterminado es OFF. Esta configuración invalida la propiedad de nivel INCREMENTAL de base de datos.

• Para obtener más información sobre cómo crear estadísticas incrementales, vea CREATE STATISTICS.
• Para obtener más información sobre cómo crear estadísticas por partición automáticamente, vea Propiedades de base de datos (página Opciones) y ALTER DATABASE SET opciones.

Al agregar nuevas particiones a una tabla grande, debe actualizar las estadísticas para incluir las nuevas particiones. Sin embargo, el tiempo necesario para examinar toda la tabla (FULLSCAN o SAMPLE opciones) puede ser largo. Además, no es necesario examinar toda la tabla porque puede que solo se necesiten las estadísticas de las particiones nuevas. La opción incremental crea y almacena estadísticas por partición y, cuando se actualiza, solo actualiza las estadísticas en esas particiones que necesitan nuevas estadísticas.

Si no se admiten estadísticas por partición, la base de datos omite la opción y genera una advertencia. Las estadísticas incrementales no se admiten para los siguientes tipos de estadísticas:

• Estadísticas creadas con índices que no están alineados por partición con la tabla base.
• Estadísticas creadas sobre bases de datos secundarias legibles AlwaysOn.
• Estadísticas creadas sobre bases de datos de solo lectura.
• Estadísticas creadas sobre índices filtrados.
• Estadísticas creadas sobre vistas.
• Estadísticas creadas sobre tablas internas.
• Estadísticas creadas con índices espaciales o índices XML.

Al crear estadísticas

El Optimizador de consultas ya permite crear las estadísticas de las siguientes formas:

1. Al crear un índice en tablas o vistas, el optimizador de consultas crea estadísticas para los índices. Estas estadísticas se crean en las columnas de clave del índice. Si el índice es un índice filtrado, el Optimizador de consultas crea las estadísticas filtradas en el mismo subconjunto de filas especificado para el índice filtrado. Para obtener más información sobre los índices filtrados, vea Creación de índices filtrados y CREATE INDEX.

   Note

   En SQL Server 2014 (12.x) y versiones posteriores, la base de datos no crea estadísticas examinando todas las filas de la tabla al crear o recompilar un índice con particiones. En su lugar, el Optimizador de consultas usa el algoritmo de muestreo predeterminado para generar estadísticas. Después de actualizar una base de datos con índices con particiones, puede observar una diferencia en los datos del histograma para estos índices. Este cambio de comportamiento puede no afectar al rendimiento de las consultas. Para obtener estadísticas de los índices con particiones analizando todas las filas de la tabla, use CREATE STATISTICS o UPDATE STATISTICS con la cláusula FULLSCAN.

2. El optimizador de consultas crea las estadísticas para las columnas únicas de predicados de consulta cuando AUTO_CREATE_STATISTICS está activada.

Para la mayoría de las consultas, estos dos métodos para crear estadísticas garantizan un plan de consulta de alta calidad. En algunos casos, puede mejorar los planes de consulta creando estadísticas adicionales mediante la instrucción CREATE STATISTICS. Estas estadísticas adicionales pueden capturar correlaciones estadísticas que el optimizador de consultas no tiene en cuenta cuando crea estadísticas para índices o columnas únicas. Su aplicación podría tener las correlaciones estadísticas adicionales en los datos de la tabla que, si se calcula en un objeto de estadísticas, podrían habilitar el Optimizador de consultas para mejorar los planes de consulta. Por ejemplo, las estadísticas filtradas en un subconjunto de filas de datos o las estadísticas de varias columnas en columnas de predicado de consulta podrían mejorar el plan de consulta.

Al crear estadísticas mediante la CREATE STATISTICS instrucción , mantenga la AUTO_CREATE_STATISTICS opción ON para que el optimizador de consultas continúe creando de forma rutinaria estadísticas de una sola columna para las columnas de predicado de consulta. Para obtener más información sobre los predicados de consulta, consulte Condición de búsqueda.

Considere la posibilidad de crear estadísticas mediante la CREATE STATISTICS instrucción cuando se aplique cualquiera de las condiciones siguientes:

• El Asistente para la optimización de motor de base de datos sugiere crear las estadísticas.
• El predicado de consulta contiene varias columnas correlacionadas que aún no son claves en el mismo índice.
• La consulta realiza la selección entre un subconjunto de datos.
• La consulta ha perdido estadísticas.

Note

Para obtener información específica de las tablas y estadísticas relacionadas con OLTP en memoria, consulte Estadísticas para las tablas optimizadas para memoria.

El predicado de consulta contiene varias columnas correlacionadas

Cuando un predicado de consulta contiene varias columnas que tienen relaciones y dependencias entre columnas, las estadísticas sobre esas columnas podrían mejorar el plan de consulta. Las estadísticas de varias columnas contienen estadísticas de correlación entre columnas, denominadas densidades, que no están disponibles en las estadísticas de una sola columna. Las densidades pueden mejorar las estimaciones de cardinalidad cuando los resultados de la consulta dependen de relaciones de los datos entre varias columnas.

Si las columnas ya están en el mismo índice, el objeto de estadísticas de varias columnas ya existe y no es necesario crearlo manualmente. Si las columnas aún no están en el mismo índice, puede crear estadísticas de varias columnas mediante la creación de un índice en las columnas o mediante la CREATE STATISTICS instrucción . Se necesitan más recursos del sistema para mantener un índice que para mantener un objeto de estadísticas. Si la aplicación no requiere el índice de varias columnas, puede economizar en los recursos del sistema mediante la creación del objeto de estadísticas sin crear el índice.

Al crear estadísticas de varias columnas, el orden de las columnas de la definición de objeto de estadísticas afecta a la eficacia de las densidades para realizar estimaciones de cardinalidad. El objeto de estadísticas almacena las densidades correspondientes a cada prefijo de las columnas de clave en la definición del objeto de estadísticas. Para obtener más información sobre las densidades, consulte la sección Densidad de este artículo.

Para crear densidades que sean útiles para las estimaciones de cardinalidad, las columnas del predicado de consulta deben coincidir con uno de los prefijos de columnas de la definición del objeto de estadísticas. Por ejemplo, en el siguiente caso se crea un objeto de estadísticas de varias columnas en las columnas LastName, MiddleName y FirstName.

USE AdventureWorks2022;
GO

IF EXISTS (SELECT name
           FROM sys.stats
           WHERE name = 'LastFirst'
                 AND object_ID = OBJECT_ID('Person.Person'))
    DROP STATISTICS Person.Person.LastFirst;
GO

CREATE STATISTICS LastFirst
    ON Person.Person(LastName, MiddleName, FirstName);
GO

En este ejemplo, el objeto de estadísticas LastFirst tiene densidades para los siguientes prefijos de columna: (LastName), (LastName, MiddleName) y (LastName, MiddleName, FirstName). La densidad no está disponible para (LastName, FirstName). Si la consulta usa LastName y FirstName sin usar MiddleName, la densidad no está disponible para las estimaciones de cardinalidad.

La consulta selecciona de un subconjunto de datos

Cuando el Optimizador de consultas crea las estadísticas para las columnas únicas e índices, crea las estadísticas para los valores de todas las filas. Cuando las consultas realizan la selección de entre un subconjunto de filas, y ese subconjunto de filas tiene una distribución de datos única, las estadísticas filtradas pueden mejorar los planes de consulta. Puede crear estadísticas filtradas mediante el uso de la instrucción CREATE STATISTICS con la cláusula WHERE para definir la expresión de predicado del filtro.

Por ejemplo, con AdventureWorks2025, cada producto de la Production.Product tabla pertenece a una de las cuatro categorías de la Production.ProductCategory tabla: Bikes, Components, Clothingy Accessories. Cada una de las categorías tiene una distribución de datos diferente en función del peso: el peso de las bicicletas (bikes) va de 13,77 a 30,0, el de los componentes (components) de 2,12 a 1050,00 con algunos valores NULL, todos los pesos de la ropa (clothing) son NULL, lo mismo que los de los accesorios (accessories) NULL.

Utilizando las Bikes como ejemplo, las estadísticas filtradas para todos los pesos de las bicicletas proporcionarán estadísticas más precisas al Optimizador de consultas y podrán mejorar la calidad del plan de consulta en comparación con las estadísticas de tabla completa o las estadísticas no existentes en la columna del peso (Weight). La columna de peso de bicicleta es una buena candidata para las estadísticas filtradas, pero no necesariamente para un índice filtrado si el número de búsquedas de peso es relativamente pequeño. La ganancia de rendimiento para las búsquedas que proporciona un índice filtrado no podría ser mayor que el mantenimiento adicional y el costo de almacenamiento de agregar un índice filtrado a la base de datos.

La siguiente instrucción crea las BikeWeights estadísticas filtradas para todas las subcategorías de Bikes. La expresión de predicado filtrado define las bicicletas enumerando todas las subcategorías de bicicleta con la comparación Production.ProductSubcategoryID IN (1,2,3). El predicado no puede usar el Bikes nombre de categoría porque está almacenado en la Production.ProductCategory tabla y todas las columnas de la expresión de filtro deben estar en la misma tabla.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

El Optimizador de consultas puede utilizar estadísticas filtradas de BikeWeights para mejorar el plan de consulta correspondiente a la consulta siguiente. Esta segunda selecciona todas las bicicletas cuyo peso es superior a 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER JOIN Production.ProductSubcategory AS S
         ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
      AND P.Weight > 25
ORDER BY P.Weight;
GO

Consulta que identifica las estadísticas que faltan

Si un error u otro evento evitan que el Optimizador de consultas cree las estadísticas, el Optimizador de consultas creará el plan de consulta sin utilizar las estadísticas. El Optimizador de consultas marca las estadísticas como perdidas e intenta regenerar las estadísticas la siguiente vez que se ejecuta la consulta.

Las estadísticas perdidas se indican mediante advertencias (el nombre de la tabla aparece en rojo) cuando el plan de ejecución de una consulta se representa gráficamente mediante SQL Server Management Studio. Además, la supervisión de la clase de eventos Missing Column Statistics con SQL Server Profiler indica cuándo se han perdido las estadísticas. Para obtener más información, consulte Categoría de eventos de errores y advertencias (motor de base de datos).

Si se han perdido estadísticas, siga estos pasos:

• Compruebe que AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS están activadas.
• Compruebe que la base de datos no es de solo lectura. Si la base de datos es de solo lectura, no se puede guardar un nuevo objeto de estadísticas.
• Cree las estadísticas que faltan mediante la CREATE STATISTICS instrucción .

Estadísticas temporales

Cuando faltan las estadísticas de una base de datos de solo lectura o de una instantánea de solo lectura o son obsoletas, motor de base de datos crea y mantiene estadísticas temporales en tempdb. Cuando el motor de base de datos crea estadísticas temporales, el nombre de las estadísticas se anexan con el sufijo _readonly_database_statistic para diferenciar las estadísticas temporales de las permanentes. El sufijo _readonly_database_statistic está reservado para las estadísticas generadas por el motor de base de datos. Los scripts de las estadísticas temporales se pueden crear y ejecutar en una base de datos de lectura y escritura. Cuando se crea el script, Management Studio cambia el sufijo del nombre de las estadísticas de _readonly_database_statistic a _readonly_database_statistic_scripted.

Solo el motor de base de datos puede crear y actualizar estadísticas temporales. No obstante, puede eliminar las estadísticas temporales y supervisar las propiedades de estadísticas mediante las mismas herramientas que se usan para las estadísticas permanentes:

• Elimine las estadísticas temporales mediante la DROP STATISTICS instrucción .
• Consulte las estadísticas mediante las vistas de catálogo sys.stats y sys.stats_columns. La vista de catálogo del sistema sys.stats incluye la columna is_temporary para indicar las estadísticas que son permanentes y las que son temporales.

Dado que las estadísticas temporales se almacenan en tempdb, un reinicio del motor de base de datos quita todas las estadísticas temporales.

Al igual que para todas las estadísticas, la creación y actualización de estadísticas temporales requiere un bloqueo de modificación de esquema (Sch-M) en el objeto. Este bloqueo podría bloquear otras consultas y procesos, incluido el proceso de puesta al día del sistema en réplicas secundarias que aplica transacciones de la réplica principal. Si este bloqueo afecta a las cargas de trabajo de consulta o a la propagación de datos, puede deshabilitar la creación y actualización automáticas de estadísticas temporales mediante las READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATEREADABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE y respectivamente.

Cuándo actualizar las estadísticas

El optimizador de consultas determina cuándo las estadísticas pueden estar obsoletas y, a continuación, las actualiza cuando es necesario para un plan de consulta. En algunos casos, puede mejorar el plan de consulta y, por tanto, mejorar el rendimiento de las consultas actualizando las estadísticas con más frecuencia que cuando AUTO_UPDATE_STATISTICS es ON. Puede actualizar las estadísticas mediante la instrucción UPDATE STATISTICS o el procedimiento almacenado sp_updatestats.

La actualización de las estadísticas asegura que las consultas se compilan con estadísticas actualizadas. La actualización de estadísticas a través de cualquier proceso puede hacer que los planes de consulta se vuelvan a compilar automáticamente. No actualice manualmente las estadísticas con demasiada frecuencia porque hay un equilibrio de rendimiento entre mejorar los planes de consulta y el tiempo necesario para volver a compilar las consultas. Las compensaciones específicas dependen de su aplicación.

Al actualizar las estadísticas mediante UPDATE STATISTICS o sp_updatestats, mantenga AUTO_UPDATE_STATISTICS establecido en ON para que el optimizador de consultas actualice las estadísticas de forma rutinaria.

• Para obtener más información sobre cómo actualizar las estadísticas en una columna, un índice, una tabla o una vista indizada, vea UPDATE STATISTICS.

• Para obtener más información sobre cómo actualizar las estadísticas para todas las tablas internas y definidas por el usuario de la base de datos, vea el procedimiento almacenado sp_updatestats.

• Para obtener más información sobre los umbrales de las actualizaciones de estadísticas automáticas, consulte Opción AUTO_UPDATE_STATISTICS.

Cuando estableces AUTO_UPDATE_STATISTICS en OFF, la recompilación del plan aún puede producirse por otros diversos motivos, pero no se produce automáticamente debido a actualizaciones de estadísticas desactualizadas. Cuando establece AUTO_UPDATE_STATISTICS en OFF, las actualizaciones de las estadísticas solo se producen a través de otros procesos programados manualmente, como los planes de mantenimiento. Por lo tanto, establecer AUTO_UPDATE_STATISTICS en OFF puede provocar planes de consulta poco óptimos y un rendimiento de consultas degradado.

Detección de estadísticas no actualizadas

Para determinar cuándo se actualizaron por última vez las estadísticas, use las funciones sys.dm_db_stats_properties o STATS_DATE.

Considere la actualización de las estadísticas en las condiciones siguientes:

• Los tiempos de ejecución de la consulta son lentos.
• Se producen operaciones de inserción en columnas de clave ascendentes o descendentes.
• Después de las operaciones de mantenimiento.

Para obtener ejemplos de actualización manual de estadísticas, consulte UPDATE STATISTICS.

Los tiempos de ejecución de la consulta son lentos

Si los tiempos de respuesta de la consulta son lentos o impredecibles, asegúrese de que las consultas tienen estadísticas actualizadas antes de realizar los pasos adicionales de la solución de problemas.

Se producen operaciones de inserción en columnas de clave ascendentes o descendentes

Las estadísticas de columnas de clave ascendentes o descendentes, como IDENTITY o columnas de marca de tiempo en tiempo real, pueden requerir actualizaciones de estadísticas más frecuentes que las que realiza el optimizador de consultas. Las operaciones de inserción anexan nuevos valores a las columnas ascendentes o descendentes. El número de filas agregado podría ser demasiado pequeño para desencadenar una actualización de las estadísticas. Si las estadísticas no están actualizadas y las consultas se seleccionan de las filas recientemente agregadas, las estadísticas actuales no tendrán estimaciones de cardinalidad para estos nuevos valores. Esta condición puede dar lugar a estimaciones de cardinalidad inexactas y al rendimiento lento de las consultas.

Por ejemplo, una consulta que selecciona entre las fechas de pedido de ventas más recientes tiene estimaciones de cardinalidad inexactas si las estadísticas no se actualizan para incluir estimaciones de cardinalidad para las fechas de pedido de ventas más recientes.

Después de las operaciones de mantenimiento

Considere la actualización de las estadísticas después de haber realizado procedimientos de mantenimiento que cambian la distribución de los datos, como truncar una tabla o realizar una inserción masiva de un porcentaje grande de las filas. La actualización proactiva de estadísticas puede evitar retrasos futuros en el procesamiento de consultas mientras las consultas esperan actualizaciones automáticas de estadísticas.

Las operaciones como volver a generar, desfragmentar o reorganizar un índice no cambian la distribución de datos. Por lo tanto, no es necesario actualizar las estadísticas después de realizar ALTER INDEX operaciones REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE . El optimizador de consultas actualiza las estadísticas cuando se reconstruye un índice en una tabla o vista con ALTER INDEX REBUILD o DBCC DBREINDEX; sin embargo, esta actualización de las estadísticas es un subproducto de la recreación del índice. El Optimizador de consultas no actualiza las estadísticas después de las operaciones DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE.

Tip

A partir de SQL Server 2016 (13.x) SP1 CU4, use la opción PERSIST_SAMPLE_PERCENT de CREATE STATISTICS o UPDATE STATISTICS para establecer y conservar un porcentaje de muestreo específico para las actualizaciones estadísticas posteriores que no especifican explícitamente un porcentaje de muestreo.

Administración automática de índice y estadísticas

Utilice soluciones inteligentes como Desfragmentación de índices adaptativa para administrar automáticamente la desfragmentación de índices y las actualizaciones de estadísticas para una o varias bases de datos. Este procedimiento elige automáticamente si recompilar o reorganizar un índice según su nivel de fragmentación, entre otros parámetros, y actualiza las estadísticas con un umbral lineal.

Determinar qué estadísticas usó el optimizador de consultas

Puede encontrar los objetos de estadísticas que usa el optimizador de consultas cuando compila una consulta inspeccionando un plan de ejecución estimado o real. Al inspeccionar un plan de ejecución, el OptimizerStatsUage elemento contiene elementos que contienen StatisticsInfo información sobre los objetos de estadísticas que el optimizador de consultas cargó durante la compilación. Los StatisticsInfo elementos contienen el nombre del objeto de estadísticas, la base de datos, el esquema y la tabla a los que pertenece, el recuento de modificaciones en tiempo de compilación, el porcentaje de muestreo y cuándo se actualizó por última vez.

Use cualquiera de las técnicas siguientes para inspeccionar el plan de ejecución:

• En SQL Server Management Studio, seleccione Incluir plan de ejecución real (Ctrl+M) antes de ejecutar la consulta. En la pestaña Plan de ejecución que aparece con los resultados, puede:
  • Haga clic con el botón derecho en el plan gráfico y seleccione Mostrar XML del plan de ejecución. Busque el elemento OptimizerStatsUsage y cada elemento secundario StatisticsInfo.
  • Seleccione el operador final (más izquierdo). (En el caso de una SELECT consulta, este operador es un SELECT nodo). En la ventana Propiedades , expanda el nodo OptimizerStatsUsage y vea información sobre los objetos de estadísticas usados en la consulta.
• Ejecute SETSET STATISTICS XML ON antes de la consulta. Seleccione el hipervínculo que aparece con los resultados para ver el XML del plan de ejecución.
• Consulta sys.dm_exec_query_plan o sys.dm_exec_query_statistics_xml para consultas recientes.
• Lea un plan capturado previamente de Almacén de consultas mediante sys.query_store_plan.

Cada StatisticsInfo elemento tiene un aspecto similar al fragmento XML siguiente de una consulta en la AdventureWorks2022 base de datos de ejemplo:

<StatisticsInfo 
    Database="[AdventureWorks2022]" 
    Schema="[Sales]" 
    Table="[SalesOrderDetail]" 
    Statistics="[IX_SalesOrderDetail_ProductID]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2025-09-07T15:32:16.89" />

Attribute	Meaning
Database, , Schema, Table	Objeto al que pertenece la estadística.
Statistics	Nombre del objeto de estadísticas de la base de datos. Use este nombre con DBCC SHOW_STATISTICS o sys.stats para inspeccionar el histograma y el vector de densidad.
ModificationCount	Número de modificaciones de datos desde la última actualización de la estadística, en el momento en que se compiló el plan. Un valor grande con respecto al tamaño de tabla indica que la estadística estaba obsoleta durante la compilación.
SamplingPercent	Porcentaje de filas muestreadas para crear la estadística. Los valores inferiores pueden producir histogramas menos precisos para los datos sesgados.
LastUpdate	Marca de tiempo de la última actualización de estadísticas. Si la opción AUTO_UPDATE_STATISTICS está habilitada en la base de datos, la base de datos actualiza automáticamente las estadísticas cuando sea necesario.

Note

StatisticsInfo refleja las estadísticas disponibles y consideradas durante la compilación del plan. Si falta una StatisticsInfo entrada para una columna en la que se filtra la consulta, el optimizador de consultas no identificó las estadísticas pertinentes, que es una fuente potencial de rendimiento deficiente.

Para comprobar los recuentos de actualización y modificación actuales de un objeto estadístico, use sys.dm_db_stats_properties. Por ejemplo, la consulta siguiente proporciona las métricas actuales de un objeto estadístico denominado IX_SalesOrderDetail_ProductID en la tabla Sales.SalesOrderDetail:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND s.name = N'IX_SalesOrderDetail_ProductID';

Para determinar si las opciones de creación y actualización automáticas de la base de datos actual están habilitadas, use:

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE [name] = DB_NAME();

Consultas que usan eficazmente las estadísticas

Algunas implementaciones de consulta, como las variables locales y las expresiones complejas en el predicado de consulta, pueden conducir a planes de consulta que no son óptimos. Para evitar estos problemas, siga las instrucciones de diseño de consultas para usar estadísticas de forma eficaz. Para obtener más información sobre los predicados de consulta, consulte Condición de búsqueda.

Puede mejorar los planes de consulta aplicando instrucciones de diseño de consulta que utilicen las estadísticas con eficacia para mejorar las estimaciones de cardinalidad en las expresiones, variables y funciones utilizadas en los predicados de consulta. Cuando el optimizador de consultas no conoce el valor de una expresión, variable o función, no sabe qué valor buscar en el histograma y, por tanto, no puede recuperar la mejor estimación de cardinalidad del histograma. En cambio, el Optimizador de consultas basa la estimación de cardinalidad en el número medio de filas por valor distinto para todas las filas buscadas en el histograma. Esta situación conduce a estimaciones de cardinalidad poco óptimas y puede afectar al rendimiento de las consultas. Para obtener más información sobre los histogramas, consulte la sección histograma de este artículo o sys.dm_db_stats_histogram.

Las instrucciones siguientes describen cómo escribir las consultas para mejorar los planes de consulta mediante la mejora de las estimaciones de cardinalidad.

Mejorar de las estimaciones de cardinalidad en las expresiones

Para mejorar las estimaciones de cardinalidad en las expresiones, siga estas instrucciones:

• Siempre que sea posible, simplifique las expresiones que contienen constantes. El optimizador de consultas no evalúa todas las funciones y expresiones que contienen constantes antes de determinar las estimaciones de cardinalidad. Por ejemplo, simplifique la expresión ABS(-100) en 100.
• Si la expresión utiliza varias variables, considere la creación de una columna calculada para la expresión y, a continuación, cree estadísticas o un índice en la columna calculada. Por ejemplo, el predicado de consulta WHERE PRICE + Tax > 100 podría tener una mejor estimación de cardinalidad si crea una columna calculada para la expresión Price + Tax.

Mejora de las estimaciones de cardinalidad en las variables y funciones

Para mejorar las estimaciones de cardinalidad para variables y funciones, siga estas instrucciones:

• Si el predicado de consulta utiliza una variable local, considere volver a escribir la consulta usando un parámetro en lugar de una variable local. El optimizador de consultas no conoce el valor de una variable local cuando crea el plan de ejecución de consultas. Cuando una consulta usa un parámetro, el optimizador de consultas usa la estimación de cardinalidad para el primer valor de parámetro real que recibe el procedimiento almacenado.

• Considere la posibilidad de usar una tabla estándar o una tabla temporal para contener los resultados de las funciones con valores de tabla de varios estados. El optimizador de consultas no crea estadísticas para funciones con valores de tabla de varios estados. Con este enfoque, el optimizador de consultas puede crear estadísticas en las columnas de tabla y usarlas para crear un mejor plan de consulta.

• Considere el uso de una tabla estándar o una tabla temporal como un reemplazo para las variables de tabla. El optimizador de consultas no crea estadísticas para las variables de tabla. Con este enfoque, el optimizador de consultas puede crear estadísticas en las columnas de tabla y usarlas para crear un mejor plan de consulta. Existen inconvenientes para determinar si se debe usar una tabla temporal o una variable de tabla. Las variables de tabla usadas en los procedimientos almacenados provocan menos recompilaciones del procedimiento almacenado que las tablas temporales. Dependiendo de la aplicación, el uso de una tabla temporal en lugar de una variable de tabla no mejora el rendimiento.

• Si un procedimiento almacenado contiene una consulta que utiliza un parámetro pasado, evite cambiar el valor del parámetro dentro del procedimiento almacenado antes de utilizarlo en la consulta. Las estimaciones de cardinalidad para la consulta se basan en el valor de parámetro pasado y no en el valor actualizado. Para evitar cambiar el valor del parámetro, puede reescribir la consulta para utilizar los dos procedimientos almacenados.

  Por ejemplo, el procedimiento almacenado siguiente Sales.GetRecentSales cambia el valor del parámetro @date cuando @date es NULL.

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

  Si la primera llamada al procedimiento almacenado Sales.GetRecentSales pasa un NULL para el parámetro @date, el optimizador de consultas compila el procedimiento almacenado con la estimación de cardinalidad para @date = NULL aunque el predicado de consulta no se invoque con @date = NULL. Esta estimación de cardinalidad puede ser significativamente diferente del número de filas del resultado de la consulta real. Como resultado, el Optimizador de consultas podría elegir un plan de consulta poco óptimo. Para evitar este problema, puede volver a escribir el procedimiento almacenado en dos procedimientos de la siguiente manera:

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNullRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      EXECUTE Sales.GetNonNullRecentSales @date;
  END
  GO
  
  IF OBJECT_ID('Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNonNullRecentSales
  @date DATETIME
  AS
  BEGIN
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

Mejora de las estimaciones de cardinalidad con sugerencias de consulta

Para mejorar las estimaciones de cardinalidad de las variables locales, use las sugerencias de consulta OPTIMIZE FOR <value> o OPTIMIZE FOR UNKNOWN con RECOMPILE. Para más información, consulte Sugerencias de consultas.

En algunas aplicaciones, volver a compilar la consulta cada vez que la ejecuta podría tardar demasiado tiempo. La sugerencia de consulta OPTIMIZE FOR puede servir de ayuda incluso si no usa la opción RECOMPILE. Por ejemplo, podría agregar una opción OPTIMIZE FOR al procedimiento almacenado Sales.GetRecentSales para especificar una fecha concreta. En el ejemplo siguiente, se agrega la opción OPTIMIZE FOR al procedimiento Sales.GetRecentSales.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Mejora de las estimaciones de cardinalidad con guías de plan

En algunas aplicaciones, es posible que las directrices de diseño de consultas no se apliquen porque no se puede cambiar la consulta o la RECOMPILE sugerencia de consulta podría provocar demasiadas recompilación. Use guías de plan para especificar otras sugerencias, como USE PLAN, para controlar el comportamiento de la consulta al investigar los cambios de aplicación con el proveedor de la aplicación. Para obtener más información acerca de las guías de plan, vea Plan Guides.

En Azure SQL Database, considere Sugerencias del almacén de consultas para forzar los planes, en lugar de las guías de plan. Para obtener más información, vea Sugerencias del Almacén de consultas.

Contenido relacionado

• Estadísticas para las tablas con optimización para memoria
• CREATE STATISTICS (Transact-SQL)
• UPDATE UPDATE STATISTICS (Transact-SQL)
• sys.sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• ALTER DATABASE SET Opciones (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• Creación de índices filtrados
• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)
• Desfragmentación de índice adaptable