Todo DBA de SQL Server sabe lo importante que es la base de datos Tempdb. Conocemos que tenerla bien configurada nos asegura no tener un cuello de botella en performance para esta base de datos tan importante dentro de nuestro motor.
Desde hace años hay varias recomendaciones para evitar la contención en la metadata de la tempdb como por ejemplo:
- Crear tantos Datafiles como cores exista (hasta 8)
- Habilitar los TF 1117 y 1118 si la versión es menor a SQL Server 2016 (en esta última viene activo por defecto).
En las últimas versiones de SQL Server el propio instalador (setup) hacia las recomendaciones necesarias para esta base de datos ya que en muchas ocasiones los DBA no la configuraban según las buenas practicas.
SQL Server 2019
En SQL Server 2019 se ha dado un paso mucho más importante y es la posibilidad de que la metadata de la tempdb este 100% en RAM como una tabla in-memory del In-memory OLTP que ya existe desde versiones anteriores.
Obviamente que tener la metadata de la TempDB en memoria hará que ya no sea más un problema ni cuello de botella la creación y recreación de objetos, recordemos que esta base de datos tiene la particularidad que se crean y borran objetos todo el tiempo.
En este post vamos a probar el funcionamiento de esta nueva funcionalidad de SQL Server 2019, en mi caso usare una imagen de docker de SQL Server 2019 sobre Ubuntu , si quieres saber como instalarlo aca te dejo un post
Paso 1:
Lo primero que vamos a chequear que la nueva funcionalidad viene deshabilitada por defecto , para chequear el estado vamos a ejecutar el siguiente código TSQL
SELECT
SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS
IsTempDBMetadataMemory;
Paso 2:
Crearemos una base de datos nueva con un simple Store Procedure el cual generar tablas temporales
IF NOT EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME='TRIGGERDBDEMO') CREATE DATABASE TRIGGERDBDEMO
USE TRIGGERDBDEMO
GO
CREATE OR ALTER PROCEDURE DBO.USP_SQL2019TEMPDB AS
CREATE TABLE #TEMP1 (ID INT IDENTITY PRIMARY KEY,
NAME CHAR(50),
FECHA DATETIME)
GO
Paso 3:
Lo que probaremos ahora es una carga de stress llamando muchas veces al Store Procedure que hemos creado antes.
Para poder hacer dicha carga de stress usaremos Ostress.exe de las RML utility las cuales puedes descargar de este link
Abriremos la línea de comandos y ejecutamos el siguiente código
c:\> ostress.exe -Slocalhost,15000 -Usa -PPassw0rd/// -dTriggerdbDEMO -Q”EXEC DBO.USP_SQL2019TEMPDB” -mstress -quiet -n50 -r1000
Si mientras ejecutamos el stress queremos monitorear las contenciones en la tempdb podríamos usar el siguiente código
USE tempdb GO SELECT object_name(page_info.object_id), page_info.* FROM sys.dm_exec_requests AS d CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id,r.page_id,'DETAILED') AS page_info
El tiempo para nuestra prueba de stress fue de 38 segundos.
Paso 4:
Ahora vamos a activar y usar la nueva funcionalidad de SQL Server 2019 para que la metadata de la tempdb resida en memoria, para ello debemos seguir estos pasos.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;
Reiniciar el servicio de SQL Server, en mi caso como estoy usando un contenedor voy a reiniciar el container de la siguiente forma:
c:\> docker restart fe139cdee3eb
Luego del reinicio nos conectaremos a nuestra instancia para verificar que haya quedado activo de forma correcta ejecutando el siguiente código y verificando que el resultado sea 1
SELECT
SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS
IsTempDBMetadataMemory;
Ahora volvemos a ejecutar nuestra prueba de Ostress
c:\> ostress.exe -Slocalhost,15000 -Usa -PPassw0rd/// -dTriggerdbDEMO -Q”EXEC DBO.USP_SQL2019TEMPDB” -mstress -quiet -n50 -r1000
En mi prueba ahora el proceso demoró 16 segundos vs los 38 originales, pero además de ello bajaron casi a 0 las contenciones dentro de mi Tempdb
Conclusiones
Ya desde hace varias versiones atrás SQL Server viene incorporando distintas funciones in-memory con el objetivo de poder mejorar la performance. La de la metadata de Tempdb es otra mas de ellas que habrá que analizar si como DBA´s la activamos o no, siempre debemos considerar que se debe analizar el consumo de memoria RAM y ver si tenemos los recursos suficientes para poder poder esta funcionalidad
