Regeneración y desfragmentación de índices con DBCC y ALTER INDEX
En esta
ocasión no continuare con los comandos de consola de base de datos de Microsoft SQL
Server (Comandos
DBCC), sino que hare un espacio para hablar de los paralelismos y
diferencias del uso de los comandos de consola de base de datos relacionados
con el mantenimiento de índices y la instrucción ALTER INDEX.
Si bien, ya
se ha indicado que los Comandos DBCC
relacionados con las actividades de mantenimiento de índices, específicamente DBCC
DBREINDEX y DBCC
INDEXDEFRAG, pueden aun ser utilizados, también se ha mencionado que debe
utilizarse la instrucción ALTER INDEX para llevar a cabo las actividades
relacionadas con estas tareas, ya que en un futuro estos comandos serán quitados
y dejados de usar en Microsoft SQL Server.
Antes de
hablar de la instrucción ALTER INDEX, mencionare algunos paralelismos en los
comandos indicados y la instrucción. Tomando como referencia la base de datos AdventureWorks2014,
una base de ejemplo y demostraciones muy utilizada para la capacitación.
Dicha base de datos la he restaurado en una de las instancias y he encontrado que
sus tablas presentan diversos índices con fragmentación.
Ahora bien,
si recordamos que Microsoft SQL Server tiende a mantener los índices de forma automática,
cuando se llevan a cabo operaciones de actualización en los datos y estas
operaciones pueden hacer que los índices queden dispersos en la base de datos,
esto es, se lleve a cabo la fragmentación. Si se define la fragmentación como “La
ocurrencia de cuando los índices tienen páginas en las que la ordenación
lógica, basada en el valor de clave, no coincide con la ordenación física
dentro del archivo de datos”. Se sabe que los índices fragmentados generalmente
son la causa de la reducción en el rendimiento de las consultas, lo que
conlleva la ralentización de la aplicación.
No es mi
intención mencionar en este espacio la forma de detectar la fragmentación de
los índices, sin embargo, si mencionaré que una vez que se han encontrado los
valores del porcentaje de fragmentación de un índice en una tabla, los
criterios para determinar si es mejor reorganizar o reconstruir el índice es la
siguiente:
% de
fragmentación
|
Acción correctiva
|
Frag > 5 and frag < = 30
|
Reorganizar
o desfragmentar
|
Frag > 30
|
Reconstruir
|
Es importante
indicar que la regeneración de un índice se puede ejecutar en línea, esto es no
es necesario que la base de datos este en modo de mantenimiento, o sin conexión,
esto es, en modo de mantenimiento. En cambio, la reorganización de un índice
siempre se ejecuta en línea. Para lograr una disponibilidad similar a la opción
de reorganización, debe volver a generar los índices en línea.
Pero más allá
de las recomendaciones para la determinación que hacer, este documento intenta
mostrar las características entre el uso de los comandos de consola de base de
datos (DBCC) relacionados con el mantenimiento de índices y la instrucción ALTER
INDEX para llevar a cabo estas tareas. Primero veremos el paralelismo en el comando DBCC DBREINDEX y posteriormente DBCC INDEXDEFRAG, finalmente mencionaré algunas de las características de la instrucción ALTER INDEX.
DBCC DBREINDEX y ALTER INDEX
Se llevó a
cabo la revisión de las características de los índices de las tablas de la base
de datos mencionada y se encontró que el índice de la tabla denominada Sales.SpecialOfferProduct, tiene un porcentaje de fragmentación de 66.6 y
se trata un índice agrupado (clustered index), la tabla cuenta con 538
registros. Utilizaremos primero el comando DBCC DBREINDEX para llevar a cabo la
regeneración del índice con el objetivo de disminuir la fragmentación, de esta
forma tenemos, pero en este caso especificaremos que se utilice un factor de
llenado (fillfactor) de 80%, quedando el comando como se indica a continuación:
USE [AdventureWorks2014];GO
DBCC
DBREINDEX ([Sales.SpecialOfferProduct], PK_SpecialOfferProduct_SpecialOfferID_ProductID, 80);
El comando se
efectúa sin problemas, y se puede
comprobar que el índice se ha regenerado para quedar en un porcentaje de
fragmentación de 50 y el factor de llenado se ha establecido en 80%. Si bien en
esta ocasión, se ha mejorado el
porcentaje de fragmentación, este no se abatió mas dadas las características
de la información en la tabla, esto no es un tema que analizaremos en este
momento, solo mostraremos los datos obtenidos para llevar a cabo las comparaciones.
Si llevamos
a cabo la restauración de la base de datos, para dejarla en su forma original y
utilizamos la instrucción ALTER INDEX para generar nuevamente el índice de la
siguiente forma:USE [AdventureWorks2014];
GO
ALTER INDEX PK_SpecialOfferProduct_SpecialOfferID_ProductID
ON Sales.SpecialOfferProduct
REBUILD WITH (FILLFACTOR = 80);
Se puede fácilmente
comprobar que el índice se ha regenerado y queda con un porcentaje de fragmentación
de 50, al igual que el obtenido cuando se llevó a acción con el comando DBCC
DBREINDEX. En estos momentos podemos
decir que el uso del comando o de la instrucción permite obtener los mismos
resultados.
Ahora bien, después de revisar los demás índices
de la tabla, se ha observado que todos presentan un porcentaje de fragmentación
mayor a 5 y se ha decidido que todos los índices deben regenerarse, asimismo se
ha determinado que todos queden con un factor de llenado de 90%, primero utilizaremos
el comando DBCC DBREINDEX de la siguiente forma
USE [AdventureWorks2014];
GO
GO
DBCC DBREINDEX ( [Sales.SpecialOfferProduct],
80);
En este
caso se observa que la ejecución del comando no puede llevarse a cabo, ya que
se indica el siguiente mensaje de error:
Msg 2560,
Level 16, State 9, Line 1
Parameter 2
is incorrect for this DBCC statement.
En el
comando solo puede indicarse la recreación de todos los índices, pero no puede
indicarse el factor de llenado, ya que éste solo puede especificarse cuando se
indica el nombre del índice correspondiente, ahora bien, utilizaremos la
instrucción ALTER INDEX para ver cómo se comporta.
USE [AdventureWorks2014];GO
ALTER INDEX ALL ON Sales.SpecialOfferProduct
REBUILD WITH (FILLFACTOR = 80);
En este
caso la instrucción ALTER INDEX sí me permite indicar que se lleve a cabo la
regeneración de todos (ALL) los índices de la tabla indicada y además especificar
el factor de llenado. En este caso el uso de la instrucción ALTER INDEX tiene
ventaja sobre el uso del comando DBCC DBREINDEX.
El comando DBCC
DBREINDEX solo permite la regeneración de todos los índices, indicando la
tabla, lo cual se lleva a cabo con las características ya establecidas
previamente. De la siguiente manera:USE [AdventureWorks2014];
GO
GO
DBCC
DBREINDEX ([Sales.SpecialOfferProduct]);
La
instrucción ALTER INDEX queda de la siguiente manera:
USE [AdventureWorks2014];
GO
GO
ALTER INDEX ALL ON Sales.SpecialOfferProduct
REBUILD;
De esta
forma, ambos llevan a cabo la tarea obteniendo los mismos resultados. Asimismo
la instrucción ALTER INDEX permite indicar algunos otros argumentos entre ellos
se pueden indicar:
SORT_IN_TEMPDB = {ON | OFF }
|
Especifica si se deben
almacenar los resultados de ordenación en tempdb. El valor predeterminado es
OFF.
|
ON LINE = {ON | OFF
}
|
Especifica si las tablas
subyacentes y los índices asociados están disponibles para realizar consultas
y modificar datos durante la operación de índice. El valor predeterminado es
OFF.
|
MAXDOP = max_degree_of_parallelism
|
Invalida el grado
máximo de paralelismo opción de configuración para la duración de la
operación de índice.
|
DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE |
COLUMNSTORE_ARCHIVE
|
Especifica la opción
de compresión de datos para el índice, número de partición o intervalo de
particiones especificado.
|
USE [AdventureWorks2014];
GO
GO
ALTER INDEX PK_SpecialOfferProduct_SpecialOfferID_ProductID
ON Sales.SpecialOfferProduct
REBUILD PARTITION = 2;
Debe asegurarse
de que se trata de una tabla e índices que se encuentran particionados, ya que
en caso de que no se tenga una tabla o índice particionado, se presentara un
error. Es posible que se indique que se lleve a cabo la regeneración de los índices
de todas las particiones, para ello se indica de la siguiente manera:
USE [AdventureWorks2014];
GO
GO
ALTER INDEX ALL ON Sales.SpecialOfferProduct
REBUILD PARTITION = ALL;
Es posible
en estos casos utilizar alguno de los argumentos que se han indicado
anteriormente para poder efectuar mas opciones en la regeneración de los índices
con la instrucción ALTER INDEX que con el uso del comando DBCC DBREINDEX.
DBCC INDEXDEFRAG y ALTER INDEX
Ahora
veremos el comportamiento del comando DBCC INDEXDEFRAG contra lo que ofrece la instrucción
ALTER INDEX, para la reorganización de los índices de una tabla. Se llevó a cabo la revisión de los índices de
la base de datos que se menciona anteriormente, y se encontró que en la tabla
Sales.SalesOrderDetail que cuenta con 121,315 registros, el indice IX_SalesOrderDetail_ProductID
tiene un porcentaje de fragmentación de 5.1, justo en el límite para la
reorganización, por lo que procedemos a llevarlo a cabo con el comando,
quedando:
USE [AdventureWorks2014];
GO
DBCC
INDEXDEFRAG(0,
[Sales.SalesOrderDetail],
IX_SalesOrderDetail_ProductID);
La
ejecución de este comando proporciona la siguiente información:
Pages Scanned
|
Pages Moved
|
Pages Removed
|
268
|
266
|
0
|
(1 row(s) affected)
DBCC execution
completed. If DBCC printed error messages, contact your system administrator.
Y al
revisar el estado del índice, se observa que se ha reducido el porcentaje de
fragmentación a 4.4, que si bien, la reducción no es alta, también ofrece una
mejora que puede ser importante en el rendimiento de las consultas. Ahora bien,
utilizaremos la instrucción ALTER INDEX y veremos cuál es el resultado, para
ello restauraremos nuevamente la base de datos, ejecutando la instrucción:
USE [AdventureWorks2014];
GO
GO
ALTER INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
REORGANIZE;
En este
caso, el mensaje que se recibe es el siguiente:
Command(s) completed
successfully.
Si bien el
comando DBCC INDEXDEFRAG permite indicar que partición se desea reorganizar,
como en el siguiente caso:
USE [AdventureWorks2014];
GO
GO
DBCC
INDEXDEFRAG(0,
[Sales.SalesOrderDetail],
IX_SalesOrderDetail_ProductID, 1);
Donde se
establece que se desea que tome la partición 1, en este caso la partición 1 o única
no tiene problema, solo debe asegurarse
que la tabla contenga particiones y el índice también pertenezca a la partición
que se indique. El resultado de esto es
el mismo, se mostrará la información como se indicó anteriormente. Ahora bien,
utilizaremos la instrucción ALTER INDEX para validar su comportamiento.
USE [AdventureWorks2014];
GO
GO
ALTER INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
REORGANIZE PARTITION = 1;
En este
caso, como la tabla no está particionada, se presentara un error como el
siguiente:
Msg 7729,
Level 16, State 1, Line 1
Cannot
specify partition number in the alter index statement as the index
'IX_SalesOrderDetail_ProductID' is not partitioned.
En este
caso, el uso de ALTER INDEX es mejor ya que ha detectado que la tabla y el índice
no se encuentra particionado, a diferencia del comando DBCC INDEXDEFRAG que no
detecta y toma como valido el uso de la partición única. Otro de las características
del uso de la instrucción ALTER INDEX es el uso de parámetros adicionales, para
la reorganización de los índices. Los cuales son:
LOB_COMPACTION = {
ON | OFF }
|
Especifica que se
debe compactar todas las páginas que contienen datos de estos tipos de datos
de objetos grandes.
|
COMPRESS_ALL_ROW_GROUPS = { ON
| OFF}
|
Proporciona una
manera para obligar a los grupos de filas delta abierto o cerrado en el COLUMNSTORE.
Disponible a partir de Microsoft SQL Server 2016.
|
Comentarios
Una de las características
del cambio entre el uso de comandos DBCC al de la instrucción ALTER INDEX es la
posibilidad de manejar opciones adicionales, por ejemplo, el uso del argumento
SORT_IN_TEMPDB en la regeneración de un indice, permite mejorar el desempeño de
la tarea ya que se toma espacio del disco donde se encuentra la base de datos
tempdb para las actividades. Otro ejemplo es el uso de la regeneración de índices
en particiones, con diferentes características, que no puede llevarse a cabo
con el comando DBCC DBREINDEX, es otra de las ventajas.
Si bien las
opciones del uso de la instrucción ALTER INDEX, no se limitan a la
reorganización o regeneración de los índices, ya que nos permite
deshabilitar, o establece diversas características
adicionales a los índices, son cosas que nos permiten determinar que es mejor
usar ALTER INDEX que los comandos DBCC relacionados con los índices.Microsoft ya ha mencionado que es recomendable el uso de ALTER INDEX sobre DBCC DBREINDEX y DBCC INDEXDEFRAG, y después de haber analizado los resultados, coincidimos en ello.
No hay comentarios.:
Publicar un comentario