Por José Mariano Alvarez

En esta serie de notas voy a mostrar primero la base técnica de como debíamos trabajar en forma manual a partir del análisis de la información que nos suministra el SQL Server 2000 en sus planes de ejecución y luego las herramientas que provee para ayudarnos a resolver el esquema de índices apropiado en una forma más efectiva y práctica. En esta primera parte se trata de mostrar el caso de una instrucción muy habitual en sistemas OLTP que puede resultar problemática si no se crea el esquema de índices apropiado. 

Introducción

En muchos casos, algunas instrucciones muy sencillas y que realizamos habitualmente sobre la bases de datos pueden tener muy mala performance. El caso que voy a analizar es el de la eliminación o actualización de un registro en una tabla foránea relacionada mediante un constraint.

Este ejemplo también va a servir de base para entender los planes de ejecución y los datos que se almacenan en la tabla del sistema que contiene información de los índices.

El modelo es un sencillo modelo de facturación tal como puede apreciarse en el siguiente esquema:

El modelo esta compuesto por las tablas cabecera e ítems de facturas y las tablas de productos y clientes. Además para hacer el modelo algo más completo y poder probar otro tipo de índices en las futuras notas, agregué las tablas ingresos y familias.

Creación de la base de datos las tablas

 En primer lugar, debemos crear la base de datos correspondiente:

-- Crea la base de datos

use master  

go

create database Indices  

go

-- Cambia a la base de datos creada

use Indices  

go

 Una vez creada la base de datos creamos las tablas del modelo.

-----------------------------------------------------

-- Creacion de tablas

-----------------------------------------------------

create table Ingresos ( 

      Ingre_Codi int not null,

      Ingre_Descrip char(100) not null)  

go

create table Familias ( 

      Produc_Fami int not null,

      Produc_Fami_Descrip char(100) not null)  

go

create table Productos ( 

      Produc_Codi int not null,

      Produc_Descrip char(100) not null,

      Produc_Fami int not null,

      Precio float not null)  

go

create table Clientes ( 

      Clien_Codi int not null,

      Clien_Descrip char(100) not null,

      Ingreso_Codi int not null)  

go

create table Items ( 

      Factu_Nume int not null,

      Produc_Codi int not null,

      Canti int not null,

      Impor float not null)  

go

create table Facturas ( 

      Factu_Nume int not null,

      Fecha smalldatetime not null,

      Impor float not null,

      Clien_Codi int not null)  

go

En este punto vamos a analizar la estructura de índices que hay en la base de datos. Para ello vamos a analizar la tabla del sistema sysindexes, de cual vamos a analizar por el momento solo los campos que se detallan a continuación:

Para analizar los índices usamos el siguiente SELECT que nos muestra la información relevante a nuestro análisis.

SELECT 

      id,

      indid,

      rowcnt,

      name

from sysindexes 

where 

      id > 100  -- Esto es para mostrar solo los índices que 

               -- nos interesan

Como vemos en el resultado del cuadro siguiente no hay ningún índice creado. Todos los indid están en 0 indicando que se han creado una tablas, la cuales están organizadas mediante una estructura de heap sin ordenamiento especifico alguno.

id          indid  rowcnt               name       

----------- ------ -------------------- ----------

1977058079  0      0                    Ingresos

1993058136  0      0                    Familias

2009058193  0      0                    Productos

2025058250  0      0                    Clientes

2041058307  0      0                    Items

2057058364  0      0                    Facturas

Creación de claves primarias

Llegado este punto, creamos las claves primarias para ver como cambia la estructura de índices.

Creamos la clave primaria sobre la tabla ingresos con el siguiente script:

-----------------------------------------------------

-- Creacion de los constraint de claves primarias

-----------------------------------------------------

alter table Ingresos

      add constraint Ingresos_PK primary key (Ingre_Codi)   

go

Si analizamos nuevamente la estructura de índices expresada en la tabla sysindexes, vemos como ahora hay un índice clustered para la primary key de la tabla ingresos, lo cual se puede ver en la columna indid con valor 1 para Ingresos_PK. Esto significa que si bien no hemos expresado la creación de índices, el SQL Server 2000 ha generado un índice clustered al crear el constraint Pimary Key

id          indid  rowcnt               name         

----------- ------ -------------------- -------------------- 

1977058079  1      0                    Ingresos_PK

1993058136  0      0                    Familias

2009058193  0      0                    Productos

2025058250  0      0                    Clientes

2041058307  0      0                    Items

2057058364  0      0                    Facturas

Luego creamos el resto de las claves primarias con el siguiente script:

alter table Familias

      add constraint Familias_PK primary key (Produc_Fami) 

go

alter table Productos

      add constraint Productos_PK primary key (Produc_Codi) 

go

alter table Clientes

      add constraint Clientes_PK primary key (Clien_Codi)   

go

alter table Items

      add constraint Items_PK primary key (Factu_Nume, Produc_Codi)   

go

alter table Facturas

      add constraint Facturas_PK primary key (Factu_Nume)   

go

Lo que no se ve

Vamos ahora a analizar que pasa internamente en la base de datos si se borra un registro en una tabla.

Para poder comparar entre el caso de borrar un registro de una tabla padre de una relación y borrar un registro de una tabla sin relación creamos la clave foránea de la tabla productos respecto de la tabla familias con el siguiente script:

-----------------------------------------------------

-- Creacion de los constraint de claves foranea

-----------------------------------------------------

alter table Productos

      add constraint Familias_Productos_FK1 foreign key (

            Produc_Fami)

       references Familias (

            Produc_Fami)  

go

Debemos tener en cuenta que las tablas no tienen datos y por lo tanto los planes de ejecución que se pueden ver a continuación pueden variar respecto de los planes de ejecución con tablas llenas de registros. Estas variaciones se deben a las estadísticas que puedan poseer estas tablas y que en estos momentos están vacías.. En las próximas notas vamos a analizar algunos de estos temas.

Para mostrar el plan de ejecución en forma de texto se debe utilizar SET SHOWPLAN_TEXT ON o también SET SHOWPLAN_ALL ON la cual además nos da información de cada paso. Al hacer esto, el query no se ejecuta sino que se muestre en la ventana de resultados la forma detallada de como sería ejecutada la instrucción internamente.

SET SHOWPLAN_TEXT ON 

go

DELETE familias where Produc_Fami_descrip = 'Familia 1'

go

SET SHOWPLAN_TEXT OFF

go

 Al ejecutar esto obtenemos el siguiente resultado:

StmtText   
------------------------------------------------------- 
  |--Assert (WHERE:(If NOT(([Expr1007] IS NULL)) then 0 else NULL))
       |--Nested Loops (Left Semi Join, OUTER REFERENCES:
          ([Familias].[Produc_Fami]), DEFINE:([Expr1007] = [PROBE VALUE]))
            |--Clustered Index Delete (OBJECT:([Indices].[dbo].[Familias].[Familias_PK]))
            |    |--Top (ROWCOUNT est 0)
            |         |--Clustered Index Scan (OBJECT:
                         ([Indices].[dbo].[Familias].[Familias_PK]), WHERE:
                         ([Familias].[Produc_Fami_Descrip]='Familia 1') ORDERED)
            |--Row Count Spool
                 |--Clustered Index Scan (OBJECT:
                    ([Indices].[dbo].[Productos].[Productos_PK]), 
                    WHERE:([Productos].[Produc_Fami]=[Familias].[Produc_Fami]))

Si miramos con cuidado vemos que busca los registros a borrar de la tabla “familias” haciendo un scan sobre el índice clustered mediante un “Clustered Index Scan”, que es lo mismo que decir un scan sobre la tabla ya que se encuentra físicamente ordenada por ese índice y luego un “Clustered Index Delete” para borrar los registros. Sin embargo prueba una condición “Assert” a partir de un “Nested Loops” donde verifica que no existan registros en la tabla hija “productos” que violen la integridad referencial. Esta verificación se realiza mediante un “Clustered Index Scan” sobre productos que como dijimos, es en definitiva el scan sobre la tabla física. Esto significa que si por ejemplo tuviéramos 1.000.000 de productos, al borrar un registro sobre la tabla familias estaríamos haciendo un scan de 1.000.000 de registros para verificar simplemente que no se viola la integridad referencial. Esto en ocasiones provoca que sistemas que en prueba funcionan correctamente a medida que crece la información almacenada el sistema se haga cada vez más lento.

También es posible mostrar el plan de ejecución en forma grafica, presionando el botón que esta ubicado a la derecha del combo de bases de datos o mediante CTRL.+L (en la versión en inglés) o mediante la opción de menú correspondiente.

Para probar lo que pasa con una tabla que no esta referenciada por otra mediante integridad referencial podemos ejecutar el siguiente DELETE:

SET SHOWPLAN_TEXT ON 

go

DELETE Clientes where Clien_Descrip = 'Cliente 1'

go

SET SHOWPLAN_TEXT OFF

go

 lo cual nos da el siguiente resultado

StmtText               
------------------------------------------------------------ 
  |--Clustered Index Delete (OBJECT:
     ([Indices].[dbo].[Clientes].[Clientes_PK]))
       |--Top (ROWCOUNT est 0)
            |--Clustered Index Scan (OBJECT:
               ([Indices].[dbo].[Clientes].[Clientes_PK]), 
               WHERE:([Clientes].[Clien_Descrip]=[@1]) ORDERED)

Aquí vemos como el plan de ejecución se simplifica y solo accede a la tabla a borrar.

Es importante notar que el comportamiento debido a estas instrucciones DELETE es similar al que ocurriría si se actualiza alguno de los campos de la clave primaria de la tabla foránea mediante UPDATE ya que debe verificar que no viola la integridad referencial con sus hijos.

Luego terminamos de crear las relaciones así el modelo nos queda completo.

alter table Clientes

      add constraint Ingresos_Clientes_FK1 foreign key (

            Ingreso_Codi)

       references Ingresos (

            Ingre_Codi)  

go

alter table Items

      add constraint Facturas_Items_FK1 foreign key (

            Factu_Nume)

       references Facturas (

            Factu_Nume)  

go

alter table Items

      add constraint Productos_Items_FK1 foreign key (

            Produc_Codi)

       references Productos (

            Produc_Codi)  

go

alter table Facturas

      add constraint Clientes_Facturas_FK1 foreign key (

            Clien_Codi)

       references Clientes (

            Clien_Codi)  

go

La solución

Evidentemente la solución a este problema se encuentra en acotar la cantidad de registros que el motor revisa en las tablas hijas en el momento de modificar el valor de la clave primaria de la tabla foránea o de eliminar un registro de la tabla foránea. Para ello se puede utilizar un índice en cada tabla hija tal como se muestra a continuación.

create index Productos_FK_Familias 

on productos

(

      Produc_Fami

)

Si analizamos nuevamente la tabla sysindexes vemos como ahora aparecen además de los índices clustered este nuevo índice con indid 2 indicando que no es clustered.

id          indid  rowcnt               name     

----------- ------ -------------------- --------------------- 

1977058079  1      0                    Ingresos_PK

1993058136  1      0                    Familias_PK

2009058193  1      0                    Productos_PK

2009058193  2      0                    Productos_FK_Familias

2025058250  1      0                    Clientes_PK

2041058307  1      0                    Items_PK

2057058364  1      0                    Facturas_PK

Si ahora ejecutamos nuevamente el DELETE sobre la tabla familias vemos como ha cambiado el plan de ejecución: El “clustered index scan” ha cambiado por un “index seek” sobre el índice no clustered “Productos_FK_Familias”, lo cual significa por un lado que no va a la tabla para verificar si viola la integridad referencial y por otro lado que no revisa todo el índice.

Como el primer componente del índice es el campo a buscar, la búsqueda es puntual y sumamente efectiva, y es por eso que en lugar de hacer un “index scan” y revisar por completo el índice que habitualmente es más chico y efectivo que revisar la tabla completa, hace un “index seek” y accede puntualmente solo a los registros que requiere. Este tema lo vamos a ver en las próximas notas y esta relacionado con la cardinalidad de la información y las estadísticas disponibles, además de la existencia de estos índices...

StmtText     
----------------------------------------------------------- 
  |--Assert (WHERE:(If NOT(([Expr1007] IS NULL)) then 0 else NULL))
       |--Nested Loops (Left Semi Join, OUTER REFERENCES:
          ([Familias].[Produc_Fami]), DEFINE:([Expr1007] = [PROBE VALUE]))
            |--Clustered Index Delete (OBJECT:
               ([Indices].[dbo].[Familias].[Familias_PK]))
            |    |--Top (ROWCOUNT est 0)
            |         |--Clustered Index Scan (OBJECT:
                         ([Indices].[dbo].[Familias].[Familias_PK]), 
                         WHERE:([Familias].[Produc_Fami_Descrip]='Familia 1') ORDERED)
            |--Row Count Spool
                 |--Index Seek (OBJECT:([Indices].[dbo].[Productos].[Productos_FK_Familias]), 
                    SEEK:([Productos].[Produc_Fami]=[Familias].[Produc_Fami]) 
                    ORDERED FORWARD)

Conclusión

Si no tomamos conciencia de cuales son las operaciones que realiza el SQL Server internamente o no usamos las herramientas que nos provee para simplificar la creación de índices tal como veremos luego, una instrucción insignificante puede provocar una degradación de performance importante.