SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    Pas de commentaires

Pour répondre rapidement aux instructions, SQL SERVER dispose d’un pool de mémoire pour stocker les plans d’exécution et les données. La part du pool de mémoire utilisée pour stocker les plans d’exécution est appelée le cache de procédures.
Lorsqu’une instruction SQL est lancé, le moteur relationnel parcourt d’abord le cache de procédures pour voir s’il existe un plan d’exécution pour la même instruction, afin de réutiliser ce plan. Ceci évite donc au moteur de mobiliser des ressources pour compiler l’instruction SQL. Si aucun plan approprié n’est trouvé, le moteur génère un nouveau plan d’éxécution pour l’instruction. Ce billet propose quelques pistes pour optimiser l’utilisation du cache de procédure et par conséquent optimiser les temps de réponse du SGBD.

– /!\ Attenttion : Ne pas reproduire cette démo sur un serveur de production, vous allez effacer les caches et tuer ainsi les perfomances !

——————————————
— Etape 0 : Création et Chargement de la table de test
——————————————

--Création de la table de test T_TEST
USE [MaBase]  
SET NOCOUNT ON  
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL  
DROP TABLE dbo.T_TEST  
CREATE TABLE dbo.T_TEST  
(  
  id int identity(1,1),  
  val varchar(10),  
  creation_date datetime  
)  
--Chargement d'un million de lignes dans la table : Cette opération a duré environ 9 minutes sur ma machine (SQL2K8 Edition Entreprise (version Evaluation) - OS: WXP SP3 - 2CPU T5600 @1.83GHZ - Mémoire = 2Go )
DECLARE @counter int;  
SET @counter = 1;  
WHILE @counter <= 1000000  
  BEGIN  
  INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(left(convert(bigint,RAND()*10000000),6))),getdate());  
  SET @counter = @counter + 1  
END;

——————————————
–Etape 1 : casse et cache
——————————————
–Effaçons le contenu du cache de procédures pour toute l’instance

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

— Exécutons ensuite cette requête de test

SELECT * FROM T_TEST WHERE id = 130

–Exécutons la requête suivante(nommons cette requête REQ_CACHE1) pour afficher le contenu du cache relatif à la requête de test.

SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like 'SELECT * FROM T_TEST WHERE id %'
ORDER BY usecounts DESC;

— Résultat : création du plan d’exécution dans le cache
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       SELECT * FROM T_TEST WHERE id = 130

— Exécutons une 2ème fois la même requête de test :

SELECT * FROM T_TEST WHERE id = 130

–Exécutons REQ_CACHE1 pour afficher le contenu du cache

–Résultat 2ème exécution : Réutilisation du plan d’éxécution existant (usecounts = 2 avec le même plan)
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  2  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       SELECT * FROM T_TEST WHERE id = 130

–Examinons maintenant l’effet de la casse en écrivant notre requête de test avec un select écrit en minuscule puis exécutons la requête

select * FROM T_TEST WHERE id = 130

–Affichons le contenu du cache avec REQ_CACHE1

–Résultat : Il n’y a pas réutilisation du plan existant, un nouveau plan est créé et consomme 24576 octets !
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  2  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       SELECT * FROM T_TEST WHERE id = 130
  1  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       select * FROM T_TEST WHERE id = 130

–>Conclusion casse et cache :
————————–
— Si à chaque fois vous changez la façon d’écrire une même requête, vous aller gaspiller de la mémoire.
— Imaginer le nombre de combinaison possible pour écrire la même requête de test :

Et chacune de ces formes va mobiliser une quantité non négligeable de mémoire.

——————————————
–Etape 2 : espace et cache
——————————————
–Effaçons le contenu du cache de procédures pour toute l’instance

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

— Exécutons cette requête de test

SELECT * FROM T_TEST WHERE id = 130

–Puis celle-ci avec un espace supplémentaire entre * et FROM (l’espace en trop n’est pas visible dans le rendu du blog, c’est pourquoi j’ai mis ..FROM pour attirer votre attention sur l’espace supplémentaire entre * et FROM )

SELECT *..FROM T_TEST WHERE id = 130

–Affichons le cache à l’aide de la requête suivante :

SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like 'SELECT * %FROM T_TEST WHERE id %'
ORDER BY usecounts DESC;

–Résultat : Il n’y a pas réutilisation du plan existant, chacune des requête génère un plan et chaque plan mobilise 24576 octets !
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       SELECT *  FROM T_TEST WHERE id = 130
  1  ............  Compiled Plan  ............           Compiled Plan   ......           24576    ......       SELECT * FROM T_TEST WHERE id = 130

–>Conclusion espace et cache :
————————–
Un espace en trop crée une différence entre deux requêtes identiques sémantiquement,syntaxiquement et ayant la même casse. Cette différence entraîne la non réutilisation du plan existant et par conséquence du gaspillage de ressources !

——————————————
–Etape 3 : requête paramétrée et cache
——————————————
–Effaçons le cache de procédures

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

— Exécutons la requête suivante avec @id = 130

DECLARE @id int;
SET @id = 130
SELECT * FROM T_TEST WHERE id = @id

–Affichons le cache avec la requête suivante (REQ_CACHE2)

SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like 'DECLARE @id %'
ORDER BY usecounts DESC;

–Résultat : Un plan est créé et consomme 40960 octets
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       DECLARE @id int;  SET @id = 130  SELECT * FROM T_TEST WHERE id = @id

— Exécutons à nouveau la requête précedente mais cette fois-ci avec @id = 131

DECLARE @id int;
SET @id = 131
SELECT * FROM T_TEST WHERE id = @id

–Puis affichons le cache avec REQ_CACHE2

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       DECLARE @id int;  SET @id = 131  SELECT * FROM T_TEST WHERE id = @id
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       DECLARE @id int;  SET @id = 130  SELECT * FROM T_TEST WHERE id = @id

–Résultat : Il n’y a pas réutilisation du plan d’exécution existant , un nouveau plan est créé et consomme 40960 octets de mémoire !
————————–

Comment éviter ce gaspillage de mémoire dans ce cas ?

——————————————
–Etape 4 : sp_executesql et cache
——————————————
–Effaçons le cache de procédures

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

— Exécutons la requête suivante avec @id = 130

EXEC sp_executesql N'SELECT * FROM T_TEST WHERE id = @id',N'@id int',@id =130

–Exécutons la requête suivante (nommons la requête REQ_CACHE_sp_executesql) pour afficher le contenu du cache relatif à cette requête de test

SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like '(@id int)SELECT * FROM T_TEST WHERE id = @id'
ORDER BY usecounts DESC;

–Résultat : Un plan est créé et consomme 40960 octets
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       (@id int)SELECT * FROM T_TEST WHERE id = @id

— Exécutons à nouveau la requête précedente mais cette fois-ci avec @id = 131

EXEC sp_executesql N'SELECT * FROM T_TEST WHERE id = @id',N'@id int',@id =131

–Puis affichons le cache avec REQ_CACHE_sp_executesql

–Résultat : il y a réutilisation du plan existant (usecounts = 2 pour le même plan d’exécution)
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  2  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       (@id int)SELECT * FROM T_TEST WHERE id = @id

——————————————
–Etape 5 : procédure stockée et cache
——————————————
–Créons la procédure stockée

CREATE PROCEDURE usp_test (@id int) AS
BEGIN
SELECT * FROM T_TEST WHERE id = @id
END

–Effaçons le cache de procédures

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

–Utilisons la procédure pour @id = 130

EXEC usp_test 130

–Exécutons la requête suivante (nommons la requête REQ_CACHE_usp_test) pour afficher le contenu du cache relatif à cette requête de test

SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like '%usp_test %'
AND c.objtype = 'Proc'
ORDER BY usecounts DESC;

–Résultat : un plan est créé et mobilise 40960 octets de mémoire
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       CREATE PROCEDURE usp_test (@id int) AS  BEGIN  SELECT * FROM T_TEST WHERE id = @id

— Exécutons à nouveau la procédure stockée mais cette fois-ci avec @id = 131

EXEC usp_test 131

–Puis affichons le cache avec REQ_CACHE_usp_test

–Résultat : il y a réutilisation du plan existant (usecounts = 2 pour le même plan d’exécution)
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  2  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       CREATE PROCEDURE usp_test (@id int) AS  BEGIN  SELECT * FROM T_TEST WHERE id = @id

——————————————
–Etape 6 : fonction et cache
——————————————
–Créons une fonction table

CREATE FUNCTION ufn_test(@id int)
RETURNS TABLE
AS RETURN
(
  SELECT * FROM T_TEST WHERE id = @id
)

–Effaçons le cache de procédures

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

–Utilisons la fonction table créée avec @id = 130

SELECT * FROM ufn_test (130)

–Exécutons la requête suivante (nommons la requête REQ_CACHE_ufn_test) pour afficher le contenu du cache relatif à cette

requête de test
SELECT c.usecounts
, c.cacheobjtype
, c.cacheobjtype
, c.size_in_bytes
, p.text
, qp.query_plan
FROM sys.dm_exec_cached_plans c  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) p
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE p.text like 'SELECT * FROM %ufn_test %'
ORDER BY usecounts DESC;

–Résultat : Un plan d’exécution est créé et mobilise 40960 octets de mémoire
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       SELECT * FROM ufn_test (130)

— Exécutons à nouveau la fonction ufn_test stockée mais cette fois-ci avec @id = 131

SELECT * FROM  ufn_test (131)

–Puis affichons le cache avec REQ_CACHE_ufn_test

–Résultat : il n’y a pas réutilisation du plan existant, un nouveau plan est créé et mobilise aussi 40960 octets de mémoire
————————–

usecounts ......       cacheobjtype ......      cacheobjtype   ......         size_in_bytes  ......           text                              
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       SELECT * FROM ufn_test (131)
  1  ............  Compiled Plan  ............           Compiled Plan   ......           40960    ......       SELECT * FROM ufn_test (130)

– Que faut-il retenir de cette démo ?

Pour une bonne utilisation du cache de procédures, il est conseillé :

–> d’écrire de la même manière les requêtes identiques (respecter la casse et les espaces)

–> d’utiliser des procédures stockées pour exécuter les instructions SQL

–> d’utiliser sp_executesql pour exécuter des commandes SQL

——————————————————————————————————
–Autres idées en rapport avec le cache :
1 .) Pour éviter la suppression totale du cache de procédure d’une instance, j’ai écrit une procédure qui permet d’effacer finement un plan d’exécution associé à la requête donnée sans toucher au cache de l’instance

2.) Pour Auditer le cache de procédures

——————————————————————————————————
–Lectures saines en rapport avec le sujet :

SQLPro — Elsuket — Elsuket
——————————————————————————————————

Vos commentaires sont les bienvenus

——————————————————————————-
Auteur : Etienne ZINZINDOHOUE
——————————————————————————-