Continuemos la saga; en el post anterior vimos que inserciones en conjuntos no provoca fragmentación en las tablas / índices;Qué vamos a probar: ahora toca probar que sucede con inserciones en pequeños grupos; en este post vamos a hacer el los grupos sean de filas de 1 en 1, pero considera que es extensible para procesos iterativos que insertan filas poco a poco.
Para el ejemplo utilizaremos el siguiente script de setup:
-- -- fragmentación durante carga de tablas en pequeños grupos -- -- Solid Quality Mentors 2010 -- http://creativecommons.org/licenses/by-sa/3.0/ -- Attribution-NonCommercial-ShareAlike 3.0 -- -- https://blogvisionarios.com/elrinconDelDBA -- http://siquelnet.com -- use Adventureworks go set nocount on go if exists (select * from sys.tables where name = 'bloqueos_base') drop table bloqueos_base go create table bloqueos_base (id int identity, d date, r int, s char(100) constraint bloqueos_base_pk primary key (id)); go if exists (select * from sys.tables where name = 'bloqueos') drop table bloqueos go create table bloqueos (id int identity, d date, r int, s char(100) constraint bloqueos_pk primary key (id)); go create nonclustered index nci_bloqueos_r on bloqueos (r) include (s); create nonclustered index nci_bloqueos_d on bloqueos (d); go insert bloqueos_base values ('20080101',1, 'aaa'), ('20081011', 2,'abb') , ('20081012',1, 'acc') ,('20081013', 2,'add') , ('20081014',3, 'baa') ,('20081015', 4,'bdd') , ('20081017',3, 'bcc') ,('20081016', 4,'bdd') , ('20081017',5, 'bcc') ,('20081016', 5,'bdd')
Si recuerdas los posts anteriores, en este la pequeña diferencia es que vamos a utilizar una tabla que llamamos “bloqueos_base” que será la tabla desde la que se inserta la información en la tabla destino.
El proceso de inserción “poco-a-poco” iterativo será el siguiente:
declare @i int=1 while @i<=100000 begin insert bloqueos select top 1 p.d, @i % 5, p.s from bloqueos_base p order by NEWID() set @i+=1 end
Es decir, se leerá de la tabla base una fila, que se insertará en la tabla destino – llamada bloqueos.
fíjate que se utiliza la variable @i para establecer la columna r, que podría considerarse como columna “variable” (en este caso va a generar valores entre 1 y 5, que serán consecutivos, es decir, 1, 2, 3, 4, 5, y así repetir la serie hasta llegar a la fila 100000.
También fíjate que la tabla tiene pre-creados dos índices: uno por la columna r (que podría considerarse un código de región en tablas de ventas) y otro por la columna d que podría ser la fecha del pedido.
Tras esto, analizamos cómo de fragmentados están los índices de la tabla con la siguiente consulta:
-- cómo está de fragmentado -- todo select si.name, index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, record_count FROM sys.dm_db_index_physical_stats ( db_id ('Adventureworks'), object_id('dbo.bloqueos'), NULL, NULL, 'detailed') v JOIN sys.tables so on so.object_id = v.object_id JOIN sys.indexes si ON v.index_id = si.index_id and so.object_id = si.object_id where index_level =0
Y como os anticipo que el proceso de inserción generará fragmentación, lo siguiente que haríamos sería reconstruir los índices de la tabla bloqueos (todos):
alter index all on bloqueos rebuild with (fillfactor = 100)
Luego, una vez reconstruido los índices, se volvería a analizar la fragmentación con la DMV anterior.
El resultado obtenido es el siguiente:
El primer resultado es la DMV tras realizar las 100K inserciones, el segundo resultado es tras defragmentar los índices; ¿qué resultados tenemos? – fíjate en las cajitas rojas que he añadido para que veáis donde están los mayores problemas:
- El proceso no ha afectado negativamente al % de relleno de las páginas de ninguno de los índices: valores cercanos al 99%.
- El proceso ha provocado mucha fragmentación de extensión en todos los índices: en el índice clustered nos quedamos con 203 fragmentos, mientras que en los índices non-clustered 1594 y 341; fíjate el mal dato que tenemos en los índices non-clustered, que el número de páginas medio por fragmento es 1!
- El proceso ha provocado mucha fragmentación interna (páginas no-consecutivas) en los índices non-clustered.
Una vez reconstruidos los índices, fíjate que se ha eliminado la fragmentación interna de los índices non-clustered y el número de fragmentos se ha reducido mucho.
La alternativa obvia es: borrar los índices antes del proceso, o deshabilitarlos porque una vez deshabilitado el índice, las operaciones de inserción, no tocarán los objetos deshabilitados; para volver a poder utilizar los índices, tendrás que reconstruir el índice deshabilitado); el código para deshabilitar los índices sería el siguiente:
alter index nci_bloqueos_r on bloqueos disable; alter index nci_bloqueos_d on bloqueos disable;
Para probarlo, inserta el código anterior justo antes del proceso de inserciones iterativas.
Vuelve a ejecutar el proceso, y comprueba los resultados; en mi máquina he obtenido los siguientes resultados:
La duración ha sido un poco menor (no muy significativa); pero fíjate que en este caso, el proceso no ha sido nada negativo para la fragmentación de extensión; como curiosidad, nótese que la reconstrucción del índice ha generado más fragmentación de extensión de la que tenía (como dijimos en el post anterior, seguramente afecte el número de filas – en nuestro caso son poca, apenas 100K).
Conclusión: hemos visto, como un proceso “iterativo” de inserción de filas si genera fragmentación en algunos de los índices que forman parte de nuestro diseño; como conclusión general, te recomendaría que al margen de lo que veas en publicaciones como esta, trates de probar tu escenario, e intenta entender cómo se comporta SQL Server 🙂 prueba, prueba y prueba, no te canses de probar.
En siguientes posts, veremos cómo afecta negativamente la fragmentación a tus índices: no lo vamos a ver desde el pto de vista de ahorro/coste de espacio porque de eso ya hay muchas referencias por ahí, sino desde el punto de vista de ejecución de consultas
