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

 

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