Estadisticas de SQL Server, parte 1

En la última consultoría que realicé, me encontré con que en el servidor de base de datos, la base de datos principal no estaba creando automáticamente las estadísticas a medida que las fuese necesitando. Esto claramente impacta el funcionamiento del servidor ya que SQL Server precisa de ellas para funciona al máximo.

En esta publicación se explicará que son las estadísticas de SQL Server y para que se utilizan. Veremos también por que es importante que existan, y que sucede al no crearlas y actualizarlas automáticamente. Dejaremos para otra oportunidad cuando podría ser bueno desactivarlas o tener un control manual de éstas.

Cada vez que creamos o actualizamos índices sobre alguna tabla de SQL Server, se nos presenta una opción que nos pregunta si queremos o no actualizar las estadísticas, y que viene por defecto instruyéndole al servidor que actualice las estadísticas. Por otra parte, en las propiedades de una base de datos se vuelve a presentar una opción similar, preguntando si deseamos crear las estadísticas automáticamente y si deseamos actualizarlas de la misma forma.

Comencemos entonces. ¿Que son las estadísticas de SQL Server y para que se utilizan?

Las estadísticas de SQL Server (de ahora en adelante, "estadísticas") son información sobre la distribución de los datos existentes en las columnas de las tablas de nuestra base de datos. A través de las estadísticas, el servidor conoce como es la información de una columna, como por ejemplo, si varía mucho, si todos los datos son iguales y los niveles de variación que hay. Estas le permiten al servidor "conocer" los datos de las columnas sin necesidad de tener que leerlas a menudo. Realmente el no conoce todos los datos, pero con la información que obtiene le basta para tomar buenas decisiones.

Esta información la utiliza cuando nosotros le pedimos datos de ciertas tablas que cumplan con ciertas condiciones (select.. from .. where). Las condiciones que especificamos en el where de una consulta son analizadas por el optimizador de consultas para determinar cual es la forma más rápida de obtener la información requerida. Para lograr esto, suponiendo que tenemos una consulta con varias condiciones en el where, el servidor examina las estadísticas asociadas a las columnas referenciadas en el where, como también los índices existentes en la tablas, y donde participan esas columnas. Para el caso de los índices, SQL Server maneja un conjunto de estadísticas de forma similar que para una columna de una tabla, independiente del tipo de índice (agrupado o no agrupado).

En el caso en que la consulta tiene una sola condición, no hay muchas soluciones posibles. Si hay un índice sobre la columna en que estamos buscando, en la mayoría de los casos lo usará (dependerá de las estadísticas y de otros factores) y en caso contrario, realizará un SCAN sobre la tabla o el índice agrupado (en caso de tener).

Veamos con un ejemplo sencillo como SQL Server maneja las estadísticas. Con nuestra base de datos configurada para crear automáticamente las estadísticas, generemos un tabla con la siguiente estructura:

CREATE TABLE [dbo].[TEST] (
[identificador] [int] IDENTITY (1, 1) NOT NULL CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED ,
[nombre] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[apellido] [varchar] (50) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[direccion] [varchar] (100) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[fechanacimiento] [datetime] NOT NULL ,
[login] [varchar] (20) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
GO

Insertemos ahora algunos registros en la base tabla de prueba, copiándolos de EMPLOYEE en la base de datos PUBS.

insert into test (nombre, apellido, direccion, fechanacimiento)
(select fname, lname, fname + ' ' + lname + ' ' + cast(hire_date as varchar(100)), hire_date from pubs..employee)

Ahora tenemos nuestra tabla, con un índice agrupado (cluster) en la columna de identidad, y con un cantidad de registros pequeña. Para el objetivo de nuestra demo no es necesario tener gran cantidad de registros.

Existen dos formas tradicionales de ver las estadísticas. Una de ellas es consultando el catálogo interno de SQL Server o la otra es a través de la interfaz gráfica asociada al plan de ejecución de una consulta. Como por ahora no es mi intención ver los planes de mantención, veremos como obtener las estadísticas desde el catálogo interno de SQL Server.

Antes de ver como se hace, debemos conocer como se consultan los catálogos internos. Algunas de las tablas a las que hacemos referencia en las consultas no necesariamente existen físicamente y muchas de ellas, o son vistas solamente o se construyen al momento de ejecutar. Además, los objetos (tablas, procedimientos, etc.) rara vez están almacenados por el nombre que nosotros le damos, sino que por un identificador interno. Para obtener el identificador interno de un objeto, existe una función llamada object_id('objeto') que lo retorna, pudiendo usarse en una consulta o una instrucción set. El catálogo donde se almacena la información de los índices y estadísticas de una tabla se llama sysindexes. Para consultarlo se debe entonces filtrar la información por el id de la tabla TEST, como se muestra en la siguiente consulta:

select * from sysindexes where id = object_id('test')

Como resultado de esta consulta, podrán obtener algo similar a lo siguiente. Vale la pena mencionar que está recortado hacia la derecha. El valor de id y otras columnas variará en cada ambiente.

id          status      first          indid  root           minlen keycnt groupid dpages      ...
----------- ----------- -------------- ------ -------------- ------ ------ ------- ----------- ...
1043495492  2066        0x300000000100 1      0x470000000100 16     1      1       2           ...

El resultado nos indica que para la tabla test (id = 1043495492), hay creado un solo índice agrupado (indid = 1) y que utiliza 2 páginas (dpages = 2).

Si realizamos ahora una consulta simple sobre la tabla buscando por la columna apellido, que sabemos no está considerada en ningún índice, se realizarán cambios sobre sysindexes. Por ejemplo, si la siguiente consulta retorna o no registros, las estadísticas serán creadas (por que nuestras base de datos está configurada para que las cree automáticamente).

 select * from test where apellido = 'gonzález'

Haciendo nuevamente la consulta sobre sysindexes, el resultado cambia, obteniéndose un nuevo registro. Se sabe que es una estadística ya que la cantidad de paginas (dpages = 0) y el grupo es 0.

id          status      first          indid  root           minlen keycnt groupid dpages      ... name                      ...
----------- ----------- -------------- ------ -------------- ------ ------ ------- ----------- ... ------------------------- ...
1043495492  2066        0x300000000100 1      0x470000000100 16     1      1       2           ... PK_TEST                   ...
1043495492  8388704     NULL           2      NULL           0      1      0       0           ... _WA_Sys_00000003_3E327A44 ...

Como vemos, se ha creado la estadística sin necesidad que preocuparnos. Ahora, si se quiere conocer que hay en la estadística, existe una instrucción de administración llamada dbcc showstatistics (tabla, indice|estadística) para hacerlo. El resultado de la ejecución de dbcc show_statistics (test, _WA_Sys_00000003_3E327A44) se despliega ahora.

Name                       Updated              Rows  Rows Sampled   Steps  Density       Average key length String Index
-------------------------- -------------------- ----- -------------- ------ ------------- ------------------ ------------
_WA_Sys_00000003_3E327A44  Feb  2 2006  8:55PM  100   100            89     1             14,23              YES




All density   Average Length Columns
------------- -------------- -------------------
0,01098901    14,23          apellido




RANGE_HI_KEY                                       RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
-------------------------------------------------- ------------- ------------- -------------------- --------------
administrador                                      0             4             0                    1
Aladino Carcamo                                    0             1             0                    1
Alvaro Vega                                        0             1             0                    1
...
...
Vladimir Vera                                      0             1             0                    1

A pesar de que puede parecer complicado el resultado, no lo es tanto. El resultado se divide en tres grupos.

El primer grupo nos entrega información general de la estadística. Acá encontramos el nombre, fecha de actualización, la cantidad de filas de la tabla (Rows = 100), la cantidad de filas que se consideraron para obtener la muestra (Rows Sampled = 100), la cantidad de pasos (steps = 89) (explicado más adelante), la densidad (no considerar este valor específico ya que la densidad se mide mejor después) y el largo promedio de los datos de la columna en el caso de una estadística o el largo promedio de los datos del índice en caso de ser un índice.

El segundo grupo muestra datos específicos asociados a la columna. En este caso, la densidad (0,01098901), el largo promedio (ya visto) y la columna. Para el caso de un índice, presenta varias líneas con información y densidades para las columnas del índice, mostrando desde la primera columna hasta todas las columnas juntas. La densidad se obtiene de calcular la siguiente ecuación:

Densidad = 1 / (cardinalidad de la llave del índice)

La cardinalidad de la llave corresponde a la cantidad de datos únicos de la columna o columnas. Lo importante es que la densidad sea el valor más chico posible. Mientras más pequeño, SQL Server obtendrá mejores resultados en las búsquedas. Por ejemplo, si la densidad de un índice es 0,3, significa que sólo se puede filtrar hasta un 30% de los datos con ese índice, resultado que puede considerarse muy malo. Un buen valor debe estar por debajo del 5%. En nuestro ejemplo, un densidad de 0,0109 (1%) significa que en la tabla hay 1/0,0109 valores diferentes, o 91. La consulta select count(distinct(apellido)) from test confirma el resultado.

El tercer bloque corresponde a la distribución de los datos de la columna en la tabla. Para un índice de varias columnas se considera sólo al valor de la primera columna. La información se segmenta por rangos (los pasos = 89), donde en cada línea se encuentran los valores correspondientes a los datos que están entre el RANGE_HI_KEY de esa línea y son menores a RANGE_HI_KEY de la línea de más abajo. Como la explicación no es del todo clara, con el ejemplo seguro se entenderá. En el resultado desplegado antes, se obtiene que entre administrador y Aladino Carcamo, no hay más valores (RANGE_ROWS = 0), hay 4 valores iguales (EQ_ROWS = 4), no hay valores diferentes en el rango sin considerar el mismo valor administrador (DISTINCT_RANGE_ROWS = 0), y para finalizar, el promedio de filas (cantidad) por cada valor distinto en el rango es uno (AVG_RANGE_ROWS = 1). Corresponde notar que DISTINCT_RANGE_ROWS no incluye las filas que son iguales a RANGE_HI_KEY ya que estas están incluidas en EQ_ROWS.

Toda esta información le permite saber al optimizador de consultas como es la información de la columna o índice, sin necesidad de "tocar" los datos. Y por el mismo motivo, si queremos que el analizador siempre encuentre la mejor opción y el servidor responda al máximo, debemos proveer a éste de las estadísticas actualizadas.

Para finalizar, podemos mencionar que las estadísticas pueden actualizarse o eliminarse manualmente a través de la interfaz gráfica o de consultas sql (drop statistics). Además, conviene saber que SQL Server se encarga de actualizarlas y eliminarlas cuando estima que es necesario, pero también puede agregarse una tarea de mantención que las actualice cada cierto tiempo.