SQL 2022 Optimización TEMPDB

Dec 27, 2022 | Performance, SQL 2022, SQL Server | 0 comments

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 -r1000
OSTRESS -S.,15000 -Usa –PPassw0rd  -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000

Cuando corremos estas pruebas podemos monitorear nuestras instancias con el Activity Monitor

Resultados

SQL 2019SQL 2022
Tiempo de proceso38s37s
Wait Promedios4342
Batch Request /sec13001300

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 -r1000
OSTRESS -S.,15000 -Usa –PPassw0rd  -Q"SET NOCOUNT ON; DECLARE @T TABLE (C1 CHAR(100)); INSERT INTO @T VALUES ('TRIGGERDB');" -n50 -r1000

Resultados

SQL 2019SQL 2022
Tiempo de proceso22s14s
Wait Promedios380
Batch Request /sec22003700

Video demo

Te comparto el video tutorial completo

Acerca del autor

Maximiliano Accotto

Con mas de 20 años de experiencia y trayectoria trabajando con bases de datos SQL Server y BI.

Fui Microsoft MVP desde el 2005 al 2019 y soy orador frecuente para distintos eventos de Microsoft y comunidades técnicas.

Me especializado en temas de tuning, administración, performance, diseño y BI en el mundo Microsoft ya sea para ambientes locales como nube.

 

Asesoramiento

¿Necesitas resolver problemas de SQL Server o Micosoft BI?

De ser así te puedo ayudar con mi asesoramiento totalmente personalizado

0 Comments