La nueva versión de SQL Server 2022 ha traído varias novedades con respecto a su versión anterior.
Una de ellas es la optimización de la TEMPDB cambiando los algoritmos internos de la misma y así evitando la famosa contención.
En este post y video te voy a mostrar cómo ha mejorado la performance de la TEMPDB en SQL Server 2022.
Para mi demo voy a usar una instancia de SQL Server 2019 y otra de 2022 para hacerle unas pruebas de stress con Ostres
Paso 1: Eliminar los archivos de Tempdb
Para mi demo lo que voy a hacer primero es eliminar los ndf de la tempdb en SQL 2019 y 2022 (la buena práctica es tener tantos archivos de Data como cores exista hasta un máximo de 8)
DBCC DROPCLEANBUFFERS;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESESSIONCACHE;
GO
USE TEMPDB
DBCC SHRINKFILE('TEMPDEV2',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV3',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV4',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV5',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV6',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV7',EMPTYFILE)
DBCC SHRINKFILE('TEMPDEV8',EMPTYFILE)
USE MASTER;
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME=TEMPLOG ,
SIZE = 200MB, FILEGROWTH = 65536KB);
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV2;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV3;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV4;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV5;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV6;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV7;
GO
ALTER DATABASE TEMPDB REMOVE FILE TEMPDEV8;
Paso 2: Revisamos la configuración de in-memory Tempdb
Desde SQL Server 2019 podemos configurar la Tempdb para que se use in-memory
SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;
Paso 3: Prueba de performance
Ahora lo que haremos es una simple prueba de performance usando el Ostress en la cual correremos la siguiente consulta
DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB')OSTRESS -S.,16000 -Usa –PPassw0rd -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000OSTRESS -S.,15000 -Usa –PPassw0rd -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000Cuando corremos estas pruebas podemos monitorear nuestras instancias con el Activity Monitor
Resultados
| SQL 2019 | SQL 2022 | |
|---|---|---|
| Tiempo de proceso | 38s | 37s |
| Wait Promedios | 43 | 42 |
| Batch Request /sec | 1300 | 1300 |
Como se puede observar en ambas pruebas de stress los resultados para SQL 2019 y 2022 son iguales.
Paso 4: Activar Tempdb in memory
Ahora lo que haremos en cada instancia (SQL 2019 y 2022) es activar el uso de la tempdb inmemory para lo cual debemos correr el siguiente código TSQL
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;Luego se debe reiniciar la instancia de SQL para que tome el cambio y podemos con ello verificar que este activo en cada instancia usando la siguiente consulta
SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;
Paso 5: Volvemos a hacer el stress
Volvemos a correr nuestros procesos de Ostress
OSTRESS -S.,16000 -Usa –PPassw0rd -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000OSTRESS -S.,15000 -Usa –PPassw0rd -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000Resultados
| SQL 2019 | SQL 2022 | |
|---|---|---|
| Tiempo de proceso | 22s | 14s |
| Wait Promedios | 38 | 0 |
| Batch Request /sec | 2200 | 3700 |
Video demo
Te comparto el video tutorial completo
