Mantenimiento de
índices – Identificar Fragmentación
Los índices como ya sabemos son de vital importancia para el
motor de base de datos tanto para hacer consultas como procesos propios de
manipulación de datos, igualmente la fragmentación de estos índices es una
condición que se debe monitorear y solucionar como parte de la administración
de las bases de datos.
La primera parte del mantenimiento debe ser monitorear la
fragmentación de los índices para ellos es importante tener en cuenta los
niveles de fragmentación que se pueden presentar:
A- Fragmentación Inocua: Se denomina a aquella fragmentación
por la cual no deberíamos preocuparnos, estos casos son cuando el índice
contiene menos de 1000 páginas o cuando la tabla es muy pequeña.
B- Fragmentación Baja: Corresponde a la fragmentación que no
es superior al % 5 del índice. Para estos casos se recomienda no tomar acciones
ya que puede ser mucho más el costo que el beneficio obtenido.
C- Fragmentación Media: es la fragmentación > % 5 y <
= al %30. Se recomienda hacer un Reorganize de los índices.
D- Fragmentación Alta: es la fragmentación > al % 30. Se
recomienda hacer un Rebuild de los índices.
Identificar el
porcentaje de fragmentación de los índices
Para identificar la fragmentación de los índices el sistema
ya cuenta con una vista prediseñada para esto sys.dm_db_index_physical_stats.
Esta vista devuelve la información de tamaño y fragmentación de los datos y los
índices de una tabla o vista especificada.
Para utilizar esta vista se deben proveer una serie de
parámetros que corresponden a:
-
database_id: Identificador de la base de datos
(NULL, 0 ó DEFAULT)
-
object_id: Es el identificador de objeto de la
tabla o vista donde está activado el índice (NULL, 0 ó DEFAULT)
-
index_id: Es el identificador del índice. (NULL, 0, -1 ó DEFAULT)
-
partition_number: Es el número de partición en
el objeto. (NULL, 0 ó DEFAULT)
-
Mode: Es el nombre del modo. (DEFAULT, NULL, LIMITED, SAMPLED o DETAILED)
Los valores entre paréntesis son los que se
pueden usar por defecto
También utilizaremos sys.indexes que es una vista que
contiene la información de los índices y las tablas o vistas que los contienen
y la vista sys.objects que contiene la información de los objetos de la base de
datos.
El script es el siguiente:
select
CURRENT_TIMESTAMP as
Fecha, DB_NAME(db_id()) as DatabaseName, @@servername Servidor, b.name as IndexName, obj.name as ObjectName,
a.avg_fragmentation_in_percent
as '%Frag', a.page_count as NumeroPaginas , a.fragment_count as PromPagFrag,a.index_type_desc as TipoIndice,
a.avg_fragment_size_in_pages, a.partition_number
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'limited') as a
INNER
JOIN sys.indexes as b
ON
a.object_id = b.object_id AND a.index_id = b.index_id
INNER
JOIN sys.objects as Obj
ON a.object_id = Obj.object_id
El resultado de este script es algo así:
Como podemos ver el script nos dirá el porcentaje de fragmentación
de cada índice de esta base, mostrándonos su nombre y en que tabla o vista esta
creado, pero para poder capturar toda la información de todas las bases de
datos de una instancia tendríamos que ejecutar el script en cada base de datos
lo cual puede ser un poco tedioso por tal motivo sería mucho más sencillo
implementar el procedimiento almacenado SP_MSFOREACHDB.
SP_MSFOREACHDB es un procedimiento almacenado de SQL Server
que nos permite iterar con cada base de datos de una instancia para este caso
en especial es muy oportuno, y para poder ver la información toda junta la
introduciremos en una tabla temporal.
Primero crearemos la tabla temporal:
CREATE
TABLE #INDEX(
[fecha] [datetime] NULL,
[dbname] [varchar](200) NULL,
[SRV] [varchar](200) NULL,
[INDICE] [varchar](500) NULL,
[TABLA] [varchar](200) NULL,
[FRAGM] [decimal](20, 5) NULL,
[numeropaginas] [decimal](20, 5) NULL,
[PROMPAGFRAG] [decimal](20, 4) NULL,
[tipoindice] [varchar](200) NULL,
[fragsizepages] [decimal](20, 5) NULL,
[PARTS] [int] NULL
)
Los tipos de datos deben coincidir con los que arroja la
consulta que planteamos antes
Posterior a crear la tabla temporal declararemos una
variable que aloje el script que queremos ejecutar en todas las bases de datos
DECLARE @QUERY NVARCHAR(MAX)
Ahora le asignaremos a la variable el script que va a
ejecutarse pero antes añadimos al script la sentencia ‘use ?’ teniendo en cuenta que ‘?’ funciona como un
marcador para el nombre de la base de datos.
SET
@QUERY = 'USE ? ;
select
CURRENT_TIMESTAMP as hora, DB_NAME(db_id()) as DatabaseName, @@servername
servidor, b.name as IndexName, obj.name as ObjectName,
a.avg_fragmentation_in_percent, a.page_count,
a.fragment_count,a.index_type_desc, a.avg_fragment_size_in_pages,
a.partition_number
FROM sys.dm_db_index_physical_stats(db_id(), NULL,
NULL, NULL, "limited") AS a
INNER
JOIN sys.indexes AS b
ON
a.object_id = b.object_id AND a.index_id = b.index_id
INNER
JOIN sys.objects as Obj
on a.object_id = Obj.object_id'
Finalmente y una vez asignado el script utilizaremos el SP_MSFOREACHDB
para que se ejecute en cada base de datos e inserte la información en la tabla
temporal
INSERT
INTO #INDEX
EXEC
SP_MSFOREACHDB @QUERY
La ejecución nos dará como resultado algo así
Y si hacemos un select a la tabla temporal veremos
Para que sea más evidente haremos un select count distinct
que nos permita ver cuantas bases de datos hay en la tabla
De esta manera y con este script podríamos hacer un
monitoreo a la fragmentación de nuestros índices creando un Job que ejecute
este script cada semana.
