martes, 5 de marzo de 2013

SQL Server: FILL FACTOR

FILL FACTOR (factor de relleno) es una propiedad de los índices que indica el porcentaje de espacio en cada página (del nivel de hoja) que se rellenará con datos y por tanto qué espacio quedará disponible para que el índice siga creciendo. Por ejemplo, un fill factor de 70% indicará que cada página de nivel de hoja tendrá disponible un 30% de espacio para inserciones en el índice.


En SQL Server las páginas tienen un tamaño de 8Kb y el fill factor es un valor de 0 a 100 (el 0 es igual que el 100, la página se rellena completamente). Mediante las instrucciones CREATE INDEX y ALTER INDEX podemos establecer el fill factor para un determinado índice.

Es importante elegir correctamente un fill factor adecuado para un índice:

  • Si seleccionamos un fill factor demasiado bajo (por ejemplo 10%) tendremos gran parte de las páginas vacías, habrá espacio suficiente en el índice para los posibles INSERT / DELETE / UPDATE que le afecten pero serán necesarias un mayor número de páginas que si utilizáramos un fill factor superior, por tanto, necesitaremos más espacio de almacenamiento y se reducirá el rendimiento de las lecturas.

  • Si seleccionamos un fill factor demasiado alto (por ejemplo 99%) y hay numerosos INSERT / DELETE / UPDATE que afectan al índice será necesario hacer divisiones de página para proporcionar espacio al índice. Estas divisiones de página consumen muchos recursos y además se produce fragmentación del índice lo que todavía genera más operaciones de E/S. Destacar que cuando una página de índice se divide los elementos se reparten entre ambas páginas al 50%, no se tiene en cuenta el fill factor del índice. La fragmentación del índice la solucionamos realizando operaciones de reorganización y regeneración de índices.

Entonces, ¿cuál es el fill factor más adecuado para un índice? Hay distintas recomendaciones que nos pueden ayudar a elegir el fill factor adecuado:

  • Un fill factor distinto de 100% puede ser adecuado para el rendimiento si los nuevos datos están distribuidos uniformemente en la tabla.

  • Si todos los datos se agregan al final de la tabla (por ejemplo, si el índice está definido sobre una columna identity) aplicaremos un fill factor con valor 100%, la clave para las filas nuevas del índice siempre aumenta por tanto siempre se insertarán al final, no tiene sentido dejar espacio libre en las páginas del índice.

  • Un índice definido sobre una tabla estática en la que sólo hayan lecturas (no hay INSERT / DELETE / UPDATE) debería tener un fill factor de 100%.

  • Para índices definidos sobre tablas que tienen pocas modificaciones (INSERT / DELETE / UPDATE) es recomendable un fill factor del 95%.

  • Para índices definidos sobre tablas con muchas modificaciones (INSERT / DELETE / UPDATE) es recomendable un fill factor entre el 70% y el 90%.

  • En raras ocasiones encontraremos adecuado un fill factor por debajo del 70% pero cuidado, podría darse el caso.

Lo anterior son recomendaciones, podemos tomarlas como punto de partida y luego analizar la fragmentación y densidad del índice para ir ajustando el fill factor según convenga. La fragmentación y densidad de índices lo dejaré para el artículo porque tiene su cosa, vayamos a ver ahora algunos ejemplos relacionados con el fill factor.

Para saber el fill factor de los índices existentes en una base de datos ejecutaremos la siguiente consulta:
-- Obtenemos fill factor de los índices de una base de datos
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, NAME AS IndexName, 
       TYPE_DESC AS IndexType, FILL_FACTOR
FROM SYS.INDEXES
Tomemos como ejemplo la base de datos AdventureWorks, modifiquemos el fill factor de un índice existente:
-- Modificamos el fill factor al 85%
ALTER INDEX IX_EmployeeDepartmentHistory_ShiftID 
ON HumanResources.EmployeeDepartmentHistory 
REBUILD WITH (FILLFACTOR = 85); 
En relación al índice anterior, podemos crearlo de nuevo con un fill factor diferente:
-- Creamos el índice con fill factor del 80%
CREATE INDEX IX_EmployeeDepartmentHistory_ShiftID 
ON HumanResources.EmployeeDepartmentHistory (ShiftID) 
WITH (DROP_EXISTING = ON, FILLFACTOR = 80); 
Algunas consideraciones más a tener en cuenta:

  • Tanto en el comando CREATE INDEX como ALTER INDEX encontramos la opción PAD_INDEX = { ON | OFF }, ¿qué quiere decir? Con PAD_INDEX = OFF las páginas del índice de niveles intermedios se rellenan al completo, con PAD_INDEX_ON se rellenerán con el valor de fill factor especificado para el índice (por ejemplo, al 80%).

  • Por defecto los índices se crean con fill factor 0 porque ese es el valor que por defecto trae configurado SQL Server. Podemos cambiar el valor por defecto de la siguiente manera:
    -- Obtenemos información sobre las opciones de configuración 
    -- Podemos ver la opción 'fill factor (%)'
    SELECT * FROM sys.configurations
    GO
    
    -- Cambiamos el fill factor por defecto a 95%
    EXEC sys.sp_configure 'show advanced options', '1'
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC sys.sp_configure 'fill factor (%)', '95'
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC sys.sp_configure 'show advanced options', '0'
    RECONFIGURE WITH OVERRIDE
    GO
    
Y hasta aquí el artículo de hoy, espero que os sirva para mejorar el rendimiento de vuestros índices. Recordad también que dentro de areaTIC podéis encontrar otros artículos, no dudéis en consultar nuestro archivo, también podéis seguirnos por RSS o las principales redes sociales (twitter, facebook, linkedin...) 


LECTURAS RELACIONADAS RECOMENDADAS POR AREATIC.NET

No hay comentarios:

Publicar un comentario en la entrada