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