SQL Server Access Methods object: Page Splits/sec

Una causa del exceso de I/O es el particionamiento de páginas, el page Split ocurre cuando una página está llena y se debe partir en 2 para colocar la data que se esta ingresando.

Si bien el page Split es normal, un número excesivo puede causar problemas de disco contribuyendo a un bajo performance, en este caso se debe considerar aumentar el fill factor de los índices, con esto aumentara la fragmentación y evitara los page Split.

Para bases de datos muy transaccionales (OLTP) el fill factor debe estar entre 1 y 99, para bases de datos de solo consulta (OLAP) debe estar en 0.

Esta es la forma muy resumida de que se trata este contador, sin embargo el tema no es tan simple como parece.

Partiendo desde el principio, Que es un página?

SQL Server almacena los indices y la data en blocks de 8KB de espacio continuo llamados páginas, estas se agrupan de a 8 continuas formando un Extend, los extends son de 64KB, los archivos fiisicos de la base de datos son "formateados" en paginas y extends. Cada pagina puede tener 1 o mas filas de una tabla o contener una o mas entradas de un indice.

Ok!, entonces Que es el page split?

Los indices y las tablas que continen indices clusterizados son almacenados siguiendo el orden que indica en indice correspondiente. Cuando insertamos una fila en la tabla esta debe ser ingresada en cierta posicion dentro del indice o la tabla y los datos contenidos en las paginas son ordenados.

Algunas veces estamos de suerte y la pagina donde debe ser ingresado tiene espacio disponible y solo se inserta en ella, sin embargo en otras ocasiones la pagina esta llena porlo tanto esta pagina es "dividida" en 2 y parte de las filas quedan en una pagina y otra parte queda en otra pagina. Esta division es el Page Split.

Entonces el page split es bueno o malo?

Los page split son normales en las bases de datos transaccionales, mientras mayor page split se produce un mayor requerimiento de I/O al disco, un excesivo page split puede afectar el performance del servidor generando problemas de performance de disco

Uups!! y hasta que valores es bueno y cuando pasa a ser malo?

Pues es dificil dar un numero, en algunos articulos mencionan 100 como un numero malo, valores sobre 100 generan problemas de discos, pero podria ser ya problemas un numero menor a ese, hay que observar como estan nuestros contadores de disco, en particular el contador "Physical Disk:Avg Disk Queue Length", si se observa este valor sobre lo aceptado y coinciden con los valores altos de page split, entonces estamos en el escenario malo.



mmmm, y como puedo evitar o disminuir los page split?

Pues incrementando el fill factor :D

ya, y que es el fill factor?

Para entender que es el fill factor primero debemos saber como se almacenan los indices dentro de la base de datos, los indices se guardan usando un B-Tree (Arbol Balanceado), cada nodo de este arbol es una pagina, la cual contiene uno o mas entradas de indice. Como ya mencionamos, cada vez que se inserta un dato este es colocado en la pagina que le corresponda segun el orden del indice y si la pagina esta llena se produce una division de la misma, esto tiene un costo.

Pero, y si nos aseguramos de alguna forma de que las paginas tengan espacio disponible de modo de no producir el Page Split?

Eso es el fill factor! con el le decimos al SQL Server que tan llenas deseamos que esten las paginas de modo que sean minimas las veces que encuentre paginas llenas, este factor solo hace referencia a las paginas hojas (Leaf), si queremos que tambien las paginas intermedias del indice esten con espacio disponible debemos habilitar el pad index.

Por ejemplo, usando CREATE CLUSTERED INDEX ... FILLFACTOR = 33 crea un índice clusterizado con un valor FILLFACTOR del 33 por ciento. Suponga que SQL Server calcula que 5,2 filas es el 33 por ciento del espacio de una página. SQL Server redondea esa cantidad, de forma que coloca seis filas en cada página.

La desventaja de usar fill factor y pad index es que usa mas paginas (Fragmentacion) y para las lecturas (SELECT) es un poco mas lento por que lee mas paginas, pero las inserciones son mas rapidas.

Entonces, que valor de fill factor uso?

Pues depende del nivel de transacciones y lecturas de las tablas donde estan los indices, para tablas del tipo OLAP, debe estar en 0, para OLTP un valor entre 1 y 99 dependiendo del nivel de transacciones, mientras mas insert/update vs select, menor debiera ser.

Mencionaste la fragmentacion, segun todo esto, la fragmentacion seria buena?


Para estos casos si, un indice fragmentado en una tabla muy transaccional tendria sus beneficios, pero solo en este escenario, en otros casos podria no ser bueno.

Para terminar, les dejo un articulo sobre Page Split que encontre muy interesante.

What is a page split? What happens? Why does it happen? Why worry?

Saludos!

Isa