4 comentarios

Optimización de Indices SQL SERVER


¿Que significa Reindexar?

¿Que es mejor Reindexar o Defragmentar?

¿Que diferencia existe entre Defragmentar y Reindexar?

Una de los topicos mas importantes a la hora de administrar una base de datos es implementar indices y hacerlo de manera adecuada, y despues de esto mantenerlos con el mejor rendimiento posible,  precisamente en este tema veremos las consideraciones que se tiene que tomar a la hora de realizar esta tarea, incluyendo consideraciones para tablas sin indices(heap) y tablas con indices agrupados (clustered index), de que manera comprobar la fragmentación (DBCC SHOWCONTIG y sys.dm_db:index_physical_stats),  ademas de las opciones que se tienen para corregir la fragmentacion, es decir :

DROP INDEX Y CREATE INDEX (Eliminar y crear de nuevo los indices)

DBCC REINDEX (Reconstruir el indice)

CREATE INDEX WITH DROP_EXISTING (Borrar indice actual y crear uno nuevo)

ALTER INDEX REBUILD (Modificar indices existente  reconstrullendo)

DBCC INDEXDEFRAG (desfragmentar los indices)

ALTER INDEX REORGANIZE  (Modificar indices existente  Reorganizando)

En el mundo productivo evitar el problema de la fragmentación es un factor de exito,  regularmente en ambientes de producción las bases de datos son muy grandes y tienen una actividad muy demandante que ocasionan la fragmentación del los indices y por ende afectan el rendimiento.

En que nos afecta que nuestros indices esten demasiado fragmentados?   La respuesta es tiempo,  las ejecuciones empezarian a ralentizarse por que harian entradas y salidas innecesarias, los bloqueos durarian mas tiempo, las consultas tardarian mas en regresar resultados y esto se convierte en un efecto domino donde una defecto lleva a otro y tendriamos un gran problema en las manos.

Lo primero es detectar donde esta el problema y como lo podemos solucionar, es decir vamos a detectar cuales de nuestros indices requieren de mantenimiento,  pero antes de esto vamos a entender un punto el mantenimiento de desfragmentacion y reindexado se realizara a los indices no a las tablas o vistas, que por que ???  sigue leyendo,,

  • Las tablas que no tienen indices utilizan una estructura llamada heap o monton , es decir sus datos no estan ordenados logicamente, recordemos que uno de los beneficios de implementar indices en nuestras tablas es que a partir de este nuestros datos se ordenaran con base en este y si por el contrario tenemos una tabla sin indices los datos son amontonados como van llegando sin orden podrian entrar en cualquier parta de la estructura, esto lo puedes comprobar creando una tabla sin indices e insertar datos ahora realiza una consulta inmediata e inserta nuevos datos vuelve a consultar y los datos estan en desorden total, el objetivo de la desfragmetanción es ordenar los datos y poner todos juntos pero como podria SQL realizar esto si no tiene un criterio para ordenar ????
  • Las tablas con indices agrupado o clustered index, es diferente ahora ya tenemos un criterio de ordenación entonces podras desfragmentar o reindexar sin ningun problema, y no solo eso si no que por buenas practicas siempre es recomendable tener al menos un indice clustered en cada tabla.

Bien ahora lo que sigue es detectar el problema vamos a comprobar la fragmentación, estos lo podemos realizar con el comando DBCC SHOWCONTIG

como se muestra en el siguiente ejemplo:

USE DB

GO
DBCC SHOWCONTIG(‘NOMBRE_TABLA’)

GO

si ejecutas el comando sin parametros podras obtener la fragmentación de todas las tablas de la base de datos en la que estes posicionado.

Si requieres entrar mas a fondo con este comando puedes visitar el post anterior: https://dbasqlserver.wordpress.com/2012/03/08/como-determinar-la-densidad-de-un-indice/

Por otra parte el comando DBCC SHOWCONTIG a pesar de que se puede usar en las versiones actuales 2000, 2005 y 2008 ,  este sera descontinuado en versiones posteriores, por lo que a partir de 2005 la recomendación para determinar la fragmentación de un indice es utilizar la función del sistema:  sys.dm_db_index_physical_stats

A continuacion un ejemplo de la sintaxis:

USE DB

GO
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N’TBL_HIS_FACTURACION’), NULL, NULL, ‘LIMITED’)

GO

Por ultimo una vez que hemos encontrado indices fragmentados, tenemos diferentes alternativas para poder dar el tratamiento adecuado dependiendo del caso:

  • Eliminar y volver a crear el índice agrupado de la tabla (DROP INDEX y CREATE INDEX). Si la tabla contiene un índice agrupado, esta es la alternativa más lenta, pero la que ofrece un mejor rendimiento. Al eliminar y volver a crear el índice agrupado, se volverán a reconstruir también los índices no agrupados de la tabla. El inconveniente, es que el índice estará sin conexión (no estará disponible) y la operación es atómica (si se interrumpe, será necesario empezar de nuevo desde el principio). Además, se necesita disponer de espacio suficiente para mantener una copia de los datos de la tabla.
  • Recontruir el índice (DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING o ALTER INDEX REBUILD). En el caso de los índices agrupados (clustered index) es mucho más óptimo que eliminar el índice (DROP INDEX) y volver a crearlo (CREATE INDEX), pues los índices no agrupados sólo se reconstruirán una única vez en vez de dos veces (una con el DROP INDEX y la otra con el CREATE INDEX). Para esta alternativa, se necesita utilizar el comando DBCC DBREINDEX, o el comando CREATE INDEX WITH DROP_EXISTING, o el comando ALTER INDEX REBUILD. El comando DBCC DBREINDEX desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REBUILD. El comando ALTER INDEX es nuevo en SQL Server 2005, ya que en ediciones anteriores sólo existían los comandos CREATE INDEX y DROP INDEX, pero no existía ALTER INDEX. Es una operación atómica. Hasta SQL Server 2000, implica que el índice estará sin conexión (no estará disponible). Desde SQL Server 2005, y al utilizar ALTER INDEX, se puede realizar con conexión (opción ONLINE=ON, manteniendo el acceso de los usuarios) o sin conexión. También necesita disponer de espacio suficiente. Esta operación admite paralelismo.
  • Defragmentar el índice (DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE). SQL Server 2000 introdujo el comando DBCC INDEXDEFRAG, que permite defragmentar sólo los nodos hoja de los índices y no soporta paralelismo, pero tiene la ventaja de ser una operación en línea (los usuarios pueden seguir accediendo a nuestras tablas e índices). Permite la realización de operaciones como BACKUP LOG mientras está en marcha (potencialmente beneficioso para tablas muy grandes), y además, en caso de verse interrumpido, puede continuarse sin perderse el trabajo realizado. Dependiendo de la fragmentación, DBCC INDEXDEFRAG puede ser considerablemente más rápido (o también más lento). El comando DBCC INDEXDEFRAG desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REORGANIZE.

También es importante tener en cuenta que con DBCC INDEXDEFRAG no se actualizan las estadísticas, mientras que con DBCC DBREINDEX si se actualizan estadísticas.

En cualquier caso, aunque DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING y ALTER INDEX REBUILD puedan parecer iguales, del mismo modo que DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE también puedan parecer iguales, en caso de necesidad revisar la documentación, pues sí existen pequeñas diferencias (ej: poder cambiar o no la definición del índice, etc.).

Es de suma importancia mantener el rendimiento de nuestras bases de datos y realizar la inspección de nuestros indices fragmentados es una clave importantisima,  la recomendación es realizar esto con algun trabajo programado o un plan de mantenimiento que se ejecute en horarios de poca afluencia tal vez en las noches, la periodicidad la podras determinar dependiendo del nivel de operacion en tus bases de datos, por ejemplo yo tengo un plan que ejecuta los domingos en las noches.

Espero que les halla sido de utilidad, dejen sus comentarios.

fuentes: http://www.guillesql.es/Articulos/SQLServerFAQ_Reindexar_Defragmentar_indices.aspx

Anuncios

4 comentarios el “Optimización de Indices SQL SERVER

  1. buenisimo muy buen explicado

  2. muy útil tu post, deberías considerar igual lo que mencionan en esta pagina https://msdn.microsoft.com/es-MX/library/ms189858(v=sql.120).aspx
    tienen una formula para determinar si es mejor un rebuild o un reorganize
    saludos

  3. muy interesante , necesitaba defragmentar una BD de 10 GB

Si te sirvio o te gusto el post, deja un comentario, o vota por el blog, esto nos ayuda a seguir creciendo, Gracias

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: