Por José Mariano Alvarez

Resumen

En esta segunda nota voy a mostrar que hace el asistente Index Tuning Wizard (ITW) y como aprovechar las capacidades que tiene para poder mejorar la rendimiento de nuestra base de datos. En esta ocasión y para poder clarificar los conceptos, vamos a tomar en cuenta solo algunas consultas o actualizaciones simples. También vamos a corroborar con la base de datos actualizada, como el asistente ITW nos sugiere crear el tipo de índices que vimos en la primera nota.

Introducción

En la nota anterior vimos como en tablas relacionadas mediante claves foráneas la falta de un índice puede generar problemas de rendimiento. También vimos como al generar claves primarias el SQL Server 2000 genera índices únicos para poder satisfacerlas. Estas operaciones se encuentran relacionadas con las características que tiene el SQL Server para satisfacer los pedidos que se le hacen de la forma más eficiente posible.
El motor de la base de datos considera distintas estrategias y posibles caminos para resolver una consulta determinada. Para realizar esto se vale de la información que tiene respecto de los datos sobre los cuales tiene que operar y es por ello que cobra vital importancia el uso de las estadísticas. A partir de la versión 7.0 se han incorporado algunas características que hacen que el SQL Server no se requiera de tanto trabajo de un administrador de bases de datos para poder optimizar o mantener a los sistemas operando en forma eficiente a medida que se van actualizando datos. Entre estas características se encuentran la posibilidad de que genere y actualice automáticamente las estadísticas requeridas para determinar la aplicabilidad de las distintas estrategias. Así pues, si dejamos las opciones por defecto (no hicimos cambios en la base de datos model), el SQL Server va a generar todas las estadísticas que necesite y además la va a ir actualizando según ciertas estrategia que si desean pueden encontrar en la documentación.
Para poder mostrar algunas características vamos a desactivar la generación de nuevas estadísticas de la base de datos.

Desde el Analizador Corporativo (Enterprise Manager) vamos a seleccionar con el botón izquierdo del ratón, la base de datos índices que aparece en el árbol de la izquierda, apretamos luego el botón derecho y seleccionamos propiedades. Al hacer esto aparece una ventana con solapas, elegimos entonces la solapa de opciones y luego desmarcamos la opción de auto creación de estadísticas y apretamos el botón OK.

Una vez realizado esto, las operaciones que vayamos haciendo van a ser resueltas en el motor de la base de datos basadas en la estadística más reciente recogida para los índices existentes, ya que no desactivamos la casilla de actualización automática de estadísticas. Las estadísticas sobre los campos que no participan de índices no van a ser creadas y el motor va a utilizar ciertas heurísticas para su conveniencia.
Carga de la base de datos
Como primera medida para poder hacer las pruebas correspondientes y luego de desactivar la creación de estadísticas, vamos a cargar la base de datos que hemos creado en la nota anterior.

Seleccionamos primero la base de datos

use índices
go
set nocount on
go

Luego cargamos la tabla ingresos.

insert into Ingresos (Ingre_Codi,Ingre_Descrip) values (1,'Bajo')
insert into Ingresos (Ingre_Codi,Ingre_Descrip) values (2,'Medio')
insert into Ingresos (Ingre_Codi,Ingre_Descrip) values (3,'Alto')
go

Luego cargamos la tabla familias.

insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (1,'Perfumeria')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (2,'Vestimenta')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (3,'Limpieza')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (4,'Congelados')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (5,'Frutas')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (6,'Verduras')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (7,'Lacteos')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (8,'Panaderia')
insert into Familias (Produc_Fami,Produc_Fami_Descrip) values (9,'Almacen')
go

Luego cargamos la tabla productos.

insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(1,'Desodorante',1,3.5)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(2,'Perfume',1,6.3)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(3,'Fecula',1,4.6)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(4,'Champu',1,7.2)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(5,'Pantalon',2,30.3)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(6,'Remera',2,10.0)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(7,'Medias',2,3.7)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(8,'Malla',2,15.3)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(9,'Camisa',2,25.35)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(10,'Saco',2,40.23)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(11,'Jabon',3,1.3)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(12,'Detergente',3,1.78)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(13,'Trapo',3,0.86)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(14,'Suavizante',2,3.45)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(15,'Apresto',3,2.59)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(16,'Limpiador',3,1.49)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(17,'Lavandina',3,1.27)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(18,'Pescado',4,4.62)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(19,'Hamburguesa',4,3.24)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(20,'Mariascos',4,12.48)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(21,'Patitas',4,3.68)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(22,'Helado',4,2.43)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(23,'Manzana',5,1.29)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(24,'Banana',5,3.25)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(25,'Anana',5,4.14)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(26,'Durazno',5,2.26)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(27,'Espinaca',6,1.13)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(28,'Zanahoria',6,0.67)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(29,'Apio',6,0.98)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(30,'Lechuga',6,1.06)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(31,'Leche',7,1.35)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(32,'Yogur',7,0.96)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(33,'Flan',7,1.25)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(34,'Pan',8,2.5)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(35,'Masas',8,15.3)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(36,'Factura',8,3.5)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(37,'Aceite',9,4.56)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(38,'Vinagre',9,2.36)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(39,'Azucar',9,1.03)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(40,'Condimentos',9,3.5)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(41,'Arroz',9,1.09)
insert into Productos ( Produc_Codi,Produc_Descrip,Produc_Fami,Precio) 
Values(42,'Harina',9,2.56)
go

Ahora es el turno de generar los clientes mediante una iteración. Se ha utilizado un case y el resto de la división entera para asignar nivel de ingreso del cliente asignando prácticamente el 50% a nivel bajo y el resto repartiéndolo entre medio y alto con una proporción de casi 2 a 1.

declare @Contador int
Set @Contador=1
While @Contador < 500
begin
 insert into Clientes ( Clien_Codi, Clien_Descrip, Ingreso_Codi)
 select @Contador, 'Cliente ' + ltrim(rtrim(str(@Contador))) , 
 case when @Contador % 2 = 0 then 1 --Bajo
 else 
   case when @Contador % 3 = 0 then 3 --Alto
   else 2 --Medio
   end
 end
 Set @Contador=@Contador+1
end
go

Vamos a generar las facturas mediante un programa iterativo. La función RAND es usada para generar los valores aleatorios. La complejidad de la generación de valores aleatorios se debe a que dentro de un mismo lote el valor del numero aleatorio es prácticamente el mismo ya que usa la misma semilla de generación. Por ese motivo se debía incorporar elementos que hagan variar la semilla y es lo que precisamente se hace al incorporar el contador y las funciones DATEPART y GETDATE. Uno podría pensar en generar una función con CREATE FUNCTION pero dado que la función GETDATE es no determinista, no se la puede usar y por lo tanto no se puede crear esta función. El WAITFOR DELAY tiene por objeto que la función DATEPART vaya cambiando de forma tal que junto con contador, se vayan generando nuevos valores de semilla y los números aleatorios tengan una variabilidad suficiente.

declare @Contador int
Set @Contador=1
While @Contador < 20000
begin
 insert into facturas( Factu_Nume, Fecha, Impor, Clien_Codi)  
 select 
  @Contador, 
  dateadd(dd,@Contador % 133 ,'20000101'),
  0.0,
  CAST(rand(@Contador * 73663 + DATEPART(ms, GETDATE()) * 135793 ) * 499 + 1 AS INTEGER)
 WAITFOR DELAY '00:00:00:002'
 Set @Contador=@Contador+1
end

Como se puede notar en el cuadro anterior se generan cerca de 20000 facturas en las cuales el cliente es elegido al azar y el importe de las mismas esta en cero. Pronto vamos a actualizar este valor mediante sentencias SQL que vamos a analizar.

Lo mismo ocurre en la generación de ítems de factura respecto de los valores aleatorios. Aquí se ha generado cuatro productos por factura tomados al azar de entre los dados de alta anteriormente, la cantidad vendida también es elegida al azar (de 1 a 10) y el importe en cero para luego actualizar mediante una sentencia SQL que también vamos a analizar

declare @Contador int
declare @Contador2 int

Set @Contador=1
While @Contador < 20000
begin
 Set @Contador2 = 0
 While @Contador2 < 4
 begin
  insert into items(Factu_Nume, Produc_Codi, Canti, Impor )  
  select
   @Contador,   
   CAST(rand(@Contador2 * 73663 + DATEPART(ms, GETDATE()) * 135793 ) * 42 + 1 AS INTEGER),
   CAST(rand(@Contador2 * 63767 + DATEPART(ms, GETDATE()) * 133737 ) * 10 + 1 AS INTEGER),
   0.0
  Set @Contador2 = @Contador2 + 1
 end
 WAITFOR DELAY '00:00:00:002'
 Set @Contador=@Contador+1
end

Análisis de consultas simples

Vamos ahora a analizar dos consultas que aunque parecidas tienen planes de ejecución muy diferentes.

Select i.* 
from items I
inner join productos P 
 on I.produc_codi = P.Produc_Codi

Select * 
from items I
inner join productos P 
 on I.produc_codi = P.Produc_Codi

Si las comparamos, vamos a notar que ambas consultas están realizadas sobre las tablas Items y Productos que están relacionadas mediante un inner join por produc_codi. Precisamente este va a ser el join que se va a utilizar en un futuro para actualizar el precio dentro de la tabla Items. Por ahora solo vamos a analizar lo que sucede en estas consultas en las cuales la única diferencia encontrada es el conjunto de columnas de salida. En el primer caso estamos pidiendo solo las columnas de la tabla Items y en el otro las columnas de ambas tablas, y aunque parezca menor, es una diferencia sustancial.

La primera consulta produce el siguiente plan de ejecución que podemos ver con la opción de mostrar el plan de ejecución disponible en el menú consulta o en la barra de herramientas..

Como vemos el motor de base de datos ha simplificado la consulta tomando en cuenta que existe una clave foranea en la tabla items referenciando la tabla productos y por lo tanto no requiere realizar el join. Además si nos paramos en el símbolo del “cluster index scan” que esta en rojo (al igual que en cualquier otro símbolo del plan), podremos ver más detalles del operador involucrado. Por estar en rojo nos da la opción para crear estadísticas faltantes en el menú contextual que aparece al apretar el botón derecho.  En este caso, en la tabla items no esta creado el tipo de índice que viéramos en la primera nota, por lo que a pesar de que no lo requiere para resolver la consulta ya que no estamos borrando registros de la tabla productos, nos sugiere la creación de una estadística sobre el campo produc_codi para que el motor pueda seleccionar en forma más efectiva un plan de ejecución.

Es importante aclarar que este tipo de estadísticas se crearían automáticamente si no las hubiéramos desactivado.

El siguiente es el plan de ejecución de la segunda consulta

Como vemos en este segundo plan, al requerir todos los atributos de las dos tablas, no  queda más remedio que recorrerlas y hacer el join. No se puede simplificar. Al igual que antes nos sugiere la creación de la estadística.

Actualizaciones de importes en la tabla Items

Llegado este punto debemos actualizar los importes que han quedado en cero en la tabla ítems, para lo cual vamos a usar el siguiente query donde el importe de la tabla items será el resultado de multiplicar el precio del producto obtenido de la tabla productos por la cantidad de ítems. Antes de ejecutar vamos a activar la bandera que nos da la cantidad de IO en la solapa de propiedades de conexión que se encuentra en la ventana de opciones.

update items
set Impor = P.Precio * Items.Canti
from productos P 
where Items.produc_codi = P.Produc_Codi

Esto nos da el siguiente resultado:

Table 'Items'. Scan count 1, logical reads 480541, physical reads 34, read-ahead reads 129.
Table 'Productos'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Si lo ejecutamos nuevamente nos da:

Table 'Items'. Scan count 1, logical reads 240553, physical reads 0, read-ahead reads 0.
Table 'Productos'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Aquí vemos que el SQL Server mantiene en cache las paginas usadas más recientemente, lo cual se puede ver a partir de la disminucion de la cantidad de physical reads (lecturas físicas). Este “working set” junto con la creación de estadísticas, suele ser en muchas ocasiones el motivo por el cual la ejecución de ciertos programas comienza lenta y va mejorando con el tiempo.
También vemos que ha cambiado la cantidad de logical reads (lecturas lógicas) por lo que ha habido alguna actividad diferente dentro del motor en ambos update. Si reiteramos el update un par de veces más, vamos a ver que prácticamente no cambian los resultados con este ultimo resultado por lo que lo vamos a comparar este resultado usando otra estructura de índices. Antes vamos a ver el plan de ejecución donde muestra nuevamente la falta de la estadística y que recorre la tabla para hacer el join:

 
Puede ampliar la imágen haciendo click

Para comparar con otra estructura de índices, primero seleccionamos el update o lo colocamos en una nueva ventana y ejecutamos el asistente Index Tuning Wizard (Ctrl-I), para que el SQL Server nos de las recomendaciones de que tipo de índices conviene generar.

Una vez iniciado el asistente, desmarcamos la opción que evita la eliminación de índices ya creados con anterioridad y marcamos las opciones que permiten crear vistas indexadas y análisis avanzado, luego seleccionamos la opción de consulta seleccionada y las tablas Items y Productos, y para finalizar seleccionamos la opción de guardar el query que ha generado.

Eliminando las partes irrelevantes queda:

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [Items3] ON [dbo].[Items] ([Produc_Codi] ASC, [Canti] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

Como vemos aquí genera un “cover index” sobre los campos produc_codi y canti que evita que se vaya a la tabla Items para realizar el join y hacer el calculo para actualizar. Según la información que posee de la base de datos, el asistente presupone una mejora de rendimiento del 41% debido al cambio de estrategia de join y a la disminución de la cantidad de páginas usadas. Puesto que el índice no clustered no apunta a las pagina de datos sino al clustered index almacenando, posee el valor clave solo con recorrer este nuevo índice y entonces alcanza para hacer el join y tener el valor de la clave para poder hacer posteriormente el update tal como se ve en el siguiente plan de ejecución.

Puede ampliar la imágen haciendo click

Actualizaciones de importes en la tabla facturas

Ahora nos toca actualizar las facturas a partir de los importes que acabamos de actualizar en la tabla Items. Para ello utilizamos el siguiente update:

update facturas 
set impor = Totales.impor
from(
 select 
  I.factu_nume,
  sum(I.impor) Impor
 from 
  items I
 group by 
  I.factu_nume
) Totales
where
 totales.factu_nume=facturas.factu_nume

Si lo ejecutamos obtenemos el siguiente plan de ejecución donde podemos observar claramente como el subquery se resuelve primero y luego se hace el join correspondiente para realizar el update.

Puede ampliar la imágen haciendo click
 
La cantidad de lecturas realizadas sera:

Table 'Facturas'. Scan count 1, logical reads 73, physical reads 1, read-ahead reads 72.
Table 'Items'. Scan count 1, logical reads 565, physical reads 40, read-ahead reads 402.

Luego de esto uno podría presuponer cual es la mejor alternativa de índices para esta consulta en particular. Para ello recurrimos nuevamente al asistente usando las mismas opciones pero solo las tablas items y facturas. El resultado es el siguiente:

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

DROP INDEX [dbo].[Items].[Items3]
IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

go
CREATE VIEW [dbo].[_hypmv_0] WITH SCHEMABINDING AS 

SELECT  
[dbo].[items].[factu_nume] as _hypmv_0_col_1,  
SUM([Índices].[dbo].[Items].[Impor]) as _hypmv_0_col_2,
count_big(*) as _hypmv_0_col_3 
FROM  [dbo].[items]  
GROUP BY  [dbo].[items].[factu_nume]  
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE UNIQUE CLUSTERED INDEX [_hypmv_02] ON [dbo].[_hypmv_0] ([_hypmv_0_col_1] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

/* Statistics to support recommendations */

CREATE STATISTICS [hind_2041058307_1A_4A] ON [dbo].[items] ([factu_nume], [impor])

No lo ejecutemos por el momento. Veamos que nos sugiere que eliminemos el primer índice, la creación de una vista indexada y la creación de una estadística y con ello conseguiríamos una mejora estimada en el orden del 66%. ¿ Pero qué pasa si debemos ejecutar los dos update en lugar de uno solo? Si ahora ejecutamos el asistente ITW sobre los dos update, seleccionando las tres tablas involucradas y las mismas opciones que antes, vemos que no nos sugiere que realicemos ningún cambio respecto de los ya realizados para el primer update.

Ahora si realizamos el cambio y volvemos a correr el asistente para los dos update, sorprendentemente no nos sugiere que realicemos ningún cambio, lo cual contradecirla lo que sugiere en el caso anterior. Sin embargo debemos entender que la existencia de la vista indexada y las estadísticas correspondientes asociadas a esta vista pueden ser la causa de esta diferencia de criterio.

Ahora si volvemos a ejecutar el update sobre la tabla Items vemos como se ha complicado para el motor la estrategia de actualización y ha aumentado la cantidad de lecturas lógicas y también ha aumentado el tiempo de ejecución de este query en particular.

Table 'Worktable'. Scan count 1, logical reads 20335, physical reads 0, read-ahead reads 0.
Table '_hypmv_0'. Scan count 2, logical reads 40144, physical reads 1, read-ahead reads 72.
Table 'Worktable'. Scan count 2, logical reads 161975, physical reads 0, read-ahead reads 0.
Table 'Items'. Scan count 1, logical reads 240553, physical reads 216, read-ahead reads 283.
Table 'Productos'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Eliminación de registro de productos

Vamos ahora a comprobar lo expresado en la nota anterior respecto de las claves foráneas. Para ello ejecutamos:

delete productos 
where Produc_descrip = 'Ninguno'

Si analizamos el plan de ejecución vemos como hace un scan del clustered index de la tabla Items tal como vimos en la nota anterior.

 
Puede ampliar la imágen haciendo click

Aquí la existencia del cover index del primer update ayudaría a mejorar la perfomance ya que la comprobación la haría sobre el índice que en tamaño es más pequeño que el  clustered index. Si corremos el asistente ITW veremos que nos sugiere la creación del tipo de índice del que habláramos en la nota anterior.

BEGIN TRANSACTION
SET @bErrors = 0

CREATE NONCLUSTERED INDEX [Items7] ON [dbo].[Items] ([Produc_Codi] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

Conclusión
Es importante que entendamos que la creación de índices no debe estar orientada a solucionar específicamente un problema único sino para resolver una problemática en general. Como vimos el Index Tuning Wizard es una excelente herramienta para ayudarnos a crear índices. Para la creación de los índices de una base de datos suele ser necesario usar un enfoque integrador ya que como vimos en los ejemplos anteriores distintos sentencias SQL para ser optimizadas requerirían distintos índices La creación de muchos índices por tabla afectaría negativamente las actualizaciones ya que habría que actualizar también los índices y como vimos en algunos casos hasta nos sugiere la eliminación de índices existentes. Por este motivo, un esquema de índices correcto suele ser un problema bastante difícil de resolver. En esta ocasión vimos como resolver el problema para un query específico. El SQL Server nos da algunas ayudas adicionales que vamos a ver en la próxima nota para resolver los problemas más complejos.