Mantenimiento Parte 4/4 Indices y estadisticas (Index and statistics)

Posiblemente el mantenimiento más importante para una base de datos en el día a día, es el mantener las estadísticas y los índices, esto nos afecta en varias maneras, la principal de todas es el desempeño que tienen nuestras consultas a la base de datos y que SQL Server pueda crear los planes de ejecución óptimos para cada consulta.

Este tema lo dividimos en dos sub temas distintos, los cuales son índices y estadísticas, para más información revisar los post anteriores, pero como dijimos un índice como su nombre lo indica son formas rápidas de ordenamiento, mientras que las estadísticas son los valores que tenemos, ambos son usados para crear los planes de ejecución, no se crea el más óptimo sino el más rápido.

¿Porque hacerlo?

Las razones son variadas pero la más simple es la ganancia de desempeño que podemos obtener al reducir los tiempos de las consultas, esto nos lleva a inserciones un poco más lentas en cargas masivas, pero en sistemas de alta transaccionalidad pero este escenario debe de ser raro, un incide bien mantenido nos permite evadir los deadlocks, cuando un recurso puede entrar en este escenario es porque la consulta toma más tiempo del esperado y se genera el bloqueo.

¿Qué hacer?

El mantenimiento tiene que ver también con los SLA que debemos de tener, pero un punto muy importante es que las versiones Enterprise cuenta con el Rebuild de incides online lo cual minimiza los tiempos de mantenimiento y evita el tener que crear ventanas de mantenimiento. Por otro lado, los mantenimientos de estadísticas e incides dependerán en gran medida de los cambios que tengamos en nuestras bases de datos y entre más seguido se realicen los tiempos que tomaran será menor hasta un cierto punto.

Las estadísticas son un punto un poco turbio por decir las bases de datos de Sharepoint no tiene auto crear estadísticas, ni deben de tener planes de mantenimiento para estadísticas o índices. Si leyeron la parte de estadísticas verán que estas se actualizan cuando hay un cambio equivalente a 500 + 20%, lo cual en tablas muy grandes (hablando en millones de registros) estas no son actualizadas hasta donde muchos cambios se han realizado y es mejor tener un control manual de ellas y no dejarlo al auto-update statistics.

Fuera de alguna situación irregular es recomendado que las estadísticas e índices esten en planes de mantenimiento.

Índices:

Rebuild: Un nuevo índice crea un nuevo índice, quitando el índice anterior, si el índice no está habilitado lo vuelve a habilitar. cuando se crea un nuevo indice se puede indicar el valor del fillfactor.
Reorganize: Esta opción es mucho menos agresiva que el Rebuild, hace una busqueda a nivel hoja y ordena las paginas físicas llenando los datos de acuerdo a nivel del fillfactor.
FillFactor: El fillfactor es el llenado de las paginas, normalmente si no hay cambio el fillfactor al 100%, pero en casos donde habrá inserciones en un orden no determinístico es posible que sea mejor el uso de un fillfactor un poco menor como un 80%.

Estadísticas:

Fullscan: Analiza todas las tablas y vistas indexadas.
Sample: Usa un % comprendido de 0 a 100%, el 100% es igual a Fullscan, no es tan optimo como Fullscan pero es mucho más rápido.

¿Como hacerlo?

Índices:

0 a 5%: No hacer nada
5 a 30%: Reorganizar
30 a 100% Rebuild

Estadísticas:

Lo recomendado es que si el tiempo es suficiente se use con fullscan mientras sea posible.

¿A que hacerlo?

Todas las tablas de usuario de cada base de datos, en especial aquellas de altos cambios (transaccionales), bases de datos de consulta, históricas o DWH que ya no cambian no es necesario el mantenimiento de ninguna de las dos, aunque se puede hacer cada cierto tiempo (meses) como mantenimiento preventivo.

Ejemplo por linea de comandos

Índices (Como lo recomienda Microsoft):

USE AdventureWorks2012; 
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ; 
GO

Rebuild todos los indices en una tabla

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

Estadisticas con Fullscan

USE AdventureWorks2012;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN
GO

Ejemplo por SSMS

Como podran observar en las siguientes imágenes el uso de SSMS fuera de los planes de mantenimiento para lo que es estadísticas e iíndices es bastante precario (por no decir nulo).

En esta imagen podemos observar como al expandir la tabla Person.Person de Adventure Works, encontraremos abajo de las columnas en sus carpetas pertinentes, los índices y las estadísticas.

Al dar un click derecho sobre un indice en las opciones podemos ver las 2 opciones principalmente descritas en este documento, rebuild y reorganize.

Esto es todo lo que podemos ver cuando queremos hacer una modificación a un índice, tanto por medio de Rebuild como de Reorganize, como ven no podemos cambiar el fill factor, lo que podemos ver es el nivel de fragmentación pero las opciones en general son nulas.

Por ultimo tenemos las estadísticas, las cuales fuera de modificarlas (cosa que no recomiendo a menos que tengan un muy alto conocimiento de desempeño), es un pequeño checkbox que nos permita hacer una actualización de las mismas.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Este script por citar un ejemplo donde se hace un update basandose en % (o sea smart) tomando cómo prioridad el Index_reorganize, index_rebuild_online, index_rebuild_offline para un rango medio de fragmentación. También apreciamos que se ara un update de las estadísticas en todas las bases y tablas solo tomando data modificada.

Nota: Una cosa que no mencione en el post anterior es que los scripts de ola pueden dejar un log, lo cual es bastante bueno en varios casos ya que podemos ver el crecimiento de una base en tiempo.

Post Relacionados

Parte 1 Introduccion
Parte 2 Backup (Copia de seguridad)
Parte 3 Revision de integridad

Más información

Reorganize and rebuild indexes

Buscar este blog

Caja de Herramientas (SQL Server)