SQL Server 2014 – Performance TempDB et écritures disque

Encore une nouveauté peu mise en avant sur SQL Server 2014 ! Certes pas très sexy, mais je peux vous certifier que bien des workload vont être (très) positivement impactés par cette nouveauté.

Vous n’êtes pas sans savoir que la TempDB possède un mécanisme de logging (écriture dans le fichier LDF) légèrement différent de celui des autres bases de données. En effet, nul besoin d’assurer le REDO en cas de crash, car la TempDB est reconstruite à chaque démarrage de votre instance.  On ne récupère rien de ce qui a été fait dans cette base de données.

Si vous avez un doute, faites le test suivant : Stoppez le service SQL, supprimez les fichiers de données et de journal de transaction de cette base et redémarrez le service SQL. Les fichiers TempDB sont de nouveau présents.

Donc la TempDB est performante, car moins de journalisation. Mais il était encore possible d’améliorer les choses, car comme toute autre base de donnée, les pages qui la composent dans le buffer pool doivent être écrites sur disque, par le checkpoint et ou lazy writer. Et cela devait se faire assez rapidement.

Avec SQL Server 2014 les pages de données d’objets temporaire peuvent être allouées, utilisées et libérées sans même avoir été écrites sur disque. On est donc moins stressé à écrire rapidement ces pages sur disque, impliquant un gain de performance, mais également, une baisse non négligeable d’activité sur les disques TempDB Data, ce qui dans le cas d’un SAN va bénéficier à tout le monde …

Deux opérations principales vont bénéficier de cette amélioration :

• création d’index avec l’option sort in tempdb
• opérations de bulk insert dans des tables temporaires.

Pour vous démontrer le gain que cela peut amener, j’ai choisi le second point. Afin d’être le plus équitable possible, mes 2 VMs sont équivalentes : 4 vCPU, 8GB de RAM, SQL Server installé de la même manière, disques rotatifs (pour bien mesurer la différence qui serait gommée par un disque flash plus rapide en écriture).

Voici le script de démo (joué à tour de rôle sur chacun des serveurs):

-- Restart SQL Server
-- PowerShell : restart-service MSSQLSERVER


-- augmentation de taille de la TempDB
USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB )
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N'templog', SIZE = 20480KB )
GO


-- Création procédure de Test
Use [TempDB]
GO

CREATE PROCEDURE dbo.usp_Test
AS
BEGIN

	SET NOCOUNT ON
	
	DECLARE @Total Money = 0
	DECLARE @min int = 1 
	DECLARE @max int = 2000
	
	WHILE @min <= @max
	BEGIN
		SELECT *
		INTO #result
		FROM AdventureWorks.Sales.SalesOrderHeader

		SELECT @Total = @Total + SUM(TotalDue)
		FROM #result

		DROP TABLE #result

		SET @min += 1
	END

END
GO


-- Vérification du nombre d'IOs sur la TempDB
SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], 
		df.physical_name AS [Physical Name], vfs.num_of_reads, vfs.num_of_writes, 
		CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 
		CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.[file_id]= df.[file_id]
OPTION (RECOMPILE);
GO

Exec usp_Test
GO

SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], 
		df.physical_name AS [Physical Name], vfs.num_of_reads, vfs.num_of_writes, 
		CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 
		CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.[file_id]= df.[file_id]
OPTION (RECOMPILE);
GO


DROP PROCEDURE usp_Test

Une simple boucle créant une table temporaire et lisant des informations … SQL Server est redémarré avant chaque test.

Plus de 40 minutes et 12,5GB écrit dans le fichier TempDB Data !

Avec SQL Server 2014 le temps d’exécution est de 1:37. On voit aussi que 40 écritures disques ont été effectuées sur le fichier de data, pour 300KB

Le constat est sans appel, la fonctionnalité d’eager write démontre tout son intérêt. Le gain de performance est hallucinant, tant que l’on dispose de suffisamment de mémoire, sinon, on reprend le fonctionnement antérieur.

Les curieux qui disposent de l’édition entreprise de SQL Server ont peut être eu l’idée de comparer les performances avec l’utilisation de tables non durables.

Il est très compliqué d’établir un comparatif car dans la procédure stockée compilée nativement, on ne peut utiliser que des tables InMemory. Reste donc l’option Interop. mais peu performante dans ce cas là (6:19 minutes).

Impossible d’annoncer le gain que vous obtiendrez à migrer sur SQL Server 2014, beaucoup de paramètres entrent en jeux. Mais en tenant compte des performances accrues de la TempDB, du nouvel estimateur de cardinalités, du SELECT INTO parallélisé, du Buffer Pool Extension, du gouverneur de ressources qui gère les IOs, et bien sur Hekaton, nul doute que SQL Server 2014 est né sous le signe de la performance.

Happy eager write behaviour !