Con el paso del tiempo tanto los índices como los heaps se acaban fragmentando. Con este post intentaremos romper el mito existente por el cual se intenta defragmentar un heap creando un índice clustered y después borrándolo. El objetivo principal de este artículo consiste en explicar que consecuencias provoca este tipo de acciones y como afecta al rendimiento de la instancia de SQL Server.
Heaps
Un heap o montón es simplemente una tabla sin índice clustered o agrupado. La diferencia entre un Heap y un índice clustered es que el Heap no tiene ordenación de ningún tipo mientras que un índice clustered sí, concretamente por la clave que especifiquemos en el índice.Este tipo de tablas solo deben utilizarse cuando por motivos de rendimiento no merece la pena el mantenimiento de un índice clustered. Además los heaps presentan los siguientes problemas:
-
Toda consulta que se realice sobre un campo no indexado provocará el recorrido completo de la tabla (operador table scan), esto cobra especial relevancia cuando la tabla tiene millones de registros.
-
Toda inserción requiere que se produzca una búsqueda de espacio libre en las páginas. Esto además de originar más operaciones de entrada/salida puede provocar contención en memoria en las páginas GAM, SGAM y PFS cuando se producen muchas inserciones concurrentemente. El evento anteriormente descrito se conoce como FreeSpace Scan. Esto no ocurre en las tablas con índice clustered ya que se conoce a priori el lugar donde insertar los nuevos registros.
-
El último evento que se produce con este tipo de tablas es denominado Forwarded Records, consiste en que en registros de tablas con columnas de tipo varchar sin índice clustered pueden experimentar expansión cuando se inserta una cadena larga en el campo. En el caso de que con la modificación superemos el tamaño máximo del registro lo que sucederá es que SQL Server pondrá un puntero apuntando a la dirección de la página donde se encuentra el nuevo contenido de la columna. Esto provoca que cuando tengamos que leer el registro en lugar de recuperar todos los datos de la página tengamos que hacer una lectura adicional. Para tablas con grandes volúmenes de datos este evento puede suponer un incremento importante en las operaciones de entrada/salida impactando considerablemente en el rendimiento de las consultas.
Problemática de la Defragmentación en Heaps
A continuación vamos a desarrollar el problema derivado de defragmentación en heaps.
Creamos el entorno donde desarrollaremos la prueba
use AdventureWorks
set nocount on
–Creamos el entorno de pruebas
if exists (select * from sys.tables where name = ‘fragmentadaheap’)
drop table fragmentadaheap
go
create table fragmentadaheap (
id1 bigint
, id2 int
, relleno char(50) default ‘a’)
go
–creamos un índice nonclustered en el heap
create nonclustered index nci2_fragmentadaheap
on fragmentadaheap (id2)
go
–cargamos la tabla
declare @i int
set @i=1
while @i<=10000–0
begin
insert fragmentadaheap (id1, id2) select @i % 25, @i % 25
set @i=@i+1
end
go
–vemos la fragmentacion del heap
SELECT
si.name,
si.index_id,
index_level,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
fragment_count,
page_count,
record_count
FROM sys.dm_db_index_physical_stats (
db_id (‘AdventureWorks’),
object_id(‘dbo.fragmentadaheap’),
NULL, NULL, null) v
JOIN sys.indexes si
ON v.object_id = si.object_id
and v.index_id = si.index_id;
En mi caso obtengo los siguientes resultados:
Como puede apreciarse el heap tiene un 35% de fragmentación y el índice nonclustered un 93%.
Mediante DBCC IND obtenemos la página raíz del índice nonclustered.
DBCC IND(‘AdventureWorks’, ‘fragmentadaheap’, -1)
En nuestro caso la página raíz del índice es la 20637.
Con la página vemos el contenido del índice con la instrucción DBCC PAGE.
DBCC TRACEON (3604)
DBCC PAGE (5,1,20637,3)
El funcionamiento de DBCC PAGE es el siguiente:
- Id de la base de datos
- Page File ID
- Page PID
- Nivel de detalle
Este es un ejemplo de página de índice, si nos fijamos en la columna id2 (key) vemos que es la columna que hemos elegido para el índice. Además vemos que aparece una segunda columna como key que es el HEAP RID.
”create nonclustered index nci2_fragmentadaheap on fragmentadaheap (id2)”
En los heap, al no existir un índice clustered, SQL Server añade una columna llamada HEAP RID (KEY) que indica donde está la página con los datos. Para ello obviamente hay que convertir ese valor hexadecimal a un formato que indique fichero, página etc…
Ahora creamos un índice clustered, con lo cual deja de ser un heap tal y como hemos comentado anteriormente.
create clustered index ci_fragmentadaheap on fragmentadaheap (id1)
Volvemos a ver el estado de la fragmentación:
SELECT
si.name,
si.index_id,
index_level,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
fragment_count,
page_count,
record_count
FROM sys.dm_db_index_physical_stats (
db_id (‘Adventureworks’),
object_id(‘dbo.fragmentadaheap’),
NULL, NULL, null) v
JOIN sys.indexes si
ON v.object_id = si.object_id
and v.index_id = si.index_id;
go
Como se puede apreciar en este caso con la creación del índice clustered la fragmentación ha desaparecido tanto en lo que era el heap como en el índice nonclustered (6% de fragmentación).
Para esto han ocurrido 2 cosas:
-
Creación del índice clustered. La creación de un índice es una operación que consume muchos recursos, espacio en disco (como 2 veces y medía del tamaño original), cpu y memoria para la ordenación etc.
-
Reconstrucción completa del índice nonclustered, como veremos a continuación la columna Heap Rid (Key) ha desaparecido y en su lugar SQL Server la sustituye por la columna del índice clustered (id1).
Como el índice nonclustered se ha creado nuevo la página raíz ha cambiado, por lo que volvemos a buscarla con DBCC IND.
DBCC IND(‘AdventureWorks’, ‘fragmentadaheap’, 2)
El resultado es el siguiente:
Ejecutamos el comando DBCC Page para ver la página del índice.
El resultado es el esperado, la columna Heap Rid ha desaparecido y se han añadido 2 nuevas, id1 y Uniquifier. Esta columna la añade SQL Server ya que el índice clustered tiene duplicados y para deshacer los duplicados agrega un secuencial. Normalmente el índice clustered está asociado a la constraint primary key la cual evita los duplicados. La constraint primary key añade al índice la cláusula “unique”, con lo cual se evitan los duplicados y se elimina la columna UNIQUIFIER.
A continuación borramos el índice clustered, comprobamos la defragmentación del heap y del índice nonclustered, tal y como hemos comentado en los puntos anteriores.
drop index ci_fragmentadaheap on fragmentadaheap
Como vemos volvemos a tener fragmentación tanto en el heap (7%) como en el índice nonclustered, (17%). Como podemos observar la ganancia no es mucha ya que, si seguimos las buenas prácticas, se recomienda reorganizar un índice cuando la fragmentación supera el 10% y se recomienda reconstruir cuando supera el 15%. No obstante es mejor tener un 7% y un 17% que un 35% y un 95% como era el caso.
Ahora vamos a ver como ha quedado el índice nonclustered.
DBCC TRACEON (3604)
DBCC PAGE (5,1,20992,3)
Han ocurrido dos cosas:
-
Se borró el índice clustered y SQL Server tuvo que volver a recurrir a la columna Heap Rid.
-
El índice nonclustered se tuvo que reconstruir de nuevo sustituyendo las columnas del índice clustered por la de Heap Rid.
Conclusión
Para reducir la fragmentación hemos realizado las siguientes acciones:
- Crear un índice clustered.
- Reconstruir el índice nonclustered.
- Borrar el índice clustered.
- Reconstruir el índice nonclustered.
¿Que hemos obtenido?
Hemos reducido la fragmentación pero está muy cercana a los límites aconsejados por SQL Server para realizar alguna acción de mantenimiento.
¿Merece la pena?
En mi opinión no, crear, borrar y reconstruir índices en SQL Server son operaciones muy costosas a nivel de recursos para obtener tan poco premio. Pensemos que el heap tiene millones de filas (suelen utilizarse para históricos) y varios índices nonclustered, el tiempo y recursos usados serían muy importantes.
En SQL Server 2008 se puede realizar un Alter Table … Rebuild para arreglar la fragmentación pero al igual que en el caso anterior todos los índices nonclustered serán reconstruidos porque la localización de los registros en el heap cambian (columna Heap Rid, recordemos que en un heap ningún orden está garantizado).
Por último recordar que esta práctica no está recomendada por Microsoft.
