Aujourd’hui, je vais aborder une situation assez couramment rencontrée : les plans d’exécution de procédures qui semblent partir de travers. Votre client vous appelle parce que ses traitements semblent être au ralenti et pourtant, lorsque vous récupérez sur votre serveur de tests une copie de sa base de données, tout semble fonctionner à merveille…

Une fois passée l’étape de contrôle des diverses configurations au niveau du serveur (niveau maximum de parallélisme, mémoire, …), il se peut qu’on arrive à vouloir observer les plans d’exécution des procédures stockées. Pour cela, SQL Server met à notre disposition un ensemble de vues et de fonctions systèmes (appelées Data Management Views / Functions) qui nous permettent de déterminer les plans d’exécution actuellement en vigueur.

select qp.query_plan
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
join sys.procedures p on p.object_id=qp.objectid
where p.name='Traitement'

Pour l’exemple, considérons un jeu de données et une procédure stockée définis de la manière suivante :

CREATE TABLE TableA (Groupe int, Identifiant bigint)
CREATE TABLE TableB (Groupe int, Identifiant bigint)
CREATE UNIQUE CLUSTERED INDEX IA ON TableA(Groupe,Identifiant)
CREATE UNIQUE CLUSTERED INDEX IB ON TableB(Groupe,Identifiant)

;WITH Liste(Id) AS
(SELECT 1
UNION ALL
SELECT Id+1 FROM Liste WHERE Id<1000000)
INSERT INTO TableA
SELECT g,Id
from Liste
cross join (values(1),(2)) groupes(g)
option (maxrecursion 0)

insert into TableB (Groupe,Identifiant)
select Groupe,Identifiant
from TableA
where Groupe=1

CREATE TYPE TypeListe as table (Id bigint)
GO

CREATE PROCEDURE Traitement
@Liste dbo.TypeListe READONLY,
@Groupe int
AS
BEGIN
CREATE TABLE #Liste (Id bigint)
CREATE TABLE #Id (Id bigint)

INSERT INTO #Id(Id)
select Id+Offset
from @Liste
cross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)

insert into #Liste(Id)
select t.Identifiant
from #Id l
join TableA t on t.Identifiant =l.Id
WHERE t.Groupe=@Groupe

select count(*) as Nb
from #Liste l
join TableB t on t.Identifiant=l.Id
WHERE t.Groupe=@Groupe

END

Dans ce cas d’exemple, lançons un premier appel de la procédure stockée et observons le plan d’exécution du dernier SELECT.
Nous avons travaillé dans une partie de la table TableB qui ne contient aucun enregistrement (Groupe=2), et les statistiques ont donc conduit à un plan d’exécution effectuant d’abord un Index Seek sur cette table (avec prédicat sur la colonne Groupe) puis ensuite une jointure avec la table temporaire #Liste.

Le problème est que ce plan est stocké en cache.

A priori, ça parait sympa, puisque lors de la prochaine exécution de la procédure, le plan d’exécution ne sera plus à déterminer, et l’appel n’en sera que plus rapide. En tout cas si l’appel se fait dans des conditions similaires … En effet, lorsque le contexte de données est différent, la ré-utilisation de ce plan d’exécution peut se révéler très problématique en termes de performances !

Notre procédure stockée s’exécute en 6 secondes ! Et si nous forçons un vidage du cache avec exécution :

On obtient alors à peine 5 millièmes de seconde, durée de retour des informations à SSMS comprise !

En effet, le plan d’exécution utilisé est alors différent, et beaucoup plus adapté à la cardinalité des données pour le groupe choisi.

Il peut donc être intéressant, lorsque les contextes d’appel varient de manière « sournoise », de ne pas chercher à mémoriser les plans d’exécution, et de préférer un faible surcoût à chaque appel (le temps de redéterminer le plan optimal) au risque de « départ en vrille » dans certains cas.

ALTER PROCEDURE Traitement
@Liste dbo.TypeListe READONLY,
@Groupe int
WITH RECOMPILE
AS
BEGIN
CREATE TABLE #Liste (Id bigint)
CREATE TABLE #Id (Id bigint)

INSERT INTO #Id(Id)
select Id+Offset
from @Liste
cross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)

insert into #Liste(Id)
select t.Identifiant
from #Id l
join TableA t on t.Identifiant =l.Id
WHERE t.Groupe=@Groupe

select count(*) as Nb
from #Liste l
join TableB t on t.Identifiant=l.Id
WHERE t.Groupe=@Groupe

END

La durée d’exécution est là presque instantannée.

Attention toutefois à ne pas systématiser l’utilisation du mot clé « WITH RECOMPILE », car sur les procédures très fréquemment appelées, le produit « nombre d’exécutions » par « durée de recompilation » risque de devenir très problématique. Pour connaître le nombre d’exécution des procédures stockées, une vue système est disponible :

select p.name,ps.execution_count
from sys.dm_exec_procedure_stats ps
join sys.procedures p on p.object_id=ps.object_id

Cette vue affiche le nombre d’exécution de la procédure depuis la dernière ré-initialisation des statistiques (en général le redémarrage de l’instance SQL, ou la création de la procédure). Dans les cas où la procédure est très souvent appelée, il peut être préférable d’agir directement sur la manière dont l’instruction problématique est implémentée, en forçant quelques éléments du plan d’exécution. Par exemple, la procédure peut être modifiée de la manière suivante :

ALTER PROCEDURE Traitement
@Liste dbo.TypeListe READONLY,
@Groupe int
AS
BEGIN
CREATE TABLE #Liste (Id bigint)
CREATE TABLE #Id (Id bigint)

INSERT INTO #Id(Id)
select Id+Offset
from @Liste
cross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)

insert into #Liste(Id)
select t.Identifiant
from #Id l
join TableA t on t.Identifiant =l.Id
WHERE t.Groupe=@Groupe

select count(*) as Nb
from #Liste l
join TableB t on t.Identifiant=l.Id
WHERE t.Groupe=@Groupe
OPTION (FORCE ORDER)

END

Au final, voici la mesure des différents temps de réponse (en millisecondes):

On voit que tout est question de compromis, entre laisser le système trouver la solution optimale, ou bien le guider sur une solution pas nécessairement optimale à tous les coups et qui risque potentiellement d’être à retoucher lorsque le fonctionnel évoluera. Tout un travail …