Con la nueva versión SQL Server 2019 aparecen un conjunto interesante de funcionalidades orientadas a mejorar el rendimiento de forma transparente. Una de ellas es Memory-Optimized TempDB Metadata que consigue aprovechar la tecnología in-memory OLTP para la metadata de tempdb permitiendo de esa forma que muchos de los problemas de congestión habituales desaparezcan.

¿Si al finalizar el artículo quieres profundizar más en este tema con los mejores expertos, nuestro experto Rubén Garrigós dará una sesión práctica sobre SQL Server 2019 in practice: Memory-Optimized Temp-DB en el SolidQ Summit 2020, el evento nacional dedicado a la plataforma de datos de Microsoft, que tendrá lugar del 18 al 22 de mayo en formato online. ¿Te gustaría acompañar a Rubén en esta sesión y llevarte conocimientos para poder aplicar a tu propia empresa?

Reserva ya tu plaza aquí ▶️ http://ow.ly/yHGe50zvNCB 

Vamos a mostrar de forma sencilla cómo puede esta mejora afectar a una carga que tenga una congestión clara por metadatos en tempdb. Comenzaremos creando un procedimiento almacenado en AdventureWorks que únicamente crea un par de tablas temporales:

USE AdventureWorks
GO
CREATE OR ALTER PROCEDURE sp_tempdbload
AS
BEGIN

  CREATE TABLE #test(id int NOT NULL PRIMARY KEY);
  CREATE TABLE #test2(id int NOT NULL PRIMARY KEY);

END

A continuación, haremos uso de una herramienta bastante práctica para generar carga concurrente llamada ostress. Esta herramienta se incluye en las RML tools y puede descargarse aquí: https://support.microsoft.com/en-us/help/944837/description-of-the-replay-markup-language-rml-utilities-for-sql-server 

Concretamente vamos a utilizar ostress configurado con 64 threads y 1000 iteraciones por thread ejecutando el procedimiento sp_tempdbload para saturar a la instancia con suficiente peticiones (64000 peticiones en total):

ostress.exe -dadventureworks -S.\sql2019_2 -E -mstress -n64 -r1000 -Q"exec dbo.sp_tempdbload" -quiet

Podemos ver que el tiempo de ejecución de esta carga es de aproximadamente 22 segundos:

Este tiempo nos da un throughput de 2910 ejecuciones por segundo (~364 por segundo y core).  Podemos ver como no podemos usar toda la CPU disponible, existe contención que lo evita:

tempdb metadata

Concretamente la contención viene causadas por una gran cantidad de esperas de tipo PAGELATCH_EX:

Una vez tenemos esta «linea base», habilitaremos esta nueva funcionalidad, tras lo cual será necesario reiniciar.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Tras reiniciar verificaremos que efectivamente está habilitada consultando la propiedad IsTempdbMetadaMemoryOptimized:

tempdb metadata

Si volvemos a lanzar la misma carga que lanzamos anteriormente, esta vez el tiempo total es de menos de 6 segundos (versus los 22 segundos que necesitábamos sin esta optimización):

ostress.exe -dadventureworks -S.\sql2019_2 -E -mstress -n64 -r1000 -Q"exec dbo.sp_tempdbload" -quiet

Este resultado nos da un throughput de 11206 ejecuciones por segundo (~1401 por segundo y core).  Podemos ver como ahora sí podemos explotar al máximo la CPU disponible:

tempdb metadata

Y también desaparecen los PAGELATCH_EX:

En conclusión, esta nueva funcionalidad puede permitir incrementar notablemente el rendimiento de aquellas cargas que tengan una alta concurrencia y hagan uso intensivo de la metadata de tempdb.

Rubén Garrigós