Artículo
03/30/2012

Utilizar las estadísticas para mejorar el rendimiento de las consultas

El optimizador de consultas emplea 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 genera ya las estadísticas necesarias para un plan de consulta de alta calidad; en algunos casos, para obtener los mejores resultados, necesita crear estadísticas adicionales o modificar el diseño de la consulta.

En este tema se discuten los conceptos e instrucciones siguientes para utilizar eficazmente las estadísticas de optimización de consulta:

¿Qué son las estadísticas de optimización de consulta?
Utilizar las opciones de estadísticas de toda la base de datos
Determinar cuándo crear las estadísticas
Determinar cuándo actualizar las estadísticas
Diseñar consultas que utilicen eficazmente las estadísticas

Para obtener más información acerca del plan de consulta y de cómo se relaciona con el rendimiento de la consulta, vea Analizar una consulta.

¿Qué son las estadísticas de optimización de consulta?

Las estadísticas para la optimización de consulta son objetos que contienen información estadística acerca de 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, o número de filas, en el resultado de la consulta. Estas estimaciones de cardinalidad habilitan al optimizador de consultas para crear un plan de consulta de alta calidad. Por ejemplo, el optimizador de consultas podría utilizar las estimaciones de cardinalidad para elegir el operador Index Seek en lugar del operador Index Scan, con un uso más intensivo de los recursos, mejorando con ello 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. Para obtener más información acerca de los objetos de estadísticas, vea DBCC SHOW_STATISTICS (Transact-SQL).

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 acerca del predicado de filtro, vea CREATE STATISTICS (Transact-SQL). Para obtener más información acerca de cuándo crear las estadísticas filtradas, vea la sección Determinar cuándo crear las estadísticas en este tema. Para consultar un caso práctico, vea la entrada de blog sobre el uso de estadísticas filtradas con tablas con particiones (puede estar en inglés), en el sitio web SQLCAT.

Utilizar las opciones de estadísticas de toda la base de datos

Es importante entender lo que hacen las siguientes opciones de estadísticas de toda la base de datos y comprobar que se han configurado apropiadamente:

AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC

Utilizar la opción AUTO_CREATE_STATISTICS

Cuando está activada la opción automática de creación de estadísticas, AUTO_CREATE_STATISTICS, el optimizador de consultas crea las estadísticas en columnas individuales en el predicado de consulta, según sea necesario, para mejorar las estimaciones de cardinalidad para el 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.

Puede usar la consulta siguiente para determinar si el optimizador de consultas ha creado estadísticas para una columna de predicado de consulta. Consulta las vistas de catálogo sys.stats y sys.stats_columns para devolver el nombre del objeto de base de datos, el nombre de columna y el nombre de las estadísticas de todas las columnas que tengan estadísticas de columna única. Cuando el optimizador de consultas crea las estadísticas en columnas únicas como resultado de utilizar la opción AUTO_CREATE_STATISTICS, el nombre de las estadísticas comienza con _WA.

USE AdventureWorks;
GO
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 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;

La opción AUTO_CREATE_STATISTICS no determina si las estadísticas se crean para los índices. Esta opción tampoco genera estadísticas filtradas. Se aplica estrictamente a estadísticas de columna única para la tabla completa.

Utilizar la opción AUTO_UPDATE_STATISTICS

Cuando está activada la opción automática de actualización de estadísticas, AUTO_UPDATE_STATISTICS, el optimizador de consultas determina cuándo las estadísticas pueden estar desfasadas y las actualiza cuando son usadas por una consulta. 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 indizada. El optimizador de consultas determina cuándo han podido quedar obsoletas las estadísticas contando el número de modificaciones de datos desde la actualización más reciente de las estadísticas, comparando el número de modificaciones con respecto a un umbral. El umbral se basa en el número de filas de la tabla o la vista indizada.

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 utiliza las columnas, tablas y vistas indizadas en el predicado de consulta, para determinar qué estadísticas podrían estar obsoletas. Antes de ejecutar un plan de consulta almacenado en la memoria caché, Motor de base de datos comprueba que el plan de consulta hace referencia a las estadísticas actualizadas.

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

Activar las opciones AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS

La opción automática de creación de estadísticas, AUTO_CREATE_STATISTICS, y la de actualización de estadísticas, AUTO_UPDATE_STATISTICS, están activadas de forma predeterminada y recomendamos el uso de estas opciones predeterminadas en la mayoría de las bases de datos de usuario. Puede utilizar la instrucción SELECT siguiente para ver los valores actuales de estas opciones para todas las bases de datos de usuario:

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

En el ejemplo siguiente se activa AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS para la base de datos AdventureWorks2008R2:

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

Para obtener más información acerca de cómo establecer estas opciones de estadísticas, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Deshabilitar y volver a habilitar AUTO_UPDATE_STATISTICS para algunas estadísticas

Cuando AUTO_UPDATE_STATISTICS está activada, puede invalidar el comportamiento de actualización de las estadísticas de toda la base de datos y desactivar las actualizaciones automáticas de las estadísticas en una tabla, un índice o una columna, según lo requiera su aplicación. Cuando AUTO_UPDATE_STATISTICS esté activada, puede deshabilitar y volver a habilitar las actualizaciones automáticas de las estadísticas en una tabla, índice o columna de los modos siguientes:

Use el procedimiento almacenado del sistema sp_autostats. Puede deshabilitar o volver a habilitar las actualizaciones de las estadísticas en una tabla o índice.
Especifique la opción NORECOMPUTE con la instrucción UPDATE STATISTICS. Para volver a habilitar las actualizaciones de las estadísticas, vuelva a ejecutar UPDATE STATISTICS sin la opción NORECOMPUTE.
Especifique la opción NORECOMPUTE con la instrucción CREATE STATISTICS. Para volver a habilitar las actualizaciones de las estadísticas, quite las estadísticas con DROP STATISTICS y, a continuación, ejecute CREATE STATISTICS sin la opción NORECOMPUTE.
Especifique la opción STATISTICS_NORECOMPUTE con la instrucción CREATE INDEX. Para volver a habilitar las actualizaciones de las estadísticas, puede ejecutar ALTER INDEX con STATISTICS_NORECOMPUTE = OFF.

Cuando está desactivada la opción AUTO_UPDATE_STATISTICS, no puede activar las actualizaciones automáticas para una tabla, un índice o una columna. Al volver a habilitar las actualizaciones automáticas de las estadísticas, se restaura el comportamiento especificado por la opción AUTO_UPDATE_STATISTICS. Si la opción AUTO_UPDATE_STATISTICS está desactivada, no se producirán actualizaciones de las estadísticas.

Cuándo utilizar actualizaciones de las estadísticas sincrónicas o asincrónicas

Las actualizaciones de las estadísticas pueden ser sincrónicas (el valor predeterminado) o asincrónicas. Con actualizaciones sincrónicas de las estadísticas, las consultas siempre se compilan y ejecutan con estadísticas actualizadas; cuando las estadísticas están obsoletas, el optimizador de consultas espera a que las estadísticas estén actualizadas antes de compilar y ejecutar la consulta. Con actualizaciones asincrónicas de las estadísticas, las consultas se compilan con las estadísticas existentes incluso aunque estén anticuadas; el optimizador de consultas podría elegir un plan de consulta poco óptimo si las estadísticas están obsoletas cuando se compila la consulta. Las consultas que se compilan cuando las actualizaciones asincrónicas han finalizado se beneficiarán del uso de estadísticas actualizadas.

La opción de actualización asincrónica de estadísticas de toda la base de datos, AUTO_UPDATE_STATISTICS_ASYNC, determina si el optimizador de consultas utiliza actualizaciones sincrónicas o asincrónicas de las estadísticas. De forma predeterminada, la opción de actualización asincrónica de las estadísticas está desactivada y el optimizador de consultas actualiza las estadísticas sincrónicamente. La opción AUTO_UPDATE_STATISTICS_ASYNC se aplica a los objetos de estadísticas creados para índices y columnas únicas de los predicados de consulta, así como a las estadísticas creadas con la instrucción CREATE STATISTICS.

Puede utilizar el comando siguiente para ver la opción de actualización asincrónica automática para todas las bases de datos:

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

Considere el uso de estadísticas sincrónicas para el escenario siguiente:

Realiza 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 las estadísticas después de finalizar la operación, el uso de estadísticas sincrónicas garantizará que las estadísticas estén actualizadas antes de ejecutar las consultas en los datos cambiados.

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. Para obtener más información acerca de cómo encontrar consultas similares, vea Buscar y optimizar consultas similares utilizando hash del plan de consulta y de consulta.
Su aplicación ha experimentado tiempos de espera de solicitud de cliente causados por una o varias consultas que aguardaban la actualización de estadísticas. En algunos casos, la espera por las estadísticas sincrónicas podría causar errores en aplicaciones con tiempos de espera agresivos.

Determinar cuándo crear las estadísticas

El optimizador de consultas crea ya las estadísticas de las maneras siguientes:

El optimizador de consultas crea las estadísticas para índices en tablas o vistas cuando se crea el índice. 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 acerca de los índices filtrados, vea Directrices generales para diseñar índices filtrados y CREATE INDEX (Transact-SQL).
El optimizador de consultas crea las estadísticas para las columnas únicas de predicados de consulta cuando está activada AUTO_CREATE_STATISTICS.

Para la mayoría de las consultas, estos dos métodos de creación de estadísticas aseguran un plan de consulta de alta calidad; en unos casos, puede mejorar los planes de consulta creando estadísticas adicionales con 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 las estadísticas con la instrucción CREATE STATISTICS, recomendamos mantener la opción AUTO_CREATE_STATISTICS para que el optimizador de consultas continúe creando rutinariamente estadísticas de columna única para las columnas de predicado de consulta. Para obtener más información acerca de los predicados de consulta, vea Condiciones de búsqueda (Transact-SQL).

Considere la creación de estadísticas con la instrucción CREATE STATISTICS cuando se aplique cualquiera de los escenarios 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 ya no están en el mismo índice.
La consulta realiza la selección entre un subconjunto de datos.
La consulta ha perdido estadísticas.

El Asistente para la optimización de motor de base de datos sugiere crear las estadísticas

El Asistente para la optimización de Motor de base de datos es una herramienta que analiza los efectos que causan en el rendimiento las cargas de trabajo en una o varias bases de datos. Proporciona recomendaciones para mejorar el rendimiento (como sugerir los índices que se van a crear) y podría sugerir utilizar CREATE STATISTICS para crear las estadísticas de optimización de consulta. Debería seguir esta recomendación. Para obtener más información acerca del Asistente para la optimización de Motor de base de datos, vea Optimizar el diseño físico de las bases de datos.

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 sobre varias columnas contienen estadísticas de correlación entre columnas, llamadas densidades, que no están disponibles en las estadísticas de columna única. 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 no están ya en el mismo índice, puede crear las estadísticas de varias columnas creando un índice en las columnas o utilizando la instrucción CREATE STATISTICS. 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 recursos del sistema creando el objeto de estadísticas sin crear el índice.

Al crear las estadísticas de varias columnas, el orden de las columnas en la definición del objeto de estadísticas afecta a la efectividad de las densidades para realizar las 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 acerca de las densidades, vea DBCC SHOW_STATISTICS (Transact-SQL).

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, lo siguiente crea un objeto de estadísticas de varias columnas en las columnas LastName, MiddleName y FirstName.

USE AdventureWorks2008R2;
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 utiliza LastName y FirstName sin utilizar MiddleName, la densidad no está disponible para las estimaciones de cardinalidad.

La consulta realiza la selección entre 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 usando la instrucción CREATE STATISTICS con la cláusula WHERE para definir la expresión del predicado de filtro.

Por ejemplo, usando AdventureWorks2008R2, cada producto de la tabla Production.Product pertenece a una de las cuatro categorías de la tabla Production.ProductCategory: Bikes, Components, Clothing y 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).

Usando las bicicletas (Bikes) como ejemplo, las estadísticas filtradas en todos los pesos de bicicleta 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 inexistentes en la columna 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 instrucción siguiente crea las estadísticas filtradas BikeWeights en 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 nombre de categoría Bikes porque está almacenado en la tabla Production.ProductCategory, y todas las columnas de la expresión del filtro deben estar en la misma tabla.

USE AdventureWorks2008R2;
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 para la consulta siguiente que selecciona todas las bicicletas cuyo peso es superior a 25.

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    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

La consulta ha perdido estadísticas

Si un error u otro evento evita que el optimizador de consultas cree las estadísticas, el optimizador de consultas crea 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. Para obtener más información, vea Mostrar planes de ejecución gráficos (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, vea Errores y advertencias (categoría de eventos del 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, el optimizador de consulta no puede guardar las estadísticas.
Cree las estadísticas perdidas usando la instrucción CREATE STATISTICS.

Determinar cuándo actualizar las estadísticas

El optimizador de consultas determina cuándo las estadísticas podrían 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 consiguiente, mejorar el rendimiento de la consulta, actualizando las estadísticas con más frecuencia que la que se produce cuando está activada AUTO_UPDATE_STATISTICS. Puede actualizar las estadísticas con la instrucción UPDATE STATISTICS o con el procedimiento almacenado sp_updatestats.

La actualización de las estadísticas asegura que las consultas se compilan con estadísticas actualizadas. Sin embargo, la actualización de las estadísticas hace que las consultas se vuelvan a compilar. Recomendamos no actualizar las estadísticas con demasiada frecuencia, porque hay que elegir el punto válido entre la mejora de los planes de consulta y el tiempo empleado en volver a compilar las consultas. Los inconvenientes específicos dependen de su aplicación.

Al actualizar las estadísticas con UPDATE STATISTICS o con sp_updatestats, se recomienda mantener activada AUTO_UPDATE_STATISTICS, para que el optimizador de consultas continúe actualizando rutinariamente las estadísticas. Para obtener más información acerca de cómo actualizar las estadísticas en una columna, un índice, una tabla o una vista indizada, vea UPDATE STATISTICS (Transact-SQL). Para obtener información sobre cómo actualizar estadísticas para todas las tablas internas y definidas por el usuario de la base de datos, vea el procedimiento almacenado sp_updatestats (Transact-SQL). Por ejemplo, el comando siguiente llama a sp_updatestats para actualizar todas las estadísticas de la base de datos.

EXEC sp_updatestats

Para determinar cuándo se actualizaron las estadísticas por última vez, use la función 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.

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. Para obtener más información acerca de cómo solucionar problemas de consultas de ejecución lenta, vea Lista de comprobación para analizar consultas de ejecución lenta.

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 las columnas de marca de tiempo en tiempo real, podrían requerir actualizaciones de las 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. Esto puede producir estimaciones de cardinalidad inexactas y un rendimiento lento de las consultas.

Por ejemplo, una consulta que selecciona entre las fechas de pedidos de venta más recientes tendrá estimaciones de cardinalidad inexactas si las estadísticas no se han actualizado para incluir las estimaciones de cardinalidad correspondientes a las fechas de pedidos de venta 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. Esto puede evitar futuros retrasos en el procesamiento de la consulta, mientras las consultas esperan las actualizaciones automáticas de las estadísticas.

No actualice las estadísticas después de realizar operaciones como volver a generar, desfragmentar o reorganizar un índice. Estas operaciones no cambian la distribución de los datos y no afectan a las estadísticas. Sin embargo, tenga en cuenta que el optimizador de consultas actualiza las estadísticas cuando vuelve a generar un índice en una tabla o una vista mediante ALTER INDEX REBUILD o DBCC DBREINDEX. Esto ocurre como subproducto de la implementación interna de estas operaciones y no porque sea necesario actualizar las estadísticas. El optimizador de consultas no actualiza las estadísticas después de realizar operaciones de desfragmentación o reorganización, ya que estas operaciones no quitan y vuelven a crear el índice.

Diseñar consultas que usen 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. Las siguientes instrucciones de diseño de consulta para el uso eficaz de las estadísticas pueden evitarlo. Para obtener más información acerca de los predicados de consulta, vea Condiciones de búsqueda (Transact-SQL).

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 conoce qué valor ha de buscar en el histograma y, por consiguiente, no puede recuperar del histograma la mejor estimación de cardinalidad. 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. El resultado son estimaciones de cardinalidad poco óptimas, además de dañar el rendimiento de la consulta.

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

Mejorar 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 utilizando 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) to 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.

Mejorar las estimaciones de cardinalidad en las variables y funciones

Para mejorar las estimaciones de cardinalidad en las 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. No se conoce el valor de una variable local cuando el optimizador de consultas crea el plan de ejecución de la consulta. Cuando una consulta utiliza un parámetro, el optimizador de consultas utiliza la estimación de cardinalidad para el primer valor de parámetro real que se pasa al procedimiento almacenado.
Considere el uso de una tabla estándar o una tabla temporal para retener los resultados de las funciones con valores de tabla de múltiples instrucciones. El optimizador de consultas no crea estadísticas para las funciones con valores de tabla de múltiples instrucciones. Con este enfoque, el optimizador de consultas puede crear estadísticas sobre las columnas de tabla y utilizarlas para crear un plan de consulta mejor. Para obtener más información acerca de las funciones con valores de tabla de múltiples instrucciones, vea Tipos de funciones.
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 sobre las columnas de tabla y utilizarlas para crear un plan de consulta mejor. Hay que determinar si se utilizar una tabla temporal o una variable de tabla; las variables de tabla utilizadas en los procedimientos almacenados causan 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 is NULL.
```
USE AdventureWorks2008R2;
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 h, Sales.SalesOrderDetail 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 compilará el procedimiento almacenado con la estimación de cardinalidad para @date = NULL aunque no se llame al predicado de consulta con @date = NULL. Esta estimación de cardinalidad podría 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 ayudar a evitar esto, puede rescribir el procedimiento almacenado en dos procedimientos del modo siguiente:
```
USE AdventureWorks2008R2;
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))
    EXEC 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 h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END;
GO
```

Mejorar las estimaciones de cardinalidad con sugerencias de consulta

Para mejorar las estimaciones de cardinalidad para las variables locales, puede utilizar las sugerencias de consulta OPTIMIZE FOR u OPTIMIZE FOR UNKNOWN junto con RECOMPILE. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

En algunas aplicaciones, volver a compilar la consulta cada vez que la ejecuta podría tardar demasiado tiempo. La sugerencia de consulta OPTIMIZER FOR puede servir de ayuda incluso si no usa la opción RECOMPILE. Por ejemplo, podría agregar una opción OPTIMIZER 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 AdventureWorks2008R2;
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 h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

Mejorar las estimaciones de cardinalidad con guías de plan

En algunas aplicaciones, podrían no aplicarse las instrucciones de diseño de consulta, porque no puede cambiar la consulta o porque usar la sugerencia de consulta RECOMPILE podría ser la causa de demasiadas recompilaciones. Puede utilizar las guías de plan para especificar otras sugerencias, como USE PLAN, para controlar el comportamiento de la consulta mientras investiga los cambios de la aplicación con el proveedor de la aplicación. Para obtener más información acerca de las guías de plan, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan.

Vea también