{"id":780,"date":"2013-01-17T03:48:51","date_gmt":"2013-01-17T02:48:51","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=780"},"modified":"2026-05-02T14:31:00","modified_gmt":"2026-05-02T12:31:00","slug":"mauvais-plan-dexecution","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/mauvais-plan-dexecution\/","title":{"rendered":"Mauvais plan d’ex\u00e9cution ?"},"content":{"rendered":"

Aujourd’hui, je vais aborder une situation assez couramment rencontr\u00e9e : les plans d’ex\u00e9cution de proc\u00e9dures qui semblent partir de travers. Votre client vous appelle parce que ses traitements semblent \u00eatre au ralenti et pourtant, lorsque vous r\u00e9cup\u00e9rez sur votre serveur de tests une copie de sa base de donn\u00e9es, tout semble fonctionner \u00e0 merveille…<\/p>\n

Une fois pass\u00e9e l’\u00e9tape de contr\u00f4le des diverses configurations au niveau du serveur (niveau maximum de parall\u00e9lisme, m\u00e9moire, …), il se peut qu’on arrive \u00e0 vouloir observer les plans d’ex\u00e9cution des proc\u00e9dures stock\u00e9es. Pour cela, SQL Server met \u00e0 notre disposition un ensemble de vues et de fonctions syst\u00e8mes (appel\u00e9es Data Management Views \/ Functions) qui nous permettent de d\u00e9terminer les plans d’ex\u00e9cution actuellement en vigueur.<\/p>\n

select qp.query_plan\r\nfrom sys.dm_exec_cached_plans cp\r\ncross apply sys.dm_exec_query_plan(cp.plan_handle) qp\r\njoin sys.procedures p on p.object_id=qp.objectid\r\nwhere p.name='Traitement'<\/pre>\n
Pour l’exemple, consid\u00e9rons un jeu de donn\u00e9es et une proc\u00e9dure stock\u00e9e d\u00e9finis de la mani\u00e8re suivante :<\/p>\n
CREATE TABLE TableA (Groupe int, Identifiant bigint)\r\nCREATE TABLE TableB (Groupe int, Identifiant bigint)\r\nCREATE UNIQUE CLUSTERED INDEX IA ON TableA(Groupe,Identifiant)\r\nCREATE UNIQUE CLUSTERED INDEX IB ON TableB(Groupe,Identifiant)\r\n\r\n;WITH Liste(Id) AS\r\n(SELECT 1\r\nUNION ALL\r\nSELECT Id+1 FROM Liste WHERE Id<1000000)\r\nINSERT INTO TableA\r\nSELECT g,Id\r\nfrom Liste\r\ncross join (values(1),(2)) groupes(g)\r\noption (maxrecursion 0)\r\n\r\ninsert into TableB (Groupe,Identifiant)\r\nselect Groupe,Identifiant\r\nfrom TableA\r\nwhere Groupe=1\r\n\r\nCREATE TYPE TypeListe as table (Id bigint)\r\nGO\r\n\r\nCREATE PROCEDURE Traitement\r\n@Liste dbo.TypeListe READONLY,\r\n@Groupe int\r\nAS\r\nBEGIN\r\nCREATE TABLE #Liste (Id bigint)\r\nCREATE TABLE #Id (Id bigint)\r\n\r\nINSERT INTO #Id(Id)\r\nselect Id+Offset\r\nfrom @Liste\r\ncross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)\r\n\r\ninsert into #Liste(Id)\r\nselect t.Identifiant\r\nfrom #Id l\r\njoin TableA t on t.Identifiant =l.Id\r\nWHERE t.Groupe=@Groupe\r\n\r\nselect count(*) as Nb\r\nfrom #Liste l\r\njoin TableB t on t.Identifiant=l.Id\r\nWHERE t.Groupe=@Groupe\r\n\r\nEND<\/pre>\n
Dans ce cas d’exemple, lan\u00e7ons un premier appel de la proc\u00e9dure stock\u00e9e et observons le plan d’ex\u00e9cution du dernier SELECT.
\n\"PlanG2\"<\/a>Nous avons travaill\u00e9 dans une partie de la table TableB qui ne contient aucun enregistrement (Groupe=2), et les statistiques ont donc conduit \u00e0 un plan d’ex\u00e9cution effectuant d’abord un Index Seek sur cette table (avec\u00a0pr\u00e9dicat sur la colonne Groupe) puis ensuite une jointure avec la table temporaire #Liste.<\/p>\n
Le probl\u00e8me est que ce plan est stock\u00e9 en cache.<\/p>\n
\"LecturePlanCache1\"<\/a><\/p>\n
\"LecturePlanCache2\"<\/a><\/p>\n
A priori, \u00e7a parait sympa, puisque lors de la prochaine ex\u00e9cution de la proc\u00e9dure, le plan d’ex\u00e9cution ne sera plus \u00e0 d\u00e9terminer, 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\u00e9es est diff\u00e9rent, la r\u00e9-utilisation de ce plan d’ex\u00e9cution peut se r\u00e9v\u00e9ler tr\u00e8s probl\u00e9matique en termes de performances !<\/p>\n
\"DureeMauvaisPlan\"<\/a><\/span><\/p>\n
Notre proc\u00e9dure stock\u00e9e s’ex\u00e9cute en 6 secondes ! Et si nous for\u00e7ons un vidage du cache avec ex\u00e9cution :<\/p>\n
\"Groupe1DureeCourte\"<\/a><\/p>\n
On obtient alors\u00a0\u00e0 peine\u00a05\u00a0milli\u00e8mes de seconde, dur\u00e9e de retour des informations \u00e0 SSMS comprise\u00a0!<\/p>\n
En effet, le plan d’ex\u00e9cution utilis\u00e9 est alors diff\u00e9rent, et beaucoup plus adapt\u00e9 \u00e0 la cardinalit\u00e9 des donn\u00e9es pour le groupe choisi.<\/p>\n
\"CacheOptimalGroupe2\"<\/a><\/p>\n
Il peut donc \u00eatre int\u00e9ressant, lorsque les contextes d’appel varient de mani\u00e8re \u00ab\u00a0sournoise\u00a0\u00bb, de ne pas chercher \u00e0 m\u00e9moriser les plans d’ex\u00e9cution, et de pr\u00e9f\u00e9rer un faible surco\u00fbt \u00e0 chaque appel (le temps de red\u00e9terminer le plan optimal) au risque de \u00ab\u00a0d\u00e9part en vrille\u00a0\u00bb dans certains cas.<\/p>\n
ALTER PROCEDURE Traitement\r\n@Liste dbo.TypeListe READONLY,\r\n@Groupe int\r\nWITH RECOMPILE\r\nAS\r\nBEGIN\r\nCREATE TABLE #Liste (Id bigint)\r\nCREATE TABLE #Id (Id bigint)\r\n\r\nINSERT INTO #Id(Id)\r\nselect Id+Offset\r\nfrom @Liste\r\ncross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)\r\n\r\ninsert into #Liste(Id)\r\nselect t.Identifiant\r\nfrom #Id l\r\njoin TableA t on t.Identifiant =l.Id\r\nWHERE t.Groupe=@Groupe\r\n\r\nselect count(*) as Nb\r\nfrom #Liste l\r\njoin TableB t on t.Identifiant=l.Id\r\nWHERE t.Groupe=@Groupe\r\n\r\nEND<\/pre>\n
\"PlanAdapteRecompile\"<\/a><\/p>\n
La dur\u00e9e d’ex\u00e9cution est l\u00e0 presque instantann\u00e9e.<\/p>\n
Attention toutefois \u00e0 ne pas syst\u00e9matiser l’utilisation du mot cl\u00e9 \u00ab\u00a0WITH RECOMPILE\u00a0\u00bb, car sur les proc\u00e9dures tr\u00e8s fr\u00e9quemment appel\u00e9es, le produit \u00ab\u00a0nombre d’ex\u00e9cutions\u00a0\u00bb par \u00ab\u00a0dur\u00e9e de recompilation\u00a0\u00bb risque de devenir tr\u00e8s probl\u00e9matique. Pour conna\u00eetre le nombre d’ex\u00e9cution des proc\u00e9dures stock\u00e9es, une vue syst\u00e8me est disponible :<\/p>\n
select p.name,ps.execution_count\r\nfrom sys.dm_exec_procedure_stats ps\r\njoin sys.procedures p on p.object_id=ps.object_id<\/pre>\n
Cette vue affiche le nombre d’ex\u00e9cution de la proc\u00e9dure depuis la derni\u00e8re r\u00e9-initialisation des statistiques (en g\u00e9n\u00e9ral le red\u00e9marrage de l’instance SQL, ou la cr\u00e9ation de la proc\u00e9dure). Dans les cas o\u00f9 la proc\u00e9dure est tr\u00e8s souvent appel\u00e9e, il peut \u00eatre pr\u00e9f\u00e9rable d’agir directement sur la mani\u00e8re dont l’instruction probl\u00e9matique est impl\u00e9ment\u00e9e, en for\u00e7ant quelques \u00e9l\u00e9ments du plan d’ex\u00e9cution. Par exemple, la proc\u00e9dure peut \u00eatre modifi\u00e9e de la mani\u00e8re suivante :<\/p>\n
ALTER PROCEDURE Traitement\r\n@Liste dbo.TypeListe READONLY,\r\n@Groupe int\r\nAS\r\nBEGIN\r\nCREATE TABLE #Liste (Id bigint)\r\nCREATE TABLE #Id (Id bigint)\r\n\r\nINSERT INTO #Id(Id)\r\nselect Id+Offset\r\nfrom @Liste\r\ncross join (values (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5)) Delta(Offset)\r\n\r\ninsert into #Liste(Id)\r\nselect t.Identifiant\r\nfrom #Id l\r\njoin TableA t on t.Identifiant =l.Id\r\nWHERE t.Groupe=@Groupe\r\n\r\nselect count(*) as Nb\r\nfrom #Liste l\r\njoin TableB t on t.Identifiant=l.Id\r\nWHERE t.Groupe=@Groupe\r\nOPTION (FORCE ORDER)\r\n\r\nEND<\/pre>\n
Au final, voici la mesure des diff\u00e9rents temps de r\u00e9ponse (en millisecondes):<\/p>\n\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n
<\/td>\nProc\u00e9dure normale<\/strong><\/td>\nAvec WITH RECOMPILE<\/strong><\/td>\nAvec FORCE ORDER<\/strong><\/td>\n<\/tr>\n
Execution sur groupe 1<\/td>\nPremi\u00e8re (cr\u00e9ation plan)<\/td>\n70<\/span><\/td>\n73<\/span><\/td>\n69<\/span><\/td>\n<\/tr>\n
Suivant plan adapt\u00e9<\/td>\n41<\/span><\/td>\n\u00a0NC<\/td>\n40<\/span><\/td>\n<\/tr>\n
Suivant plan non adapt\u00e9<\/td>\n63<\/span><\/td>\n\u00a0NC<\/td>\n9<\/span><\/td>\n<\/tr>\n
Execution sur groupe 2<\/td>\nPremi\u00e8re (cr\u00e9ation plan)<\/td>\n18<\/span><\/td>\n18<\/span><\/td>\n20<\/span><\/td>\n<\/tr>\n
Suivant plan adapt\u00e9<\/td>\n4<\/span><\/td>\n\u00a0NC<\/td>\n4<\/span><\/td>\n<\/tr>\n
Suivant plan non adapt\u00e9<\/td>\n7122<\/span><\/td>\n\u00a0NC<\/td>\n78<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
On voit que tout est question de compromis, entre laisser le syst\u00e8me trouver la solution optimale, ou bien le guider sur une solution pas n\u00e9cessairement optimale \u00e0 tous les coups et qui risque potentiellement d’\u00eatre \u00e0 retoucher lorsque le fonctionnel \u00e9voluera. Tout un travail …<\/p>\n","protected":false},"excerpt":{"rendered":"
Aujourd’hui, je vais aborder une situation assez couramment rencontr\u00e9e : les plans d’ex\u00e9cution de proc\u00e9dures qui semblent partir de travers. Votre client vous appelle parce que ses traitements semblent \u00eatre au ralenti et pourtant, lorsque vous r\u00e9cup\u00e9rez sur votre serveur … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-780","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/780","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=780"}],"version-history":[{"count":34,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/780\/revisions"}],"predecessor-version":[{"id":1937,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/780\/revisions\/1937"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=780"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=780"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=780"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}