Estadísticas de índice

Actualizado: 12 de diciembre de 2006

SQL Server 2005 permite crear información estadística acerca de la distribución de valores en una columna. El optimizador de consultas utiliza esta información estadística para determinar el plan de consulta óptimo realizando una estimación del costo de usar un índice para evaluar la consulta.

Cuando se crean estadísticas, el Database Engine (Motor de base de datos) ordena los valores de las columnas en las que se generan las estadísticas y crea un histograma basado en hasta 200 de estos valores, separados por intervalos. El histograma especifica el número exacto de filas que debe coincidir con el valor de cada intervalo, el número de filas que entran en un intervalo y un cálculo de densidad de los valores o de la incidencia de los valores duplicados, dentro de un intervalo.

SQL Server 2005 incorpora información adicional recopilada por estadísticas creadas en las columnas char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text y ntext. Esta información, denominada resumen de cadena ayuda al optimizador de consultas a realizar una estimación de la selectividad de los predicados de consultas de los patrones de cadena. Los resúmenes de cadenas permiten unas estimaciones más precisas de los tamaños de los conjuntos de resultados y planes de consultas frecuentemente mejores cuando las condiciones LIKE están presentes en una consulta. Esto incluye condiciones como WHERE ProductName LIKE '%Bike' y WHERE Name LIKE '[CS]heryl'.

[!NOTA] La información de resumen de cadena no se mantiene si el resumen de la muestra de una columna es mayor del que Database Engine (Motor de base de datos) puede mantener. Por ejemplo, un resumen de cadena no se mantiene en una estadística creada utilizando WITH FULLSCAN en una columna varchar(80) única con 80 caracteres en cada cadena, sin casi similitud entre cadena y en una tabla con 85.000 filas. Para determinar si un resumen de cadena está almacenado para un objeto de estadísticas específico, utilice DBCC SHOW_STATISTICS (Transact-SQL).

Cómo funcionan las estadísticas automáticas

Cuando crea un índice, el optimizador de consultas almacena automáticamente información estadística acerca de las columnas indizadas. Además, cuando la opción de base de datos AUTO_CREATE_STATISTICS es ON (valor predeterminado), Database Engine (Motor de base de datos) crea automáticamente estadísticas de las columnas sin índices que se utilizan en un predicado.

Cuando cambian los datos de una columna, las estadísticas de índice y de columna pueden quedar desfasadas y provocar que el optimizador de consultas tome decisiones poco adecuadas acerca de cómo procesar una consulta. Por ejemplo, si crea una tabla con una columna indizada y 1.000 filas de datos, todas con valores exclusivos en la columna indizada, el optimizador de consultas considera que la columna indizada puede ser una buena forma de recopilar los datos para una consulta. Si actualiza los datos de la columna de forma que haya muchos valores duplicados, la columna deja de ser una candidata ideal para usarla en consultas. No obstante, el optimizador de consultas sigue considerándola una buena candidata según las estadísticas desfasadas del índice, que se basan en los datos anteriores a la actualización.

[!NOTA] Si faltan estadísticas o están desfasadas, se indica mediante advertencias (el nombre de la tabla aparece en rojo) cuando el plan de ejecución de una consulta se representa gráficamente con SQL Server Management Studio. Para obtener más información, vea Mostrar planes de ejecución gráficos (SQL Server Management Studio). Además, si se supervisa la clase de evento Missing Column Statistics con el Analizador de SQL Server, se indica cuándo faltan estadísticas. Para obtener más información, vea Errores y advertencias (categoría de eventos del motor de base de datos).

Cuando la opción de la base de datos AUTO_UPDATE_STATISTICS está establecida en ON (valor predeterminado), el optimizador de consultas actualiza automáticamente esta información estadística periódicamente a medida que cambian los datos de las tablas. Se inicia una actualización de estadísticas cuando las estadísticas que se utilizan en un plan de ejecución de consultas no pasan una prueba de las estadísticas actuales. El muestreo de las páginas de datos es aleatorio y se realiza a partir de la tabla o del índice no agrupado más pequeño en las columnas necesarias para las estadísticas. Después de leer una página de datos del disco, todas las filas de la página de datos se utilizan para actualizar la información estadística. Casi siempre, la información estadística se actualiza cuando ha cambiado aproximadamente el 20 por ciento de las filas de datos. No obstante, el optimizador de consultas siempre se asegura de que se muestrea un número mínimo de filas. Las tablas de menos de 8 megabytes (MB) siempre se examinan completamente para recopilar estadísticas.

El muestreo de los datos, en lugar del análisis de todos los datos, minimiza el costo de la actualización estadística automática. En determinadas circunstancias, el muestreo estadístico no permitirá caracterizar con precisión los datos de una tabla. Puede controlar la cantidad de datos que se muestrean durante las actualizaciones manuales de las estadísticas, tabla por tabla, mediante las cláusulas SAMPLE y FULLSCAN de la instrucción UPDATE STATISTICS. La cláusula FULLSCAN especifica que se recorran todos los datos de la tabla para recopilar datos estadísticos, mientras que la cláusula SAMPLE se puede utilizar para especificar el porcentaje o el número de filas que se deben muestrear.

Actualizaciones de estadísticas asincrónicas

Una consulta que inicia una actualización de estadísticas no actualizadas debe esperar a que se actualicen esas estadísticas antes de compilar y devolver un conjunto de resultados. Esto puede causar tiempos de respuesta impredecibles y puede hacer que las aplicaciones con tiempos de espera cortos devuelvan un error.

En SQL Server 2005, la opción AUTO_UPDATE_STATISTICS_ASYNC de la base de datos proporciona una actualización asincrónica de las estadísticas. Cuando esta opción se establece en ON, las consultas no esperan a que se actualicen las estadísticas antes de la compilación. En su lugar, las estadísticas que no están actualizadas se ponen en una cola para que las actualice un subproceso de trabajo en un proceso en segundo plano. La consulta y cualquier otra consulta simultánea se compilan inmediatamente utilizando las estadísticas obsoletas existentes. Como no hay ningún retardo para las estadísticas actualizadas, los tiempos de respuesta de las consultas son predecibles; no obstante, las estadísticas obsoletas pueden hacer que el optimizador de consultas elija un plan de consulta menos eficiente. Las consultas que empiezan cuando las estadísticas actualizadas están preparadas utilizarán esas estadísticas. Esto puede provocar la recompilación de planes almacenados en la memoria caché que dependen de versiones de estadísticas más antiguas. La actualización asincrónica de estadísticas no puede tener lugar si cualquiera de las instrucciones de lenguaje de definición de datos (DDL), tales como instrucciones CREATE, ALTER y DROP, tiene lugar en la misma transacción de usuario explícita.

La opción AUTO_UPDATE_STATISTICS_ASYNC se establece en el nivel de la base de datos y determina el método de actualización para todas las estadísticas de la base de datos. Sólo es aplicable a la actualización de estadísticas y no se puede usar para crear estadísticas de forma asincrónica. El establecimiento de esta opción en ON no tiene ningún efecto a menos que AUTO_UPDATE_STATISTICS también se establezca en ON. De forma predeterminada, la opción AUTO_UPDATE_STATISTICS_ASYNC está en OFF. Para obtener más información acerca de la configuración de esta opción, vea ALTER DATABASE (Transact-SQL).

Antes de establecer una base de datos como SINGLE_USER, compruebe que la opción AUTO_UPDATE_STATISTICS_ASYNC está establecida en OFF. Cuando se establece en ON, el subproceso en segundo plano usado para actualizar las estadísticas toma una conexión con la base de datos y no se podrá tener acceso a la base de datos en modo de usuario único. Si la opción está establecida en ON, realice las tareas siguientes:

1. Establezca AUTO_UPDATE_STATISTICS_ASYNC en OFF.
2. Compruebe si hay trabajos de estadísticas asincrónicos consultando la vista de administración dinámica sys.dm_exec_background_job_queue.
3. Si hay trabajos activos, permita que se completen o termínelos manualmente con KILL STATS JOB.

Consideraciones acerca de prácticas recomendadas

Debería establecer AUTO_UPDATE_STATISTICS_ASYNC en ON cuando las siguientes características se aplican a su aplicación:

• Se ha excedido el tiempo de espera de una solicitud del cliente a causa de la espera de estadísticas actualizadas por parte de una o más consultas.
• Se requieren tiempos de respuesta a consultas predecibles, incluso a costa de ejecutar ocasionalmente consultas con planes de consultas menos eficientes a causa de estadísticas obsoletas.

Ver propiedades de estadísticas de actualización asincrónicas

Para ver el estado ON u OFF de la opción AUTO_UPDATE_STATISTICS_ASYNC, seleccione la columna is_auto_update_stats_async_on de la vista de catálogo sys.databases. Para obtener más información, vea sys.databases (Transact-SQL).

Para ver si las estadísticas están en cola para su actualización o en proceso de actualización, use la vista de administración dinámica sys.dm_exec_background_job_queue. Para las estadísticas, la columna object_id1 muestra la tabla o el Id. de vista y la columna object_id2 muestra el Id. de estadística. Utilice la vista de administración dinámica sys.dm_exec_background_job_queue_stats para ver estadísticas de agregado de todas las colas de trabajos, como el número de las solicitudes de trabajo que esperan su ejecución, el número de solicitudes erróneas y los tiempos de ejecución medios de las solicitudes enviadas anteriormente.

Deshabilitar estadísticas automáticas

Puede deshabilitar la generación automática de estadísticas para una columna o índice particulares mediante:

• El uso del procedimiento almacenado del sistema sp_autostats.
• El uso de la cláusula STATISTICS_NORECOMPUTE de la instrucción CREATE INDEX.
• El uso de la cláusula NORECOMPUTE de la instrucción UPDATE STATISTICS.
• El uso de la cláusula NORECOMPUTE de la instrucción CREATE STATISTICS.
• La desactivación de las opciones de base de datos AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS mediante la instrucción ALTER DATABASE. Para obtener más información, vea Configurar las opciones de la base de datos.

Si se indica a Database Engine (Motor de base de datos) que no mantenga las estadísticas automáticamente, es necesario actualizar manualmente la información estadística.

[!NOTA] La instrucción UPDATE STATISTICS vuelve a habilitar la actualización automática de las estadísticas, a menos que se especifique la cláusula NORECOMPUTE.

Crear y actualizar estadísticas manualmente

También es posible crear estadísticas para todas las columnas disponibles de todas las tablas de usuario de la base de datos actual con una sola instrucción mediante el procedimiento almacenado del sistema sp_createstats. Las estadísticas se pueden crear en una tabla o en columnas de vista específicas utilizando la instrucción CREATE STATISTICS y se pueden actualizar utilizando la instrucción UPDATE STATISTICS. El número máximo de estadísticas que se pueden crear en una tabla o vista independientemente de un índice es 2.000. Cualquier columna o combinación de columnas que se pueda elegir como clave de índice se puede elegir para estadísticas, con las siguientes excepciones:

• Se pueden especificar columnas de tipos de objetos grandes, excepto xml. Se pueden especificar los tipos varchar(max), nvarchar(max), varbinary(max), image, text y ntext.
• El tamaño máximo permitido de los valores de columnas combinadas puede sobrepasar el límite de 900 bytes que se impone en el valor de la clave de índice.

Si ya no desea guardar ni mantener las estadísticas generadas para una columna, puede eliminarlas.

La creación manual de estadísticas permite crear estadísticas que contienen varias densidades de columnas. Esos promedios son duplicados de la combinación de columnas. Por ejemplo, una consulta que contiene la cláusula WHERE a = 7 and b = 9.

La creación manual de estadísticas para ambas columnas (a, b) permite a Database Engine (Motor de base de datos) realizar una estimación más precisa para la consulta, porque las estadísticas también contienen el promedio de valores distintos para la combinación de las columnas a y b.

Para crear estadísticas de una columna

CREATE STATISTICS (Transact-SQL)

Para crear estadísticas de las columnas seleccionables en todas las tablas de usuario

sp_createstats (Transact-SQL)

Para actualizar estadísticas manualmente

UPDATE STATISTICS (Transact-SQL)

Para ver las estadísticas de una tabla

DBCC SHOW_STATISTICS (Transact-SQL)

Para eliminar las estadísticas de una columna

DROP STATISTICS (Transact-SQL)

Trabajar con estadísticas después de actualizar una base de datos a SQL Server 2005

Cuando actualiza una base de datos a SQL Server 2005 desde una versión anterior de SQL Server, todas las estadísticas de la versión anterior se consideran obsoletas. Por lo tanto, durante el primer uso, las estadísticas que se pueden actualizar con la opción AUTO_UPDATE_STATISTICS de la base de datos se actualizan utilizando la frecuencia de muestreo predeterminada. Esta característica tiene importantes ventajas y normalmente no hace falta emprender ninguna acción. No obstante, en casos excepcionales, esto puede dar como resultado estadísticas menos precisas si éstas se han calculado manualmente en una versión anterior de SQL Server utilizando FULLSCAN u otra frecuencia de muestreo elevada; o bien si la tabla que se está muestreando ocupa más de 8 MB y la distribución de datos no es aleatoria. De hecho, una reducción de la frecuencia de actualización siempre se puede producir en estadísticas FULLSCAN para tablas mayores de 8 MB cuando se produce AUTO_UPDATE_STATISTICS. La actualización inicial de estadísticas se puede producir antes de la actualización a la nueva versión de SQL Server.

La ventaja de actualizar al formato de SQL Server 2005 es que las estadísticas de SQL Server 2005 para una frecuencia de muestreo específica son de mayor calidad normalmente que las de SQL Server 2000 y versiones anteriores. Asimismo, SQL Server 2005 crea estadísticas de resumen de cadenas especiales para columnas de caracteres, como se ha descrito anteriormente. Para obtener más información acerca de las estadísticas en SQL Server 2005, vea el sitio Web de Microsoft.

Consideraciones acerca de prácticas recomendadas

En la mayoría de casos, no hay que tomar ninguna acción especial acerca de las estadísticas tras actualizar una base de datos. No obstante, si tiene una base de datos grande con requisitos de rendimiento exigentes, se recomienda que, tras actualizar, ejecute sp_updatestats (Transact-SQL) con la opción RESAMPLE. Esto conserva las frecuencias de muestreo anteriores y actualiza todas las estadísticas al formato más reciente. Tenga en cuenta que las estadísticas creadas durante la creación del índice se han creado utilizando la frecuencia de muestreo FULLSCAN. Éstas y otras estadísticas de FULLSCAN utilizan la frecuencia de muestreo predeterminada cuando se actualizan a causa de AUTO_UPDATE_STATISTICS. Si prefiere no actualizar todas las estadísticas ejecutando sp_updatestats, considere la posibilidad de utilizar UPDATE STATISTICS para actualizar estadísticas selectivamente en índices y otras estadísticas de FULLSCAN con la frecuencia de muestreo FULLSCAN después de la actualización de la base de datos.

Vea también

Conceptos

Optimizar índices

Otros recursos

CREATE INDEX (Transact-SQL)
Optimizar consultas
sp_autostats (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios