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://blogs.solidq.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

 

Eladio Rincón