kentoh - Fotolia
Cómo configurar la característica de extensión de grupo de búfer de SQL Server
SQL Server 2014 incluye la función de extensión de grupo de búfer, lo que puede acelerar el rendimiento de las consultas. Ashish Mehta explica los beneficios de la función y la forma de activarla y desactivarla.
El grupo de búfer de SQL Server es un sistema de caché en la memoria de SQL Server que contiene los resultados de la consulta. Esto permite que las consultas posteriores que comparten los mismos resultados se desempeñen más rápido, ya que SQL Server puede extraer los datos de la caché del grupo de búfer en lugar de tener que obtenerlo del almacenamiento en disco. SQL Server 2014 introdujo la posibilidad de ampliar el grupo de búfer en una unidad de estado sólido u otro dispositivo de almacenamiento no volátil. La función de extensión de grupo de búfer, que también será compatible con SQL Server 2016, puede ayudar a aumentar el rendimiento de un sistema de procesamiento de transacciones en línea de una manera rentable.
El método más común para resolver los cuellos de botella de entrada y salida (E/S) del disco que a menudo ralentizan el rendimiento OLTP es agregar más memoria o unidades de disco de alto rendimiento a un sistema de SQL Server. Sin embargo, ambas opciones son caras. Ahí es donde la extensión de grupo de búfer de SQL Server se convierte en útil para que las organizaciones la implementen. Permite que el grupo de búferes de SQL Server, que consiste en datos de 8 KB y páginas de índice, manejen grandes conjuntos de datos a un costo relativamente bajo. Según Microsoft, la descarga de E/S de las operaciones de discos convencionales para bajar la latencia de los SSD a través de la extensión del grupo de búfer debe "significativamente" mejorar el rendimiento de E/S.
La función está disponible actualmente en las ediciones Enterprise, Business Intelligence y Standard x64 de SQL Server 2014. Habilitar la característica de extensión de grupo de búfer en SQL Server 2014 también proporciona un par de ventajas a los administradores de bases de datos. Mediante su uso, los administradores de bases pueden aumentar el rendimiento de las consultas al tiempo que limitan la cantidad de memoria en una instancia de SQL Server a través del parámetro SP_CONFIGURE MAX SERVER MEMORY en Transact-SQL. También pueden habilitar la extensión durante las cargas de trabajo de procesamiento pesado sin necesidad de reiniciar una instancia de SQL Server.
Permitiendo la extensión de grupo de búfer
Para empezar, encuentre el valor actualmente configurado del parámetro MAX SERVER MEMORY para su instancia de SQL Server mediante el procedimiento almacenado SP_CONFIGURE como se muestra a continuación.
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory (MB)'
GO
En este ejemplo, digamos que la instancia está configurada para utilizar 16 GB de memoria RAM disponible. Al habilitar y configurar la función de extensión de grupo de búfer, establezca el uso de 32 GB como el nuevo tamaño máximo de archivo y añada un dispositivo dedicado SSD de 150 GB como la unidad P: del sistema de SQL Server.
Antes de seguir adelante y habilitar la extensión, también es necesario crear una carpeta llamada "BUFFER POOL EXTENSION" en la unidad P. Sin esto, su próxima consulta fallará. Después de crear la carpeta, ejecute la secuencia de comandos a continuación.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'P:\BUFFER POOL EXTENSION\SQLServerCache.BUFFER POOL EXTENSION', SIZE = 32 GB);
GO
Desactivación y alteración de la extensión
Para desactivar la función de extensión de grupo de búfer en SQL Server 2014, ejecute la secuencia de comandos a continuación. Sin embargo, para evitar tener un impacto negativo, reinicie la instancia de SQL Server después de desactivar la extensión.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
Antes de modificar los parámetros de extensión del grupo de búfer existente, deshabilite la función y luego vuelva a habilitarla con los parámetros modificados. Para alterar o modificar el tamaño de un archivo de extensión de grupo de búfer –por ejemplo, para aumentar el tamaño de archivo a 64 GB– ejecute la secuencia de comandos a continuación.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'P:\BUFFER POOL EXTENSION\SQLServerCache.BUFFER POOL EXTENSION', SIZE = 64 GB);
GO
Cuando se vuelva a activar la extensión, la memoria se puede volver a utilizar sin tener que reiniciar la instancia de SQL Server.
Mejores prácticas de extensión de grupo de búfer
Microsoft dice que la extensión de grupo de búfer puede ser de hasta 32 veces el valor del parámetro de memoria física (es decir, MAX SERVER MEMORY) en el caso de una instancia de SQL Server Enterprise Edition, y hasta cuatro veces con una edición estándar. Pero recomienda una proporción de 1:16 o menos entre la cantidad de memoria y el tamaño de la extensión y advierte que una proporción de 1:4 a 1:8 podría ser óptima como punto de partida.
Según Microsoft, los usuarios también deben probar plenamente la extensión en un entorno de control de calidad o de preproducción para identificar el tamaño del archivo justo antes de ponerlo en producción.
Además, el rendimiento del sistema de SQL Server puede verse afectada negativamente si el tamaño total del grupo de búfer se reduce debido a que la función de la extensión no está habilitada o el tamaño del archivo se reduce. Si desactiva la extensión de agrupación de almacenamiento intermedio, asegúrese de reiniciar rápidamente la instancia de SQL Server –el sistema no va a reclamar la memoria utilizada para soportar la extensión hasta que usted haga esto.
Sobre el autor: Ashish Kumar Mehta es administrador de bases de datos, entrenador y autor técnico. Tiene más de una década de experiencia en la administración de bases de datos, ajustes del rendimiento, desarrollo de bases de datos y entrenamiento técnico en Microsoft SQL Server, desde la versión 2000 a la 2014. También ha desarrollado y entregado proyectos exitosos en infraestructura de bases de datos, data warehouse e inteligencia de negocios, migración de bases de datos y actualizaciones. Es ingeniero en Ciencias de la Computación y cuenta con certificaciones de Microsoft, incluyendo MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 y MCTS .NET Framework 2.0 Web Applications.
