¿Cómo reducir el costo de un LookUp de SQL Server?

Durante la exposición de nuestra charla de como optimizar consultas de SQL Server, hice bastante hincapié en la relación/dependencia de los índices no agrupados (no clustered) y el agrupado (clustered) de un tabla. En caso de que ésta no cuente con un índice agrupado, existe una relación/dependencia similar pero con una estructura interna de la tabla (RID).


En este post veremos qué es un LookUp (SQL 2005) o Bookmark LookUp (SQL 2000) y cómo eliminarlos o al menos mitigarlos cuando se pueda. Usaré SQL 20005, pero si tienes 2000, la manera de enfrentarlo es similar.


Antes de ir a la información del caso, haremos un pequeño repaso de lo revisado en la charlas de optimización de consultas.


Relación/dependencia entre índices en una tabla


Un índice no agrupado almacena, por cada elemento del índice, un “identificador” que dice donde encontrar en la tabla los datos asociados a este ítem del índice. De esta forma, cada vez que SQL Server utiliza el índice no agrupado y determina que debe ir a buscar los datos, utiliza ese “identificador” para ubicar los datos en la tabla.


Si la tabla no tiene un índice agrupado, el identificador utilizado es el identificador de la fila o row id (en inglés), el cual se representa con las siglas RID. Este RID está compuesto por el numero del archivo de la base de datos, el número de la página dentro de ese archivo y por último, el número de la fila en la página.


Si la tabla tiene un índice agrupado, el identificador utilizado es la llave del índice agrupado. La llave del índice agrupado son TODAS las columnas del índice agrupado. Por favor, no confundir la llave del índice con la llave primaria de una tabla, aunque muchas veces tienen el mismo “valor,” en especial cuando la tabla se creó sin darle mayor importancia a este tema.


Para más información, revisa el BOL (Books On Line) de SQL Server, en la instrucción Create Index.


Bookmark LookUp o LookUp a secas


En SQL Server 2000 se le conoce como Bookmark LookUp. En la versión 2005 se llama solamente LookUp. La idea es la misma para ambas versiones; El problema es el mismo, las soluciones similares.


LookUp, traducido al español, significa operación de búsqueda, y representa exactamente eso. Realiza una operación de búsqueda, con un “identificador” de una fila, para traer más información, de la fila específica, desde la tabla.


La búsqueda se hace, como ustedes se imaginan, utilizando el RID si la tabla no tiene un índice agrupado, o utilizando la llave del índice agrupado, si la tabla cuenta con uno de ese tipo. La siguiente imagen sirve para aclarar el concepto que se trata de explicar.







Utilizando el índice no agrupado, primero se identifica las filas que cumplen con la condición de filtrado.


Resultado: Celdas amarillas.


 


 


Se usan los tres identificadores de filas (rojo, verde y azul) y se realiza la operación de búsqueda en el índice agrupado.


Si la tabla no tiene índice agrupado, la operación es similar, pero usa el RID.


Se retornan los datos faltantes.


Veamos entonces ahora información del caso. En esta oportunidad reproduciré el escenario en una base de datos de prueba, con unas tablas creadas para la ocasión.


LookUp muy costoso


Nota: Hace un tiempo atrás estaba en un curso de optimización de SQL Server y le hice la siguiente pregunta a la instructora: “Si tengo el plan de ejecución de una consulta, y el operador de lookup es responsable de casi el 100% del costo del plan, ¿cómo puedo mejorar el rendimiento de mi consulta?.”


Tristemente, no hubo una respuesta aclaratoria ante mi pregunta, como ocurrió en varias otras además. Hoy en día, retrospectivamente, pienso que la instructora era lo que nosotros acá en Chile llamamos “un Adán”, de Adán y Eva. ¿Por qué?. Porque si a Adán le sacas la hojita, queda desnudo. En este caso, la “hojita” es el “libro del curso.”


Si has estado tratando de optimizar consultas de SQL, es probable que ya te hayas topado con este problema. ¿Qué hacer cuando la operación LookUp es muy costosa?


Antes de comenzar, estructura de tablas


Para este ejemplo, utilizaremos dos tablas, t1 y t2, con la siguiente estructura cada una






CREATE TABLE [dbo].[t1](
    [c1] [int] IDENTITY(1,1) NOT NULL,
    [c2] [varchar](50) NOT NULL,
    CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED ([c1] ASC) ON [PRIMARY])
ON [PRIMARY]

CREATE TABLE [dbo].[t2](
    [c1] [int] IDENTITY(1,1) NOT NULL,
    [c2] [int] NOT NULL,
    [c3] [varchar](50) NOT NULL,
    [c4] [tinyint] NOT NULL CONSTRAINT [DF_t5_c4] DEFAULT ((1)),
    [c5] [char](200) NOT NULL CONSTRAINT [DF_t2_c5] DEFAULT (”),
    CONSTRAINT [PK_t2_1] PRIMARY KEY CLUSTERED ([c1] ASC) ON [PRIMARY])
ON [PRIMARY]

CREATE NONCLUSTERED INDEX [idx_c2] ON [dbo].[t2] ([c2] ASC)
ON [PRIMARY]

ALTER TABLE [dbo].[t2] WITH CHECK
ADD CONSTRAINT [FK_t2_t1] FOREIGN KEY([c2]) REFERENCES [dbo].[t1] ([c1])


La tabla t1 tiene 100.000 registros. La tabla t2 tiene 1.000.000 registros. La consulta que estamos realizando es la siguiente.






select t1.c1, t1.c2, t2.c1, t2.c3, t2.c4
from t2 inner join t1 on t2.c2 = t1.c1
where t2.c2 between 1000 and 2000 and t2.c3 like ‘2%’ and t2.c4 = 1 


Con esta consulta, estamos trayendo un cantidad importante de columnas de la tabla t2, como también las dos columnas de la tabla t1. Existe un índice en t2.c2, por lo tanto, el filtro between 1000 and 2000 se realizará utilizando el índice para reducir las filas.


El siguiente es el plan de ejecución generado, en versión texto y gráfico.






Table ‘t2’. Scan count 1, logical reads 30682, physical reads 0, …
Table ‘t1’. Scan count 1, logical reads 6, physical reads 0, …

Rows Executes StmtText
——————————————————————————————-
561 1 |–Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t
1001 1 |–Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([
561 1 |–Filter(WHERE:([test].[dbo].[t2].[c3] like ‘2%’ AND [test].[dbo].
10010 1 |–Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2
10010 1 |–Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:(
10010 10010 |–Clustered Index Seek(OBJE…]) LOOKUP ORDERED FORWARD)


Si miramos la versión gráfica, podremos observar que el 100% del costo del plan esta en la operación lookup. Notar que hemos marcado algunos textos en blanco arriba, los cuales revisaremos más adelante. Para ver el plan gráfico más grande, haz clic sobre él.







En el plan gráfico, podemos ver que la operación Key Lookup es responsable del 100% del costo de plan, con un costo de 22,06 (muy malo para un operador, y en consecuencia para el plan). Complementando con el plan de texto, en éste aparece la palabra LOOKUP, y está usando el índice agrupado (clustered) para recuperar las demás columnas que necesita.


Además, se realizan más de 30.000 lecturas sobre la tabla t2. 


¿Donde está el problema?¿Por qué tiene un costo tan elevado?


Una única operación de LookUp es de bajísimo costo, más aún si utiliza un índice para hacer un seek a una fila específica en la tabla. Sin embargo, si se ejecuta 10.010 veces, la sumatoria de estos costos pequeñísimos pasa a tener relevancia, como es nuestro caso.


La cantidad de veces que se ejecuta la operación LookUp se puede ver en 2 partes. Una de ellas es el texto marcado con blanco en el plan de ejecución de texto y la otra es en el Tooltip amarillo, en “Actual Number of Rows.”


Solución[es] al problema


Existen dos formas de enfrentar este problema, y por supuesto, dependen de cada escenario.


Una de ellas, y que no veremos ahora porque no tienen ninguna gracia, es agregar todas las columnas necesarias al índice, y con eso, evitar la operación LookUp. Si tengo toda la información en el índice, no necesito ir a buscar más columnas a la tabla. Esta no falla nunca, pero uno debe evitar incluir muchas columnas en los índices.


La segunda solución, y que es mucho más elegante, sólo es posible realizar en un escenario como el que tenemos ahora.







Si miran con detenimiento el plan de ejecución, el operador filter está filtrando en las columna c3 y c4, aplicando los criterios definidos en la consulta SQL. Esto es esperable.


Más aún, como resultado del filtrado, la cantidad de filas retornadas disminuye (anchos de las flechas de entrada y de salida del filter). Esto también es esperable, ¿o no?

 
 

Si todo es esperable, ¿dónde está la gracia?


El costo del operador LookUp es muy alto porque se ejecuta muchas veces, ¿verdad?. ¿Qué sucedería si logro reducir la cantidad de veces que deba ejecutarse? Seguramente el costo de la consulta será menor y el rendimiento de ésta mucho mayor.


Para reducir la cantidad de veces que se ejecuta, debo reducir la cantidad de filas que son retornadas luego de que se filtre utilizando el índice idx_c2. Para reducir la cantidad de filas retornadas, agrego una columna más al índice, la que más me ayude a filtrar.


Debido a que la selectividad de la columna c3 es mejor que la de c4, incluiré sólo la columna c3 en el índice idx_c2. Con eso, reduciré la cantidad de filas que requerirán un lookup de más información. Recuerden que estamos tratando de minimizar el costo del LookUp. Si se quiere suprimir totalmente, se deberán agregar todas las columnas necesarias al índice, solución que deberá dejarse sólo para casos ultra-extremos.


Veamos el plan de ejecución de texto de la misma consulta, pero con el índice idx_c2 incluyendo a la columna c3 además de la c2 que ya tenía.





Table ‘t2’. Scan count 1, logical reads 3332, physical reads 0, …
Table ‘t1’. Scan count 1, logical reads 6, physical reads 0, …

Rows Executes StmtText
——————————————————————————————-
561 1 |–Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t
1001 1 |–Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([
561 1 |–Filter(WHERE:([test].[dbo].[t2].[c4] = (1)))
1075 1 |–Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2
1075 1 |–Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:(
1075 1075 |–Clustered Index Seek(OBJE…]) LOOKUP ORDERED FORWARD)


 







Ahora sólo ejecutó 1.075 veces el LookUp. No nos hemos deshecho de él, pero al menos hemos reducido su impacto en el costo de la consulta.  


Las lecturas lógicas fueron reducidas desde más de 30.000 a sólo 3.332. Una excelente optimización.


El nuevo costo del operador LookUp es 3,58, como se puede observar a la derecha en el Tooltip. El costo antes de modificar el índice era de 22,06. Otra excelente reducción en el costo.

 

Conclusión


Hemos visto como minimizar el impacto de la operación LookUp en SQL Server. No vimos como eliminarlo ya que para lograr eso, el camino es directo y simple. A veces no es necesario eliminarlo para mejorar drásticamente el rendimiento de una consulta.


 


Hasta la próxima, desde Santiago de Chile
Patrick.
 

Charla de optimización de consultas de SQL

Aunque no ha faltado trabajo, las últimas semanas las pasé preparando material para una charla de optimización de consultas en SQL Server que dimos hace algunos días.


Ya ha pasado casi una semana desde que dimos la charla, a la cual asistieron, si mal no recuerdo, 139 personas. Todo salió casi perfecto. Nos faltó tiempo para hacer las demos, aunque los asistentes nos aguantaron estoicamente desde las 6:35 pm hasta las 10:15 pm, 3 horas y 40 minutos. Impresionante.


Algo muy gratificador, y que no siempre ocurre lamentablemente, es poder lograr buena interacción con los asistentes. Usualmente no preguntan mucho, pero en esta oportunidad, estimo que se deben haber realizado 30 o más preguntas y contra preguntas. Así da gusto!!.


La imagen que usamos como ícono de la charla causó risas y simpatía entre nuestros amigos más cercanos.



Los principales temas abordados fueron:




  • Definición de plan de ejecución, como se construye, que elementos influyen en éste,



  • Detección de problemas en planes de ejecución



  • Evaluación de costos, lecturas lógicas



  • Teoría de índices, relaciones entre índice agrupados y no agrupados



  • Buenas prácticas, selectividad, estadísticas, fill factor, entre otras cosas.


Aquí van algunas fotos de la preparación de la charla, el evento propiamente tal y la celebración al final. No incluí fotos con asistentes porque uno nunca sabe si quieren o no aparecer. Para ver las fotos más grades, pincha en éstas.












 

 


Si te interesa ver el contenido de la charla, ésta está publicada en [cafeina], precisamente éste link.


Estamos viendo de realizar una continuación de la charla, sólo para los registrados de ésta, con el fin de poder terminar las demos y resolver más dudas.


desde Puyehue, al sur de Chile


Patrick


PD: Esta es la vista desde mi habitación acá en el sur de Chile [:)]


 

Estadisticas de SQL Server, parte 2

Continuando con la revisión de las estadísticas de SQL Server, veremos ahora cuando es recomendable no habilitar la actualización automática y reemplazarla por un proceso manual. Debo aclarar que el proceso debe realizarse igual para obtener el máximo rendimiento, pero como no será responsabilidad del servidor, será nuestra responsabilidad elegir el momento y cómo se hará.


En el post anterior revisamos que son las estadísticas, cómo se componen y cuándo se generan. Veamos ahora como se actualizan.


Si recordamos la consulta para obtener las estadísticas de una tabla llamada test, y la volvemos a ejecutar obtendremos información como la siguiente:


select * from sysindexes where id = object_id(‘test’)





id          status      first          indid  root           minlen keycnt … rowmodctr … name                      …
———– ———– ————– —— ————– —— —— … ——— … ————————- …
1043495492 2066 0x300000000100 1 0x470000000100 16 1 … 100 … PK_TEST
1043495492 8388704 NULL 2 NULL 0 1 … 0 … _WA_Sys_00000003_3E327A44

Previamente mencionamos que SQL Server las actualiza automáticamente, pero ¿cuando sabe que tiene que actualizarlas?. Como no existen procesos mágicos, la única forma es darse cuenta de que la estadística ya no refleja la realidad es por que los registros han sido modificados, y para eso el servidor lleva la cuenta de cuantos registros se han modificado, insertado y eliminado. Es posible que haya nuevos datos y que se generen nuevos rangos (RANGE_HI_KEY), como también más valores repetidos (EQ_ROWS). Puede incluso que la densidad de nuestra estadística sea menor y ahora si sea útil para las búsquedas.


Una columna que no agregamos en la publicación anterior, y que es existe en sysindexes, es rowmodctr. Ésta lleva el registro de los cambios en la tabla, pero sólo para la o las columnas involucradas en la estadística, y cuando este contador llega a cierto límite, es momento de que sean actualizadas. Hagamos algunas modificaciones a la tabla entonces y volvamos a consultar por las estadísticas.


update test set apellido = ‘Alvaro Vegaaaaa’ where apellido = ‘Alvaro Vega’


Recordemos que el cambio debe hacerse en la o las columnas involucradas. Es obvio que si cambian otras columnas, la estadística seguirá siendo representativa. Una nueva consulta a sysindexes retorna:





id          status      first          indid  root           minlen keycnt … rowmodctr … name                      …
———– ———– ————– —— ————– —— —— … ——— … ————————- …
1043495492 2066 0x300000000100 1 0x470000000100 16 1 … 100 … PK_TEST
1043495492 8388704 NULL 2 NULL 0 1 … 1 … _WA_Sys_00000003_3E327A44

El límite que mencionamos recién, y que corresponde al momento en que el servidor decide actualizar las estadísticas está dado por la siguiente ecuación:


Cantidad de modificaciones > 500 + 0,2 * (cantidad de filas de la tabla)


De esta ecuación se puede concluir que mientras más grande la tabla, son necesarias más modificaciones para generar una actualización. Esto está bien ya que mientras más larga la tabla, las modificaciones afectan en menor forma la variación de los datos y las estadísticas seguirán reflejando la realidad por más tiempo. Con una base de 500 se garantiza que no haya actualizaciones muy seguido para tablas más pequeñas.


En sysindexes existe otra columna que no hemos visto hasta ahora. Esta es StatVersion, que refleja la cantidad de veces que la estadística ha sido actualizada. Después de cierta cantidad de actualizaciones (numero no conocido) , la estadística se elimina. En caso de necesitarse posteriormente, se vuelve a crear. Desde el punto de vista del rendimiento, crear un estadística de cero o actualizar una existente no es muy diferente.


Para inhabilitar la actualización automática de estadísticas, se debe configurar la base de datos de esa forma. Las únicas justificaciones para hacerlo pueden deberse a que por la naturaleza de la aplicación no son necesarias o por que el costo de la actualización es prohibitivo en un servidor con mucha carga. En este último caso se pueden actualizar manualmente en algún momento donde no haya demasiada carga como los fines de semana o un día de madrugada, aunque esto dependerá del tipo de aplicación.


Para actualizar las estadísticas manualmente se pueden ejecutar cualquiera de estas dos opciones:



  • update statistics
  • sp_updatestats

La diferencia principal entre estos métodos es que con el primero se puede controlar que tabla se quiere actualizar, y si se quiere para todos sus índices y estadísticas o para alguno específico, o para todos los índices o todas las estadísticas de la tabla. Además se pueden definir otros parámetros como la cantidad de filas de la tabla a revisar (numero, porcentaje, etc.) como también si se quiere utilizar el mismo valor que se utilizó la primera vez. Por otra parte, el procedimiento almacenado actualiza las estadísticas de todas las tablas de la base de datos que tengan cambios.


Un ejemplo que actualiza las estadísticas del apellido de nuestra tabla se muestra a continuación. Podrán encontrar muchas más opciones de ejecución en los libros en línea de SQL Server.


update statistics test _WA_Sys_00000003_5649C92D


Una vez ejecutado, el contador de actualizaciones (rowmodctr) vuelve a cero. Para una actualización masiva que actualiza todas las estadísticas de columnas e índices que han sufrido alguna modificación (rowmodctr > 0), ejecutamos


sp_updatestats


Por último, para la creación de estadísticas, existen instrucciones análogas a las primeras. Estas son create statistics y sp_createstats. El control de create statistics  es completo comparado con sp_createstats, pero este último tiene la ventaja de que crea estadísticas en todas las columnas de todas las tablas que poseen las siguientes características:



  • No son columnas computadas (formulas)
  • No son de tipo text, ntext e image
  • No poseen estadísticas
  • No son la primera columna de un índice.

Con estas instrucciones se puede controlar de forma completa las estadísticas de SQL Server. Les recuerdo que más información podrán encontrar en los libros en línea.


 

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.


 

Scripts utilizados en charla de optimización de SQL Server

Los archivos utilizados para la presentación y las demostraciones son los siguientes:


Presentacion : OptimizaciónSQLServer.zip


Script para contar procedimientos : ResultadosContadorProcedimientos.sql
Script para ver la duracion de consultas : ResultadosConsultasDuracion.sql
Análisis de indices parecidos : AnalisisIndicesParecidos.sql (utiliza una vista definida en vista.sql)
Análisis de fragmentación de tablas : AnalisisFragmentacionTabla.sql
Defragmentador de indices : DefragmentarIndices.sql


Archivos binarios de sql 7. Es requisito que esté instalado SQL 2000 en el mismo computador. Archivos


Algunos de estos scripts han sido recolectados de otros sitios web. No declaro autoría sobre ninguno de ellos.