Estadísticas
de distribución en SQL Server 2000 (I)
En
este primer articulo sobre "Estadísticas de Distribución
en SQL Server 2000", hablaré sobre:
- Qué
son las estadísticas de distribución ?
- Dónde
se guardan las estadísticas de distribución y qué
información contienen ?
Qué son
las estadísticas de distribución ?
SQL
Server 2000 almacena estadísticas sobre la distribución
de los valores de la llave de un índice, las cuales son creadas
de forma automática cuando el índice es creado. Estas estadísticas
son usadas por SQL Server para estimar que tan eficiente seria usar un
índice para traer la data asociada a un valor de la llave o un
rango especificado en la sentencia "SELECT". Recordemos que
SQL Server tratara de usar el método que menor costo ofrezca en
cuanto a operaciones de lectura y/o acceso al disco. Estas estadísticas
también pueden ser creadas sobre columnas que no participan en
un índice, de forma manual usando "CREATE STATISTICS"
y también de forma automática (por SQL Server), si activamos
la opción "AUTO CREATE STATISTICS" en la base de datos,
usando la sentencia "UPDATE DATABASE" o el procedimiento almacenado
"SP_DBOPTION".
Dónde
se guardan las estadísticas y que información contienen
?
Las
estadísticas son almacenadas en la tabla de sistema "SYSINDEXES",
que es donde SQL Server almacena información sobre los índices.
Parte importante de estas, es el histograma (rangos de distribución),
el cual se guarda en la columna "STATBLOB", como una cadena
de bits. El histograma solo es calculado para la primera columna de la
llave del índice (o estadísticas manuales en múltiples
columnas). Podemos usar el comando "DBCC SHOW_STATISTICS" para
ver el contenido de estas. Una tabla puede tener hasta 249 índices
nonclustered y las estadísticas sobre columnas que no participan
en un índice están contempladas en este límite, o
sea que en la tabla "SYSINDEXES" pueden haber hasta 249 filas
por tabla, relacionadas con los índices nonclusteres y las estadísticas
sobre columnas que no participan en un índice. Esto lo podemos
probar si creamos una tabla con 249 columnas y creamos un índice
nonclustered por cada columna y tratamos de crear una estadística,
lo cual dara el siguiente mensaje de error:
"Cannot create more than 249
nonclustered indices or column statistics on one table."
La
distribución de los valores de la llave de un índice varia
cuando sobre la tabla se ejecutan las sentencias insert, update (sobre
la columna que participa en la llave) y delete, y por lo tanto se deben
tener actualizadas para que SQL Server pueda saber la distribución
actual en el momento que las necesite. Podemos actualizar las estadísticas
usando el comando "UPDATE STATISTICS", el procedimiento almacenado
SP_UPDATESTATS y también podemos dejar que SQL Server las actualice
de forma automática cuando sea necesario, si activamos la opción
"AUTO UPDATE STATISTICS" en la base de datos, usando la sentencia
"UPDATE DATABASE" o el procedimiento almacenado SP_DBOPTION.
Hablemos
ahora sobre la información que nos devuelve el comando "DBCC
SHOW_STATISTICS". La sintaxis de este comando es:
dbcc
show_statistics (nombre_tabla, nombre_indice / nombre_de_la_estadistica)
Veamos
un ejemplo:
Updated
Cuando fue la ultima vez que se actualizo estas estadísticas.
Puede verse también con la función "STATS_DATE".
Rows
Número de filas en la tabla.
Rows
Sampled
Número de filas usadas para calcular estas estadísticas.
Este valor es el 100% de las filas en la tabla cuando se crea el índice,
o un valor especificado en la sentencia "CREATE STATISTICS".
También se puede pasar este valor cuando usamos "SP_UPDATESTATS"
o "UPDATE STATISTICS".
Steps
Número de pasos de distribución dentro del histograma.
SQL Server guarda hasta 200 pasos en el histograma.
Density
Densidad de la primera columna de la llave del índice (no frecuente).
Para ser sincero, no he encontrado una literatura que explique exactamente
como calcular este valor que aparece en la primara fila del resultado
del comando dbcc.
Average
Key Length
Longitud promedio de la llave en bytes.
All
Density
Densidad de la combinación de columnas. Se calcula como 1 dividido
por la cantidad de distintos valores en la combinación. Para el
caso de una sola columna, el valor es:
Select
cast(1 as float) / count(distinct nombre_columna)
From nombre_tabla
Ejemplo:
Este valor es muy importante,
pues cuando el valor que aparece en la expresión que se usa para
filtrar en la cláusula "WHERE", no es conocido en tiempo
de compilación, entonces SQL Server aplica esta densidad contra
la cantidad de filas existentes para estimar el numero de filas que serán
devueltas.
En
el segundo articulo de esta serie, veremos ejemplos de esto.
Average
Length
Longitud promedio en bytes, para la combinación de columnas.
Columns
Nombre de las columnas del índice para las cuales se muestra los
valores de "All density" y "Average Length".
Cuando
la tabla asociada contiene un índice "CLUSTERED", las
columnas que forman parte de su llave aparecerán también
en esta información. Fíjense en las filas 2 y 3 de la sección
correspondiente a esta información en el ejemplo que aparece a
continuación. Esta es otra prueba de cómo SQL Server usa
la clave del índice "CLUSTERED" como referencia (BOOKMARK)
en los índices "NONCLUSTERED".
Ejemplo:
RANGE
HI KEY
Valor máximo en el paso del histograma.
RANGE
ROWS
Numero de filas en el paso, sin tomar en cuenta el valor máximo.
EQ
ROWS
Número de filas con valor igual al valor máximo (RANGE_HI_KEY).
DISTINCT
RANGE ROWS
Número de valores distintos dentro del paso, excluyendo la cota
máxima o máximo valor.
AVG
RANGE ROWS
Numero promedio de filas por cada valor en el paso, sin tomar en cuenta
el valor máximo (RANGE_ROWS / DISTINCT_RANGE_ROWS para DISTINCT_RANGE_ROWS
> 0).
Como
mencione antes, el histograma es creado solo para la primera fila de la
llave del índice o del grupo de columnas que participan en la estadística.
Analicemos
los tres pasos o rangos del histograma del índice [dbo].[orders].[pk_orders]
El
paso uno es fácil de entender. No hay paso anterior a este, así
que la cantidad de filas entre el paso anterior y este, es cero. Una sola
fila macha este valor en la llave.
El
paso dos esta mas interesante.
RANGE_HI_KEY:
11076
RANGE_ROWS: 827.0
EQ_ROWS: 1.0
DISTINCT_RANGE_ROWS: 827
AVG_RANGE_ROWS: 1.0
La cantidad de filas
entre el máximo valor del paso anterior (10248) y el máximo
valor de este paso (11076), son 827.
En la tabla existe
una sola fila cuyo valor en la columna [orderid] es igual al máximo
del paso.
De las 827 filas (RANGE_ROWS),
827 tienen distinto valor en la columna [orderid].
La cantidad de filas
promedio por cada valor en el rango es 1. Esto es lógico si sabemos
que los 827 valores son distintos o únicos.
Veamos un histograma
más complicado, como por ejemplo el de la tabla [orders],
índice [OrderDate].
Voy a copiar un pedazo del resultado.
Veamos de donde salen
los valores de las estadísticas para el paso 8.
SQL Server también
almacena información sobre la cantidad de filas afectadas por sentencias
de DML que afecten las llaves de los índices o estadísticas,
desde la última vez que se actualizaron las estadísticas.
Esto lo hace en la columna [rowmodctr] de la tabla [sysindexes], en los
indices 0 o 1 (heap o índice clustered) y en los índices
nonclustered. El valor en los índices 0 o 1, es incrementado cada
vez que una fila cambia, mientras el valor en los índices nonclustered,
es un valor relativo que será sumado al valor del índice
0 o 1, para saber realmente cuantas filas cambiaron en ese índice,
permitiendo llevar un control individual por índice. Estos valores
son usados por SQL Server para saber cuando volver a actualizar las estadísticas,
en caso de que se requiera. SQL Server usa una formula que depende de
cuantas filas tiene la tabla y cuando el optimizador lo necesite.
Conclusión
Las estadísticas,
en conjunto con los índices, son dos aspectos fundamentales para
mejorar los tiempos de respuesta de las sentencias DML en SQL Server.
Conocer como estos son usados por SQL Server, le dan al desarrollador
y/o administrador una herramienta mas para ajustar la base de datos.
En el próximo
artículo de esta serie hablare sobre:
- Cómo
usa SQL Server las estadísticas ?
- Riesgo que se
corre cuando las estadísticas no están actualizadas y
cuando la opción "AUTO CREATE STATISTICS" esta apagada.
Espero
que puedan aprender algo de este artículo y pido que no duden en
enviar sus comentarios y/o recomendaciones sobre este tema a la dirección
mesaalejandro@msn.com
Agradecimientos
Quiero agradecerle
a Salvador Ramos por su ayuda en la publicación de este artículo
en el sitio http://www.helpdna.net
Bibliografía
- Microsoft SQL Server 2000 UNLEASHED, Second Edition (Ray Ranking,
Paul Bertucci and Paul Jensen)
- SQL Server Query Performance Tuning Distilled (Sajal Dam)
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
( Lubor Kollar, Microsoft Corporation, November 2000)
|
