COMO HACER UNA TABLA FAGMENTADA
FRAGMENTACIÓN
DE ÍNDICES EN TABLAS SQL SERVER
La fragmentación
ocurre cuando los índices tienen páginas que se encuentran ordenadas de forma
lógica, y basándose en la llave estos no coinciden con el orden físico dentro
del archivo de datos.
Los
índices son objetos de la bases de datos, cuya función es optimizar el acceso a
datos. A medida que las tablas se van haciendo más grandes y se desea hacer
consultas sobre estas tablas, los índices son indispensables
Primero
vamos por partes que es un índice indices clustered & non-clustered
En
un índice non-clustered, la clave por la que buscamos tiene un puntero a la
página de datos donde se encuentra el registro. Mientras que en índice
clustered, la leaf level es la pagina de datos!. Con lo cual, el SQL Server, se
ahorra hacer un salto para leer los datos del registro (Bookmark lookup)
Parametros
configurables para la fragmentacion
Fillfactor:
nos permite añadir un espacio libre en las hojas de los índices, de esta forma,
si tenemos muchos updates, podemos evitar o minimizar el temido
particionamiento de las hojas de los índices (splits)
Padindex:
es similar a fillfactor, pero añade dicho espacio libre a todas las hojas del
índice (no sólo a la raiz del índice, sino a todo el árbol del índice).
¿Como es que se genera la fragmentación?
La fragmentación de un índice se genera cuando los datos
de una tabla son modificados y estas modificaciones afectan una página de
índice. Es decir, cuando una aplicación, inserta, borra o actualiza información
de una tabla, el índices clustered y los nonclustered también son modificados.
Cuando se lleva a cabo una operación de borrado, se
libera espacio en las páginas de índice, lo que causa que solo cierta parte de
la página de índice se encuentre ocupada, a diferencia de lo que normalmente
contiene cuando no existe fragmentación, a esta condición se le conoce como fragmentación
interna.
Cuando existe fragmentación interna, las páginas de
índices no utilizan el espacio en disco de una manera eficiente y se
incrementan el número de páginas, es decir ocupamos más páginas de índices de
las necesarias, causando que SQL Server tenga que leer más páginas para
satisfacer un query. Ahora, cuando estas páginas son leídas del disco para
pasarlos a la memoria, se le conoce como paging y debido a que el acceso a los
discos es lo más lento de un sistema, cualquier tipo de acción que hagamos para
evitar acceder al disco se va a ver reflejado en el rendimiento, es decir entre
menos paging llevemos acabo, mejor va a ser el rendimiento de nuestro SQL
Server.
Las inserciones y borrados de las tablas, provoca que se
generen más páginas de índices. Cuando llevamos a cabo un insert y la página no
puede almacenar ese registro de índices se genera un page split, en este caso
es cuando se crea una nueva página conservando orden lógico de las llaves de
índices pero no el orden físico. Generalmente esta nueva página no se crea
junto a la original, sino en otro lado del disco y cuando estas páginas no se
encuentras físicamente ordenadas se le conoce como fragmentación externa.
Cuando tenemos fragmentación externa se incrementa el número de IO's al disco
al igual que lecturas lógicas.
Algunas veces es aceptable tener cierto grado
fragmentación interna en sistemas altamente transaccionales, para evitar los
page splits, pero la fragmentación externa siempre debe ser evitada, pues
también evita que SQL Server lleve a cabo lecturas adelantadas.
¿Como podemos ver la fragmentación?
A diferencia de SQL Server 2000 y versiones anteriores,
donde ejecutábamos un DBCC SHOWCONTIG, SQL Server 2005 provee la función sys.dm_db_index_physical_stats
para poder ver la fragmentación mediante un simple select. Las columnas a
revisar son avg_fragmentation_in_percent y avg_page_space_used_in_percent de
donde podemos ver el grado de fragmentación de los índices.
Con el siguiente query podemos ver la fragmentación de
todas las tablas de la base de datos AdventureWorks
Debe ser ejecutado desde la base de datos que se quiere
revisar.
SELECT
OBJECT_NAME(dt.object_id),si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'), NULL, NULL, NULL,
'DETAILED')
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de
tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
Debemos de observar el valor avg_fragmentation_in_percent
para determinar si el índice contiene fragmentación externa, cuando este es
mayor a 10 debemos considerar desfragmentar el índice. En la columna
avg_page_space_used_in_percent podemos ver la fragmentación interna y esta se
presenta cuando el valor es menor a 75.
Fragmentación
Valor
Interna
< 75
Externa
> 10
Si determinamos que nuestra base de datos esta
presentando fragmentación, ya sea interna o externa, debemos de ejecutar ALTER
INDEX...REORGANIZE o ALTER INDEX...REBUILD para eliminarla. Debemos recordar
que cada sistema es diferente y este valor es un aproximado, pero lo podemos
tomar como base y realizar pruebas para tomar un valor más adecuado.
¿Como manejar la fragmentación?
ALTER INDEX...REORGANIZE
Esta operación nos va a desfragmentar el leaf level de un índice clustered y
nonclustered en tablas y vistas ordenando físicamente las páginas leaf-level
del índice para hacer match con las lógicas. En nivel de llenado de las
páginas, va a depender del fill factor definido. Para poder ver el valor de
fill factor en nuestras tablas podermos ver la vista sys.indexes.
ALTER INDEX Indice1 on tabla1
REORGANIZE;
ALTER INDEX...REBUILD
Esta operación va a eliminar la fragmentación interna y externa mediante la
eliminación y recreación del índice. Este Proceso remueve la fragmentación
externa ordenando los registros del índice en páginas continuas y remueve la
fragmentación interna eliminando páginas al reacomodar los registros en ella,
donde el llenado de las páginas va a depender del fill factor.
ALTER INDEX Indice1 on tabla1
REBUILD;
Opción ALL
Si especificamos la opción ALL en la operación ALTER
INDEX...REBUILD, le estamos diciendo que todos los índices de la tabla van a
ser eliminados y regenerados en una sola transacción.
Otra de las cosas nuevas de la versión 2005 es que
podemos regenerar un índice sin bloquear las tablas y los índices mediante la
opción ONLINE, Y mejor aun, podemos ejecutar operaciones sobre los
índices en una misma tabla al mismo tiempo, siempre y cuando se realicen las
siguientes operaciones:
Crear múltiples índices nonclustered
Reorganizar diferentes índices en la misma tabla
¿Como saber que tipo de sentencia debo ejecutar?
Cuando los índices no están demasiado fragmentados
podemos reorganizar(REORGANIZE) los índices ya que usa menos recursos y corre
automáticamente en línea, para índices muy fragmentados necesitamos
regenerarlos(REBUILD).
También podemos ejecutar el query sobre
sys.dm_db_index_physical_stats y utilizar los siguientes parámetros.
avg_fragmentation_in_percent
avg_page_space_used_in_percent
ALTER INDEX...REORGANIZE
> 10 y <15
60
ALTER INDEX...REBUILD
> 15
< 60
QUÉ
HACER CUANDO DESCUBRIMOS NUESTRO PORCENTAJE DE FRAGMENTACIÓN
Entre
5% y 30% ALTER INDEX REORGANIZE
Reconstrucción
del índice (Rebuild): Este proceso elimina y crea nuevamente el índice,
remueve la fragmentación y recupera espacio en disco compactando las páginas
basándose en la configuración del fill factor el cual menciono mas arrbia en la
imange o en el parámetro de la instrucción.
Mayor
al 30% ALTER INDEX REBUILD
Reorganización del índice (Reorganize): Este proceso requiere menos
recursos del sistema y realiza la desfragmentación al nivel de la hoja de la
página, reorganizando a nivel físico las hojas para que coincidan con el orden
lógico de las mismas, la reorganización también compacta las páginas de los
índices, esta se da basándose en la configuración del fill factor proceso
explicado previamente.
Consulta
para determinar el porcentaje de fragmentación (En toda la base de datos)
WITH INDICES (BD, INDICETIPO, FRAGMENTACION, INDICE, TABLA) AS (
SELECT DBS.NAME
BASEDEDATOS, PS.INDEX_TYPE_DESC,PS.AVG_FRAGMENTATION_IN_PERCENT,
IND.NAME
INDICE, TAB.NAME TABLA
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) PS
INNER JOIN SYS.DATABASES DBS
ON PS.DATABASE_ID = DBS.DATABASE_ID
INNER JOIN SYS.INDEXES IND
ON PS.OBJECT_ID = IND.OBJECT_ID
INNER JOIN SYS.TABLES TAB
ON TAB.OBJECT_ID = IND.OBJECT_ID
WHERE IND.NAME IS NOT NULL AND PS.INDEX_ID = IND.INDEX_ID
AND PS.AVG_FRAGMENTATION_IN_PERCENT > 0)
SELECT DISTINCT
CASE
WHEN FRAGMENTACION > 5 AND FRAGMENTACION <= 30 THEN 'ALTER
INDEX ' +INDICE + ' ON ' + TABLA + '
REORGANIZE'
WHEN FRAGMENTACION > 30 THEN 'ALTER
INDEX ' + INDICE + ' ON ' + TABLA + '
REBUILD'
END QUERY, FRAGMENTACION, BD, INDICE, TABLA
FROM (SELECT FRAGMENTACION, INDICE, TABLA, BD FROM INDICES
WHERE FRAGMENTACION > 5) A
ORDER BY FRAGMENTACION DESC
Los
índices pueden ser reconstruidos en línea o fuera de línea, la reorganización
siempre se da en línea, para mantener niveles de disponibilidad similares a la
de los índices reorganizados, la reconstrucción debe darse en línea y mediante
la instrucción.
ALTER
INDEX REBUILD WITH (ONLINE = ON).
Conclusiones
¿Como es que se genera la fragmentación?
La fragmentación de un índice se genera cuando los datos
de una tabla son modificados y estas modificaciones afectan una página de
índice. Es decir, cuando una aplicación, inserta, borra o actualiza información
de una tabla, el índices clustered y los nonclustered también son modificados.
Cuando se lleva a cabo una operación de borrado, se
libera espacio en las páginas de índice, lo que causa que solo cierta parte de
la página de índice se encuentre ocupada, a diferencia de lo que normalmente
contiene cuando no existe fragmentación, a esta condición se le conoce como fragmentación
interna.
Cuando existe fragmentación interna, las páginas de
índices no utilizan el espacio en disco de una manera eficiente y se
incrementan el número de páginas, es decir ocupamos más páginas de índices de
las necesarias, causando que SQL Server tenga que leer más páginas para
satisfacer un query. Ahora, cuando estas páginas son leídas del disco para
pasarlos a la memoria, se le conoce como paging y debido a que el acceso a los
discos es lo más lento de un sistema, cualquier tipo de acción que hagamos para
evitar acceder al disco se va a ver reflejado en el rendimiento, es decir entre
menos paging llevemos acabo, mejor va a ser el rendimiento de nuestro SQL
Server.
Las inserciones y borrados de las tablas, provoca que se
generen más páginas de índices. Cuando llevamos a cabo un insert y la página no
puede almacenar ese registro de índices se genera un page split, en este caso
es cuando se crea una nueva página conservando orden lógico de las llaves de
índices pero no el orden físico. Generalmente esta nueva página no se crea
junto a la original, sino en otro lado del disco y cuando estas páginas no se
encuentras físicamente ordenadas se le conoce como fragmentación externa.
Cuando tenemos fragmentación externa se incrementa el número de IO's al disco
al igual que lecturas lógicas.
Algunas veces es aceptable tener cierto grado
fragmentación interna en sistemas altamente transaccionales, para evitar los
page splits, pero la fragmentación externa siempre debe ser evitada, pues
también evita que SQL Server lleve a cabo lecturas adelantadas.
¿Como podemos ver la fragmentación?
A diferencia de SQL Server 2000 y versiones anteriores,
donde ejecutábamos un DBCC SHOWCONTIG, SQL Server 2005 provee la función sys.dm_db_index_physical_stats
para poder ver la fragmentación mediante un simple select. Las columnas a
revisar son avg_fragmentation_in_percent y avg_page_space_used_in_percent de
donde podemos ver el grado de fragmentación de los índices.
Con el siguiente query podemos ver la fragmentación de
todas las tablas de la base de datos AdventureWorks
Debe ser ejecutado desde la base de datos que se quiere
revisar.
SELECT
OBJECT_NAME(dt.object_id),si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id,
FROM
(SELECT object_id, index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'), NULL, NULL, NULL,
'DETAILED')
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
Debemos de observar el valor avg_fragmentation_in_percent
para determinar si el índice contiene fragmentación externa, cuando este es
mayor a 10 debemos considerar desfragmentar el índice. En la columna
avg_page_space_used_in_percent podemos ver la fragmentación interna y esta se
presenta cuando el valor es menor a 75.
Fragmentación
|
Valor
|
Interna
|
< 75
|
Externa
|
> 10
|
Si determinamos que nuestra base de datos esta
presentando fragmentación, ya sea interna o externa, debemos de ejecutar ALTER
INDEX...REORGANIZE o ALTER INDEX...REBUILD para eliminarla. Debemos recordar
que cada sistema es diferente y este valor es un aproximado, pero lo podemos
tomar como base y realizar pruebas para tomar un valor más adecuado.
¿Como manejar la fragmentación?
ALTER INDEX...REORGANIZE
Esta operación nos va a desfragmentar el leaf level de un índice clustered y nonclustered en tablas y vistas ordenando físicamente las páginas leaf-level del índice para hacer match con las lógicas. En nivel de llenado de las páginas, va a depender del fill factor definido. Para poder ver el valor de fill factor en nuestras tablas podermos ver la vista sys.indexes.
Esta operación nos va a desfragmentar el leaf level de un índice clustered y nonclustered en tablas y vistas ordenando físicamente las páginas leaf-level del índice para hacer match con las lógicas. En nivel de llenado de las páginas, va a depender del fill factor definido. Para poder ver el valor de fill factor en nuestras tablas podermos ver la vista sys.indexes.
ALTER INDEX Indice1 on tabla1
REORGANIZE;
REORGANIZE;
ALTER INDEX...REBUILD
Esta operación va a eliminar la fragmentación interna y externa mediante la eliminación y recreación del índice. Este Proceso remueve la fragmentación externa ordenando los registros del índice en páginas continuas y remueve la fragmentación interna eliminando páginas al reacomodar los registros en ella, donde el llenado de las páginas va a depender del fill factor.
Esta operación va a eliminar la fragmentación interna y externa mediante la eliminación y recreación del índice. Este Proceso remueve la fragmentación externa ordenando los registros del índice en páginas continuas y remueve la fragmentación interna eliminando páginas al reacomodar los registros en ella, donde el llenado de las páginas va a depender del fill factor.
ALTER INDEX Indice1 on tabla1
REBUILD;
REBUILD;
Opción ALL
Si especificamos la opción ALL en la operación ALTER
INDEX...REBUILD, le estamos diciendo que todos los índices de la tabla van a
ser eliminados y regenerados en una sola transacción.
Otra de las cosas nuevas de la versión 2005 es que
podemos regenerar un índice sin bloquear las tablas y los índices mediante la
opción ONLINE, Y mejor aun, podemos ejecutar operaciones sobre los
índices en una misma tabla al mismo tiempo, siempre y cuando se realicen las
siguientes operaciones:
Crear múltiples índices nonclustered
Reorganizar diferentes índices en la misma tabla
¿Como saber que tipo de sentencia debo ejecutar?
Cuando los índices no están demasiado fragmentados
podemos reorganizar(REORGANIZE) los índices ya que usa menos recursos y corre
automáticamente en línea, para índices muy fragmentados necesitamos
regenerarlos(REBUILD).
También podemos ejecutar el query sobre
sys.dm_db_index_physical_stats y utilizar los siguientes parámetros.
avg_fragmentation_in_percent
|
avg_page_space_used_in_percent
|
|
ALTER INDEX...REORGANIZE
|
> 10 y <15
|
60
|
ALTER INDEX...REBUILD
|
> 15
|
< 60
|
- Los índices se fragmentan durante las operaciones de INSERT, DELETE y UPDATE y van degradando el performance.
- La fragmentación interna ocurre cuando las páginas de índice no están llenas a su máxima capacidad indicada por el fill factor.
- La fragmentación externa ocurre cuando el orden físico de las páginas de índices no son iguales al orden lógico.
- Los niveles de fragmentación pueden revisarse mediante un select a la función sys.dm_db_index_physical_stats.
- La fragmentación se puede corregir ejecutando ALTER INDEX...REORGANIZE o ALTER INDEX...REBUILD.
Desde mi punto de vista la fragmentación de tablas en una Base de Datos es muy importante porque es aqui en donde se pueden controlar hasta cierto punto los datos
almacenados localmente, en un sistema centralizado, el administrador de base de
datos de la localidad central controla la base de datos,en un sistema
distribuido existe un administrador global de la base de datos que se encarga
de todo el sistema
