Technological recipes that I've held to prepare some solutions DBA environment. (by Felipe Donoso Bastias)

2 Nov 2007

Sql Server: Estadisticas y la forma de administrar estas



Como muchos de ustedes deben saber, la actualización de estadisticas en una base de datos cualquiera permite al optimizador de planes realizar y evaluar los mejores planes de ejecución de acuerdo al costo que esta query representa.

Por defecto la creación y actualización de estadisticas en SQL Server es automática, y para entender en qué momento y con qué frecuencias se realiza esto recomiendo leer lo siguiente:

Cómo funcionan las estadísticas automáticasCó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ónicasActualizaciones 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.

Fuente: http://technet.microsoft.com/es-es/library/ms190397.aspx

Principales funciones y procedimientos que interfieren en el proceso de actualización de estadisticas:

-- Para visualizar datos de estadisticas en un índice de cierta tabla
USE northwind
DBCC SHOW_STATISTICS (Customers,City )


--A. Mostrar el estado actual de todos los índices de una tabla
--Este ejemplo muestra el estado actual de las estadísticas de todos los índices de la tabla authors.

USE Northwind
exec sp_autostats @tblname = 'Customers'

--B. Habilitar las estadísticas automáticas de todos los índices de una tabla
--Este ejemplo habilita la configuración de estadísticas automáticas de todos los índices de la tabla authors.

USE pubs
EXEC sp_autostats authors, 'ON'

--C. Deshabilitar las estadísticas automáticas de un índice especificado
--Este ejemplo deshabilita la configuración de estadísticas automáticas del índice au_id de la tabla authors.

USE pubs
EXEC sp_autostats authors, 'OFF', au_id

-- Para actualizar manualmente las estadisticas
UPDATE STATISTICS Northwind..Customers WITH FULLSCAN

y Cómo recomendación las estadisticas se deben actualizar en lo posible regularmente. Las tablas a actualizar dependen de la aplicación con las que interactuan. Generalmente se deben actualizar las tablas con mayor cantidad de registros y la más usadas en las bases de datos productivas. Lo más aconsejable es dejar corriendo jobs nocturnos los fines de semana que realizen estas labores de actualización.

Saludos.
Felipitux

Saludos.
Share:

Copyright © DBA TIPS | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com | Free Blogger Templates