El algoritmo de llenado proporcional (Proportional fill)

En una reunión fuera de lo laboral me hicieron la siguiente pregunta:

¿Si yo pongo más archivos a mi base de datos, va a funcionar mejor?  o me da lo mismo?

Bien, la respuesta es relativa, ahora, de entrada, podemos decir que el rendimiento se va a mejorar, no sabemos a ciencia cierta y con tan poca información cuanto vaya a mejorar, pero de que mejora mejora, sea un 1%, pero eso es mejora al fin y al cabo.

El paso a seguir es entender cómo funciona el algoritmo de llenado proporcional (Proportional fill) que usa SQL Server.

El algoritmo de llenado proporcional en SQL Server se encarga básicamente de distribuir la carga a través de los diferentes archivos de datos que tenga un filegroup, como regla básica, la carga se va hacia el archivo de datos que mayor espacio disponible tenga.

Funciona mediante Round-Robin, de esta forma el algoritmo intenta ser lo mas equitativo posible en términos de carga en los archivos, sin embargo, esto trae otro dolor de cabeza, ¿Todos los discos de mi maquina tienen el mismo rendimiento esperado en términos de IOPs?, de ser así, pues no hay problema, pero de no ser así (Que es como seguramente ocurre), entonces vienen los problemas, se debe tener en cuenta que el rendimiento de mi base de datos entonces dependerá de mi componente mas lento, pero bueno, para seguir mas a fondo, veamos entonces:

¿Como saber cuánto espacio disponible tengo en cada archivo de datos?

SQL Server cuenta con la función FileProperty, esta función recibe como parámetros el nombre del archivo y la propiedad que se desea obtener, para este caso utilizamos la opción SpaceUsed, esta nos devuelve la información en términos de la cantidad de paginas en el archivo, siendo asi, debemos tener en cuenta que una pagina en SQL Server tiene un tamaño de 8KB, por ende debemos multiplicar este dato por 8 para obtener el tamaño en KB.

SELECT (FILEPROPERTY('AdventureWorks2012_Data', 'SpaceUsed') * 8)/1024 [Espacio en MB]

De igual manera utilizamos la tabla del sistema sys.master_files, allí tenemos cada archivo de datos y de log dentro de la base de datos, allí encontramos el tamaño del archivo igualmente en términos de paginas.

select name,

CAST(FILEPROPERTY(name, 'SpaceUsed') AS float) [Paginas],

size * 8 [Tamano KB],

CAST(FILEPROPERTY(name, 'SpaceUsed') AS float) * 8 [Usado en KB],

(size * 8) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS float) * 8) [Disponible en KB]

from sys.master_files

where type = 0

and database_id = db_id()