Si no necesitas el índice, ¿por qué no lo borras? (II)

Como continuación al post anterior, vamos a ver qué fragmentación se provoca durante las cargas masivas. En esta ocasión, no vamos a mirar información de monitor de rendimiento, nos vamos a centrar en la información que nos proporcionan las DMVs de indexación (en este caso sys.dm_db_index_physical_stats).Qué vamos a probar: ¿provoca fragmentación dejar índices creados durante cargas masivas? SI / NO / DEPENDE; veámoslo…Primero definición de esquema de objetos:

--
-- fragmentación durante carga de tablas
--
-- 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
if exists (select * from sys.tables where name = 'bloqueos')
    drop table bloqueos
go
create table bloqueos
(id int identity, d date, s char(100)
constraint bloqueos_pk primary key (id));
go
create nonclustered index nci_bloqueos_d
on bloqueos (d);
create nonclustered index nci_bloqueos_a
on bloqueos (s);
go
insert bloqueos values
  ('20080101', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081014', 'baa') ,('20081015', 'bdd')
, ('20081017', 'bcc') ,('20081016', 'bdd')
go

Ahora insertar unas cuantas filas:

insert bloqueos
select top 100000 p.d, p.s
from bloqueos p 
cross join bloqueos n
cross join master.dbo.spt_values c
go

Tras la inserción, comprobamos cómo está de fragmentada la información en la tabla con la siguiente query:

-- 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

Tenemos como resultado lo siguiente:

¿Qué vemos?

• avg_fragmentation_in_percent: porcentage de páginas no “consecutivas”; muy bajo en general; el peor dato: 5% para el índice de fecha. Cuanto menor sea este dato, mejor,
• avg_page_space_used_in_percent: cómo de llenas están las páginas: muy alto, cercano al 100%; cuanto mayor sea el dato, menos espacio “libre” hay en las páginas.
• fragment_count: número de grupos de extensiones que están “seguidas”; es decir, extensiones consecutivas-contiguas, etc. cuanto menos fragmentos mejor. 74 fragmentos hay que ponerlos en contexto del número de páginas, en este caso 74 / 1614 = 0,04584 que indicaría el número de saltos de extensión por página; este es un buen dato;
• avg_fragment_size_in_pages: media de número de páginas seguidas en los fragmentos; básicamente, cuantas páginas contiene cada fragmento; un número alto indica que los fragmentos tienen muchas páginas, y por lo tanto páginas muy seguidas unas tras otras. cuanto mayor el dato, mejor.
• page_count: número de páginas
• record_count: número de filas

Para ver, cómo de mejor podría ser el dato, podemos reconstruir todos los índices del objeto “bloqueos” con la siguiente instrucción:

alter index all
on bloqueos rebuild
with (fillfactor = 100)

Teniendo como resultado lo siguiente:

Fíjate que el resultado es un poco confuso, seguramente asociado con el número de páginas que tiene la tabla (pocas – apenas 1500 con un total de 100K filas):

• para los índices de las columnas d y a se ha mejorado en todo (tanto fragmentación lógica como de extensión).
• para el índice clustered, ha mejorado la fragmentación lógica, ha empeorado el relleno de las páginas, y ha empeorado el número de fragmentos.

para finalizar, si ves el plan de ejecución de la sentencia de inserción, verás lo siguiente:

insert bloqueos
select top 100000 p.d, p.s
from bloqueos p 
cross join bloqueos n
cross join master.dbo.spt_values c

que como vimos en el post anterior, nos indicaba que antes de hacer la inserción sobre los índices ordenaba el conjunto de datos, para precisamente evitar la fragmentar la información 🙂

Conclusión: si tienes varios índices, antes de insertar las filas y rellenar las estructuras paralelas (índices), el motor de SQL Server se encarga de pre-ordenar las filas para poder insertarlas de forma eficiente: es decir, con la mínima fragmentación posible.

En el siguiente post veremos qué efecto tiene insertar múltiples batches.