Mantenimiento a Indices ,SQL Server

MANTENIMIENTO A INDICES

Hola de nuevo, en esta ocasión trataremos un tema no solo interesante, me parece que sumamente importante y al cual casi nunca le damos el lugar que se merece.

Al crear nuestros objetos de Base de datos y específicamente tablas y vistas (indexadas) hacemos uso de índices. Pero un índice que hoy funciona y que el motor de SQL utiliza al resolver una consulta no necesariamente funcionara al pasar el tiempo.

¿Qué ocurre cuando nuestras Bases de datos OLTP son usadas? Su naturaleza nos indica que miles o quizá cientos de miles de registros se agregaran a nuestras tablas, los registros también serán actualizados y eliminados, esos eventos provocaran que nuestros índices sufran de fragmentación interna , externa o ambas e irremediablemente con esto afectaran el performance de nuestras consultas , pues SQL al resolver una consulta determina cual es el mejor camino a seguir (plan de ejecución) y si nuestros índices están fragmentados no los usara y con ellos nuestras consultas sufrirán un decremento en el tiempo de respuesta.

Fragmentación interna:
La unidad mínima de almacenamiento de nuestras bases de datos son las paginas, en ellas se almacenan entre otras cosas los índices cuando la información de nuestras tablas es borrada se libera el espacio que usaba en la pagina asignada, es entonces cuando se presenta la fragmentación interna, esto quiere decir que las paginas no están siendo usadas completamente y que quizá se necesiten mas de las necesarias.

Fragmentación externa:
La fragmentación externa es más delicada, y sucede cuando SQL necesita agregar más filas y no existe suficiente espacio es las paginas para alojarlas, entonces ocurre una división de las paginas (page Split), esa división mantiene el orden lógico de las filas en nuestro índice, pero no mantiene el orden físico de la pagina, usualmente cuando ocurre una división de paginas físicamente no quedaran adjunta a la pagina original. Esto nos lleva a que cuando las paginas no están en un orden físico podemos decir que tenemos fragmentación externa.

¿En que nos podemos apoyar para determinar que índices son necesarios reconstruir?
Existe una función de SQL Server que tiene por nombre sys.dm_db_index_physical_stats la cual nos permitirá identificar la fragmentación de índices. Los argumentos que recibe son:

sys.dm_db_index_physical_stats (@IDBD, null, null,null,'DETAILED')

database_id: ID de la Base de datos, el cual recuperaremos utilizando la función del sistema db_ID
object_id: Es el identificador de la tabla o vista a analizar, si establecemos null recuperaremos información de todas las tablas
index_id: Es el identificador del índice a analizar, si establecemos null recuperaremos información de todos los índices de una tabla.
partition_number: Es el número de partición del objeto (vistas/tablas particionadas), si establecemos null recuperamos información de todas las particiones
Mode: Este parámetro nos permite especificar el nivel del examen a realizar para obtener datos estadísticos

Las columnas de nuestro interés que devuelve la función son las siguientes:
object_id int Identificador de objeto de la tabla o vista en la que se encuentra el índice.
index_id int Identificador de índice.
0 = Montón.
avg_fragmentation_in_percent float Fragmentación lógica para índices o fragmentación de extensión para montones en la unidad de asignación IN_ROW_DATA.
El valor se mide como un porcentaje y tiene en cuenta varios archivos. Para obtener definiciones de fragmentación lógica y de extensión, vea la sección Notas.
0 para unidades de asignación LOB_DATA y ROW_OVERFLOW_DATA.
NULL para montones cuando mode = SAMPLED.
avg_page_space_used_in_percent float Porcentaje medio del espacio de almacenamiento de datos disponible utilizado en todas las páginas.
En el caso de un índice, el promedio se aplica al nivel actual del árbol b en la unidad de asignación IN_ROW_DATA.
En el caso de un montón, se trata del promedio de todas las páginas de datos en la unidad de asignación IN_ROW_DATA.
En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, se trata del promedio de todas las páginas en la unidad de asignación.
NULL cuando mode = LIMITED.


Después de revisar brevemente lo que ocurre cuando se fragmenta un índice y de analizar la función sys.dm_db_index_physical_stats establezcamos los pasos para crear un plan de mantenimiento.

Se dice que un índice que está fragmentado en un porcentaje mayor al 10 % está fragmentado externamente

1. Creamos un procedimiento almacenado que determine que índices sufrieron fragmentación externa
El script correspondiente al procedimiento almacenado se puede abrir dando clic al icono, se coloca su contenido en el documento solo para referencias.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 's_ReconstruirIndices')
BEGIN
PRINT 'Dropping Procedure s_ReconstruirIndices'
DROP Procedure s_ReconstruirIndices
END
GO

PRINT 'Creating Procedure dbo.s_ReconstruirIndices'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure dbo.s_ReconstruirIndices
(
@NameBD varchar(100) /*Nombre de la Base de datos analizar*/
)
/******************************************************************************
** All rights reserved.
** File: dbo.s_ReconstruirIndices.sql
** Name: dbo.s_ReconstruirIndices
** Desc: NEVER EDIT THIS FILE.
**
** This template can be customized:
** Return values:N/A
** Called by: Puede ser invocado manualmente o por un plan de mantenimiento(JOB)
**
** Parameters: Nombre de la Base de Datos a analizar
** Input
** ----------
**
**
** Output Retorna Tres conjuntos de Filas
** .-El primer Result Set nos indica Los indices y el porcentaje de fragmentación
** que se encontro.
** .-El segundo Result Set nos indica las sentencias que se generaron para
** reconstruir indices
** .-El tercer Result set nos indica cuales sentencias no pudieron ejecutarse
**
**
** -----------
** Auth: Carlos Nabor Espinoza G
** Date: 1 Abril 2010
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- -------------------------------------------
** 07/04/2010 Carlos Espinoza Creación
**
*******************************************************************************/
as
BEGIN

Declare @IDBD int --ID de la base de datos

set @IDBD=db_ID(@NameBD); --Recuperamos el ID de la BAse de datos

/*Cursor que analiza la Base de datos que deseamos para buscar
fragmentación en los indices
Declarar nuestro cursor como Fas_Forward lo optimiza,
lo hace de solo lectura y unicamente para adelante */
Declare IndicesFragmentados Cursor Fast_forward For
Select
OBJECT_SCHEMA_NAME(dt.object_id, @IDBD) SchemaName
,Object_name(DT.Object_id) [Tabla], si.name [Indice],
dt.avg_fragmentation_in_percent [Fragmentacion Externa]
, dt.avg_page_space_used_in_percent [fragmentacion Interna]
From
(Select Object_id,index_id,avg_fragmentation_in_percent, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats (@IDBD, null, null,null,'DETAILED')
Where Index_id <>0) as DT
Inner join sys.indexes si
on si.object_id =dt.object_id and si.index_id=dt.index_id

/*Tabla que almacenara los indices que estan fragmentados (TODOS) */
Create table #IndicesFragmentadosTodos
(
SchemaName varchar(20)
,Tabla varchar(100)
,Indice varchar(100)
,fragmentacionExterna float
,FragmentacionInterna float
)

/*Tabla que almacenara unicamente las sentencias
que corresponden a indices fragmentados externamente */
Create table #IndicesFragmentadosExternamente
(
Sentencia varchar(1000)
)

/*Tabla temporal que almacena unicamente las sentencias que no
pudieron ser ejecutadas */
Create table #IndicesFragmentadosExternamenteNoEjecutados
(
[Sentencia] varchar(1000)
,[Mensaje Error] varchar(1000)
,[Numero Error] Int
,[Severidad] Int
)

Declare @Schema varchar(20) /*Nombre del esquema al que pertenece la tabla*/
,@Tabla varchar(100) /*Nombre de la tabla al que pertenece el indice*/
,@Indice varchar(100) /*Nombre del indice*/
,@fragmentacionExterna float /*Porcentaje de fragmentación externa*/
,@FragmentacionInterna float /*Porcentaje de fragmentación Interna*/
,@Sentencia Nvarchar(1000)/*Sentencia SQL que se arma para reconstruir el indice*/

--Abrimos el cursor previamente declarado
Open IndicesFragmentados
Fetch IndicesFragmentados into @Schema,@Tabla,@Indice,@fragmentacionExterna,@FragmentacionInterna
While @@Fetch_status=0
/* Este ciclo nos permitira armar las sentencias sql
para reconstruir los indices.
Asi como alimentar las tablas que nos indicaran los
indices que se encontraron fragmenteados y
las sentencias que no pudieron ser ejecutadas */
Begin
Insert into #IndicesFragmentadosTodos([SchemaName],[Tabla],[Indice],[fragmentacionExterna],[FragmentacionInterna])
values (@Schema ,@Tabla ,@Indice ,@fragmentacionExterna,@FragmentacionInterna);
If @fragmentacionExterna>=10
/*Se clasifica como indice fragmentado externamente cuando
la fragmentación en porcentaje(avg_fragmentation_in_percent) supera un 10 % */
Begin
Set @Sentencia='Alter index All'
+ ' ON ' + @Schema + '.'+ @Tabla + ' REBUILD WITH (FILLFACTOR=90, ONLINE=ON);';
Insert into #IndicesFragmentadosExternamente(Sentencia)
values (@Sentencia);
Begin try
Exec sp_executeSQL @Sentencia
End try
Begin catch
Insert into #IndicesFragmentadosExternamenteNoEjecutados
([Sentencia],[Mensaje Error],[Numero Error],[Severidad])
values (@Sentencia,ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY())
End catch
End
Fetch IndicesFragmentados into @Schema,@Tabla,@Indice,@fragmentacionExterna,@FragmentacionInterna
End
Close IndicesFragmentados
Deallocate IndicesFragmentados

-- Evidencia de indices fragmentados
Select [SchemaName],[Tabla],[Indice],[fragmentacionExterna],[FragmentacionInterna] From #IndicesFragmentadosTodos
--Sentencias generadas
Select [Sentencia] from #IndicesFragmentadosExternamente
--Sentencias no Ejecutadas
Select [Sentencia]
,[Mensaje Error]
,[Numero Error]
,[Severidad]
from #IndicesFragmentadosExternamenteNoEjecutados

END
GO


Ejemplo de salidas del procedimiento almacenado
El primer conjunto de datos
Schema Tabla Índice FragmentacionExterna FragmentacionInterna
dbo CLY_TIPOS_EXTENSION PK_CLY_TIPOS_EXTENSION 50 1.704967
dbo AM_DATOS_CENTRALES PK_AM_DATOS_CETRALES 70 98.48603

El segundo conjunto de datos
Sentencia
Alter index PK_GCPROESTRUCTURA_IDESTRUCTURA ON dbo.GC_PRO_ESTRUCTURA Rebuild;
Alter index PK_OPSERVICIOSOPERADOS_SEOCONSERV ON dbo.OP_SERVICIOS_OPERADOS Rebuild;


El tercer conjunto de datos
Sentencia Mensaje Error Numero Error Severidad
Alter index Pk_indice ON dbo.OP_DETALLES_VALIDACION Rebuild; Cannot find index 'Pk_indice'. 2727 11



El procedimiento almacenado lo podemos ejecutar manualmente o dejar programado mediante el JOB, esto nos permitira dar matenimiento a los indices de nuestras tablas, pues indice al que no se le da mantenimiento en un Base de datos altamente transaccional no servira de nada al pasar el tiempo.

Referencias

http://technet.microsoft.com/es-mx/library/ms188917.aspx
http://www.dbasupport.com.mx/index.php?option=com_content&view=article&id=133:fragmentacion-en-sql-server-2005&catid=11:administracion&Itemid=2
http://www.dbasupport.com.mx/index.php?option=com_content&view=article&id=134&catid=11:administracion&Itemid=2