Con cierta frecuencia surgen preguntas sobre cuando es el momento ideal de la creación de índices; para empezar, sabes que un índice se crea para usarse, es decir, para fomentar la resolución eficiente de consultas: algunos ejemplos, pueden ser operaciones de agregados, consultas con predicados selectivos, o ayudar a la resolución de cruces (JOIN) entre tablas. No hay ningún problema con todo esto, está asumido, pero la pregunta quizás es:

  • ¿cuando debo crear el índice? si sabes cuando no se necesita el índice, ¿por qué razón lo vas a crear? si de entrada sabes cuando se usará, deberías medir qué tienes que pagar durante el tiempo que está/no-está el índice.
  • ¿me interesaría borrar el índice en ciertas ocasiones? es posible que tengas en tu sistema operaciones masivas y quizás tengas que hacer mediciones que te conviene más: tener el índice durante toda la operación, o borrar el índice, hacer la operación masiva, y crear el índice al final.
  • ¿me interesa mantener el índice siempre? o dicho de otra forma, me conviene tener el índice durante todo el mes, si se por anticipado que ese índice sólo se usa durante los cierres de mes.

Vamos a ver uno de estos casos, en posteriores artículos veremos evolución de estos conceptos

Caso de uso: tenemos una tabla sobre la que debemos insertar periódicamente mucha información; durante el proceso de las cargas, sabes que no habrá usuarios accediendo a los datos: básicamente es un proceso que agendas con cierta frecuencia. Además, la tabla en cuestión tiene índice clustered y además algunos índices non-clustered, y debes evaluar si conviene borrar los índices durante la inserción periódica o decides mantenerlos.

Antes de seguir adelante, “pensemos un poco en índices” para este proceso (recuerda que los índices también son importantes en Sharepoint —  de hecho hay seminarios específicos para ello 🙂 ).

Primero, cuando se insertan XX filas en una tabla, primero se insertan las filas en la tabla (si es clustered – ordenados en función de la clave clustered, y si no tiene indice clustered amontonados).

Una vez hecho eso, si la tabla tiene otros índices (nonclustered, XML, etc.) SQL Server, obtendrá los datos necesarios para crear las otras estructuras; mira el siguiente plan de ejecución:

En color Rojo, verás la inserción sobre el índice clustered (donde se almacenan las filas); una vez procesado, el siguiente paso es el operador “Table Spool”, que en este caso, crea una estructura temporal con los datos que necesia para crear el siguiente índice: básicamente, se recorre la estructura en memoria de los datos recién insertados, y se queda con las columnas necesarias, para el primer índice non-clustered (nci_name) se quedará con las columnas id (clave clustered) y name; para el de abajo, necesitará las columnas id (clave clustered) y number (esta información la puedes ver poniendo el cursos sobre el propio operador:

Una vez creada la estructura temporal, SQL Server necesita ordenar es estructura para poder insertar de forma ordenada la información en el índice non-clustered; como estarás imaginando, el operador “sort” ordena la información para el primer caso por name, y para el segundo caso por number. De nuevo, lo puedes ver poniendo el cursor sobre el operador “sort”:

Finalmente queda el operador de inserción (en color naranja dos imágenes antes), que realiza la inserción sobre el índice definido.

Además, nota que la operación sobre cada índice tiene costes diferentes; la razón de básicamente que las columnas del índice seas más o menos grandes: en el caso visto, la columna name es nvarchar(35) frente a la columna number que es integer.

 

Fíjate que tras insertar en el índice non-clustered, aparece otro operador que se llama “sequence” que básicamente indica que se hacen las operaciones una tras otra. ¿qué quiere decir? que SQL Server procesará los índices uno por uno, no todos a la vez, como podrías imagina en estos mundos multi-core. De hecho, recientemente Rubén estuvo en un cliente que necesitaba insertar muchísimas filas en una tabla, y el rendimiento (al margen de la configuración de la cabina – dichosas SAN!!) la mayor parte del proceso consistía en rellenar los 16 índices que tenía la tabla. Obviamente, si para este tipo de operaciones no tienes el almacenamiento bien afinado, en operaciones eminentemente dependientes de IO, poco más puedes hacer.

De aquí supongo que ya estás sacando una conclusión útil:

  • si SQL Server necesita insertar XX filas, primero insertará las filas en la estructura base, y después creará la estructura para cada índice non-clustered que tenga. Esta inserción la hará en grupo, y no uno por uno; es decir, la operación no generará fragmentación lógica (ojo que no estoy diciendo física) si son rangos (por ejemplo, nuevas filas al final o al princiipo de la tabla) y generará fragmentación (a medir) si inserta entre medias de datos ya existentes (también a medir).

 

Vamos a reproducir un ejemplo y veamos cual es su comportamiento; empezaremos por un caso sencillo: tenemos una tabla con índice clustered y 2 índices nonclustered. La base de datos está en modo de recuperación sencillo; la tabla inicialmente está vacía, y el proceso se encarga de insertar 1.000.000 filas. Necesitamos saber si nos interesa:

  1. crear el esquema de la tabla con sus índices e insertar las filas.
  2. crear el esquema básico de la tabla (tabla e índice clustered), insertar las filas, y después crear los índices non-clustered.

El script que vamos a usar lo veis a continuación; muy sencillo: reproducen los dos pasos anteriores:

set statistics io on 
go
use AW go checkpoint 
go 
dbcc dropcleanbuffers 
go 
waitfor delay '00:00:03' 
go  
-- -- tabla con índices creados -- 
drop table t1; 
create table t1 (id int identity, name nvarchar(35), number int, type nchar(3) , c char(100) default('a'), constraint pk primary key (id)) 
go 
create nonclustered index nci_name on t1 (name) ON Indices; 
create nonclustered index nci_number on t1 (number) ON Indices; 
go  
declare @d datetime set @d=GETDATE() insert t1 (name, number, type) select top 1000000 t1.name, t1.number, t1.type   
	from master.dbo.spt_values t1 cross join master.dbo.spt_values t2 select DATEDIFF(second,@d,getdate()) diff  
go  
waitfor delay '00:00:03' 
go 
checkpoint 
go 
dbcc dropcleanbuffers 
go  
waitfor delay '00:00:03' 
go  
-- -- primero insertar en tabla y luego crear índices -- 
drop table t1; 
create table t1 (id int identity, name nvarchar(35), number int, type nchar(3) , c char(100) default('a'), constraint pk primary key (id)) 
go  
declare @d datetime set @d=GETDATE() insert t1 (name, number, type) select top 1000000 t1.name, t1.number, t1.type   
	from master.dbo.spt_values t1 cross join master.dbo.spt_values t2  create nonclustered index nci_name on t1 (name) ON Indices; 
create nonclustered index nci_number on t1 (number) ON Indices; 
select DATEDIFF(second,@d,getdate()) diff  

El tiempo invertido en ambos procesos es de 30 segundos para el primer caso, y 21 segundos para el segundo de ellos; parece que la segunda aproximación ha sido más eficiente que la primera. Veamos un poco monitor de rendimiento; he creado una traza de monitor de rendimiento con los siguientes contadores:

En mi entorno de desarrollo tengo las siguientes unidades:

  • C: unidad donde están la mayoría de las bases de datos y tempdb.
  • E: unidad donde está el fichero de transacciones.
  • F: unidad donde está el filegroup de Indices.
  • G: unidad donde está el fichero de la base de datos destino de la inserción.

Además, estoy recogiendo los contadores:

  • Page Splits/sec: rupturas de páginas/sec,
  • Pages Allocated/sec: páginas asignadas/sec
  • Checkpoint pages/sec: páginas/sec gestionadas en proceso checkpoint

 

Veamos el gráfico obtenido:

Aclaración: intencionadamente la escala de Page Splits/Sec está en decimas, frente a la escala de Pages Allocated/sec que está en céntimas; la razón es que en la captura, casi todo el tiempo están solapadas entre sí, veremos más adelante cuando no.

Analicemos las secciones del gráfico:

  • Fíjate en los checkpoint marcados: tienen actividad sobre las unidades G: en el primer caso, y G: y F: en el segundo. Como recordarás, el proceso de checkpoint básicamente baja a ficheros de datos (nosotros tenemos datos en G: e índices en F:) páginas sucias de memoria (aquellas cuya versión en memoria es diferente a la versión en disco). que  unidad procesos
  • El patrón de las zonas marcadas como A y C es muy parecido: corresponde a la parte de inserción en el índice clustered: Page Split y Page Allocation constante con los mismos valores, y actividad en la unidad E: (fichero de transacciones) y sin noticias de ficheros de datos (G:) e índices (F:). también date cuenta que el tiempo invertido en ambos casos es similar, por lo que la diferencia en tiempo no viene por ahí
  • Comparemos la sección B con la sección D; para empezar, vemos que B tiene mayor duración de tiempo; aparte de eso, fijate que B tiene Page Splits/sec mientras que D no tiene ninguno; en la sección B; además, nota que D tiene picos altos de localización de páginas (Page Allocation/sec) mientras que B los tiene bastante más reducidos (más o menos un 25% de lo que tiene D); como punto final fíjate que mientras en B hay actividad en el fichero de transacciones (unidad E: color verde), en D no hay nada para fichero de transaccciones pero si para fichero de índice (color azul – unidad F:). La causa de este comportamiento es que la operación CREATE INDEX es mínimamente registrada cuando la base de datos está configurada con modelo de recuperación simple o bulked-logged.

 

El siguiente gráfico muestra el comportamiento cuando la base de datos está configurada en modelo de recuperación completo:

 

Centrándonos en las secciones B y D, nota que la linea roja en este caso (unidad E: – donde está el fichero de transacciones) si que tiene actividad aunque notablemente diferente.

Con estos datos diría que internamente SQL Server resuelve de forma diferente la creación del índice frente a el camino para hacer la inserción en la tabla , frente a la inserción de filas en el índice. Es una conclusión a la que he llegado yo mismo viendo los datos; me gustaría que alguien me corrija, o me ayude a interpretarlo.

Conclusiones:

  • Haz pruebas y considera si te conviene hacer operaciones grandes con índices de apoyo (non-clustered) ya creados. Compáralo con la aproximación de borrar y crearlos.
  • El modelo de recuperación (bulk-logged o simple vs completo)afecta a estas operaciones.
  • Considera el beneficio de SQL 2008 de las inserciones masivas del tipo (INSERT FROM SELECT).
  • Mide tus pruebas y aprende mientras interpretas los resultados

 

En publicaciones posteriores, trataré de cubrir otros escenarios de operaciones masivas:

  • Añadir filas a la estructura ya creada: habitual en escenarios analíticos en los que periodicamente se añaden muchas filas a la tabla.
  • Borrar de filas sobre la estructura creada: habitual en operaciones de borrado, windowing, historificación.
  • Modificaciones de filas sobre la estructura creada: no muy habitual, generalmente asociado a cambios importantes en diseño de tablas de hechos.

 

Eladio Rincón