Optimización del almacenamiento de la base de datos
Para optimizar el almacenamiento de la base de datos, deberá tener en cuenta la configuración de tempdb y del relleno proporcional.
¿Qué es el relleno proporcional?
Si se inserta un gigabyte de datos en una base de datos de SQL Server con dos archivos de datos, se espera que el tamaño de cada uno de los archivos de datos aumente aproximadamente 512 megabytes. Sin embargo, este crecimiento igual no corresponde necesariamente a este caso, ya que SQL Server insertará datos en archivos de datos en diferentes volúmenes en función del tamaño del archivo de datos. En el ejemplo anterior, si los archivos de datos tenían dos gigabytes de tamaño, se esperaría la distribución uniforme de los datos. Sin embargo, si uno de los archivos de datos era de 10 gigabytes y el otro, de 1, aproximadamente 900 MB se incluirían en el archivo de 10 gigabytes y 100 MB, en el archivo de 1 gigabyte. Aunque este comportamiento se produce en cualquier base de datos, con la naturaleza de escritura intensivas de tempdb, un patrón de escritura desigual podría provocar un cuello de botella en el archivo más grande, ya que se producirían allí más operaciones de escritura.
Configuración de tempdb en SQL Server
En SQL Server 2016, se cambió este comportamiento, ya que se detecta el número de CPU disponibles durante la instalación, se configura el número adecuado de archivos, hasta 8, y se ajusta el tamaño de los archivos de datos uniformemente. Además, los comportamientos de las marcas de seguimiento 1117 y 1118 están integrados en el motor de base de datos, pero solo para tempdb. En el caso de las cargas de trabajo pesadas de tempdb, puede haber ventajas en aumentar el número de archivos de tempdb más allá de ocho hasta el número de CPU de la máquina.
SQL Server usa tempdb para mucho más que el almacenamiento de tablas temporales definidas por el usuario. Las tablas de trabajo que se usan para almacenar los resultados intermedios de consultas, las operaciones de ordenación y el almacén de versiones para las versiones de fila son solo algunos de los usos de tempdb. Debido a este uso, es importante tanto la colocación de tempdb en el almacenamiento de latencia más bajo posible como la configuración correcta de los archivos de datos.
Antes de SQL Server 2016, tempdb tenía un solo archivo de datos como opción predeterminada. Este único archivo significaba que podría producirse la contención de varios procesos que intentaran acceder a las páginas del sistema de la base de datos tempdb. Una solución común a este problema de contención era habilitar la marca de seguimiento 1118, que cambió el modo de asignación de las extensiones. Otro procedimiento que se recomendaba habitualmente era crear varios archivos de datos de tempdb. Dado que SQL Server usa un algoritmo de relleno proporcional para las bases de datos con varios archivos de datos, también era importante asegurarse de que esos archivos tuvieran el mismo tamaño y aumentaran a la misma velocidad. Para admitir esta gran cantidad de DBA, se usaba la marca de seguimiento 1117, que forzaba que todas las bases de datos con varios archivos de datos aumentaran a la misma velocidad.