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

Mentor & Flex Director at SolidQ
I am professionally focused on SQL Server databases. I am an MVP on SQL Server since 2003, and my area of expertise is resolution of performance and scalability issues for OLTP based systems.

My professional career revolve on SQL Server mentoring, consulting, and training projects. I believe that mixing training and mentoring projects is the best approach to help the clients to get the most from SQL Server. With other mentors of SolidQ I have developed a tuning methodology applied in most of the SQL Server consulting projects.
Eladio Rincón