SQL Server 2014 – Buffer Pool Extension

Lors de mes derniers billets concernant Hekaton, j’ai parlé performance. Il est clairement apparu que les tables Hekaton, des tables résident en mémoire et ne souffrant pas de lock ni de latch permettaient d’atteindre des performances très largement supérieures à ce que permet une table “classique”, ou disk-based table. D’où l’importance de la mémoire sur la performance des requêtes.

Les tables Hekaton doivent résider en mémoire, en totalité, sinon, la transaction échoue, tout simplement :

image

A l’heure actuelle il n’est pas possible de fixer de limite sur la quantité de mémoire utilisée par les bases de données memory-optimized. On peut cependant observer la quantité que cela représente.

image

Les valeurs min server memory et max server memory permettant de contrôler la taille du buffer pool devront donc être positionnées précisément pour laisser le champs libre aux tables memory-optimized. Encore que, si on est en mémoire dynamique, SQL Server devrait diminuer la taille du buffer pool.

On sait donc qu’une transaction pour une table Hekaton qui ne dispose pas suffisamment de mémoire échoue. Qu’en est-il pour une table disk-based ?

Vous connaissez tous la réponse. Cela fonctionne parfaitement, puisque vous n’avez jamais eu de problème … Mais au prix de performances dégradées, n’est-ce pas ? Car il faut lire les données du disque vers le buffer pool (PAGEIOLATCH), probablement au détriment d’autres données entrainant du LazyWrite / sec … Et si vous devez charger une grande quantité de données ou bien si vous souffrez de latence disque élevée, alors l’impact sur la performance de votre serveur est non négligeable.

Pour en faire la démonstration, je vais m’appuyer sur une version modifiée de la base AdventureWorks dont certaines tables ont été “gonflées” dont bigTransactionHistory (1.031 GB et 31 263 601 enregistrements).

image

Si on exécute une requête lisant l’intégralité de la table, on constate que la mémoire occupée par SQL Server augmente et que le buffer pool s’est rempli des pages de cette table.

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT AVG(actualcost) 
FROM bigTransactionHistory WITH (INDEX=0)

Durant les 38 secondes d’exécution de la requête, le disque C:\ hébergeant le base est fortement sollicité. Ce disque est un disque magnétique, à plateau.

/*————————
SELECT AVG(actualcost) FROM bigTransactionHistory WITH (INDEX=0)
————————*/
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 60 ms.

(1 row(s) affected)
Table ‘bigTransactionHistory’. Scan count 4, logical reads 144068, physical reads 1, read-ahead reads 143652, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 11813 ms,  elapsed time = 38111 ms.

Si l’on jette un œil au buffer pool, on constate alors que toutes les pages de données sont présentes :

image

Une seconde exécution de cette même requête est bien plus rapide (moins de 3 secondes) puisque aucune lecture physique n’est effectuée :

/*————————

SELECT AVG(actualcost) FROM bigTransactionHistory WITH (INDEX=0)
————————*/
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table ‘bigTransactionHistory’. Scan count 4, logical reads 143984, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 7438 ms,  elapsed time = 2648 ms.

Afin de stresser la mémoire, je passe la valeur max server memory à 256 MB afin de ne pas pouvoir stocker l’intégralité de mes données en mémoire. Ensuite, je vide le contenu du buffer pool.

EXEC sys.sp_configure N'max server memory (MB)', N'256'
GO
RECONFIGURE WITH OVERRIDE
GO


DBCC DROPCLEANBUFFERS

Et je relance la requête de test :

/*————————
SELECT AVG(actualcost) FROM bigTransactionHistory WITH (INDEX=0)
————————*/
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 10 ms.

(1 row(s) affected)
Table ‘bigTransactionHistory’. Scan count 4, logical reads 144080, physical reads 1, read-ahead reads 143637, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 11392 ms,  elapsed time = 37827 ms.

Après avoir effectué plusieurs tests, la durée peut varier légèrement mais reste globalement enter 33 et 38 secondes.  Mais le CPU time reste quasi constant. On fait des lectures physiques à chaque requête. Le buffer pool ne stocke plus l’intégralité des données, ce qui est assez représentatif des serveurs installés car peu de serveur ont une mémoire au moins égale à la somme des pages de toutes les tables de toutes les bases hébergées sur l’instance.

Ce buffer pool est partagé par toutes les bases de données de l’instance et doit satisfaire toutes les requêtées de tous les utilisateurs connectés. Il est fort probable que cela constitue un goulet d’étranglement pour votre instance SQL. Une valeur de PLE un peu faible, des lazy writes / sec un peu élevés peuvent traduire ce stress mémoire. Un peu de compression de données aidera probablement et permettra de donner un second souffre à votre serveur (je pars du principe que l’optimisation était correcte, on est bien d’accord ? Et que ce n’est pas un bon vieux cluster index scan qui vous vide le buffer pool des données les plus utilisées).

Avec l’arrivée de SQL Server 2014, il est maintenant possible d’étendre le buffer pool sur disque, rapide de préférence ! Je vais donc profiter de cette nouvelle option offerte par SQL Server 2014 : le Buffer Pool Extension.

image


ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON ( 
	FILENAME = 'G:\Data\BufferPoolExtensionFile.BPE',
	SIZE = 10 GB);
GO

DBCC DROPCLEANBUFFERS

La première exécution s’est avérée un peu plus lente que les tests précédents, probablement du au temps d’écriture des données dans le fichier BPE :

/*————————
SELECT AVG(actualcost) FROM bigTransactionHistory WITH (INDEX=0)
————————*/
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table ‘bigTransactionHistory’. Scan count 4, logical reads 144024, physical reads 3, read-ahead reads 143643, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 11798 ms,  elapsed time = 41978 ms.

On constate de l’activité disque (en écriture) sur le fichier permettant de stocker l’extension du buffer pool. Il s’agit d’un disque SSD (un peu ancien) directement présenté à la VM. Des compteurs de performance ont été ajoutés afin de visualiser ce fonctionnement.

image

Lors de l’écriture des données dans le BPE, on voit que le nombre de pages écrites sur l’extension varie.

Au final, l’espace consommé dans le buffer pool n’est que de 69 MB (la même valeur que lors du test avec MAX Memory = 256 MB et sans BPE)

image

En interrogeant la DMV sys.dm_os_buffer_descriptors, on s’aperçoit qu’une nouvelle colonne a fait son apparition : is_in_bpool_extension. Ainsi, on est en mesure de savoir si la page de données a laquelle on accède est dans le buffer pool (en RAM) ou bien dans l’extension (le BPE) sur disque :

image

Lorsque je ré exécute la requête de test, le performance sont un peu meilleure car une partie de la lecture des données du fichier de données est reportée sur le fichier d’extension du BPE, théoriquement plus rapide.

image

Plus ce disque sera performant, plus le BPE présentera un gain de performance pour votre instance SQL. Les IOs ressemblent à ce que le buffer manager réaliser sur le buffer pool (normal), soit des lectures de 8Ko (une page de donnée). Il sera probablement intéressant d’aligner la taille des cluster à 8Ko (mon disque était formaté avec des cluster à 64Ko).

Pour supprimer le BPE, la requête SQL est très simple :

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION OFF
GO

Est-ce que cette fonctionnalité est intéressante : sans nul doute ! Voire, elle pourrait même, à elle seule, justifier une migration …

  • Seule des clean pages sont délocalisées sur le BPE, ainsi les dirty pages restent dans le buffer pool dont l’accès est très rapide. Sachant que l’on ne stocke que des pages propres, il n’y a aucun risque de perte de données
  • Pour des serveurs dont les slots mémoire sont remplis, il sera très simple d’ajouter un disque SSD ou bien une carte de type FusionIO et d’y étendre le buffer pool afin de profiter d’un maximum de performance pour votre charge (plutôt de type OLTP)
  • Le fichier étant stocké sur un disque local, cela permet de soulager le SAN en “délocalisant” certains IOs qui auraient du être fait sur les fichiers de données vers un disque local, rapide, à faible latence
  • Cette solution est compatible avec les instances FCI

En bref, je suis fan. Et vous ?

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

6 commentaires pour SQL Server 2014 – Buffer Pool Extension

  1. Frederic Dumont dit :

    j’ai pas tout compris …. Mais c’est beau !

  2. Ping : SQL Server 2014 – Buffer Pool Extension |...

  3. Ping : SQL Server 2014 RTM | Christophe LAPORTE – Consultant SQL Server

  4. Ping : SQL Server 2014 – Performance TempDB et écritures disque | Christophe LAPORTE – Consultant SQL Server

  5. Ah j’étais étonné que tu n’en aies pas parlé à la sortie de SQL 2014, mais en fait t’avais fait un article y a 6 mois :p

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s