{"id":8983,"date":"2022-04-06T07:34:25","date_gmt":"2022-04-06T06:34:25","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=8983"},"modified":"2022-04-08T09:46:34","modified_gmt":"2022-04-08T08:46:34","slug":"query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/","title":{"rendered":"Query Store : bloquer les r\u00e9gressions de plans d’ex\u00e9cution et Capture Mode"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Depuis SQL Server 2016, Microsoft propose dans toutes les \u00e9ditions un outil d’analyse de performance qui peut sembler un petit peu obscure \u00e0 premier abord.<\/p>\n

Il ne remplace pas r\u00e9ellement le Data Collection, qui existe toujours, mais propose une approche moins intrusive que celui-ci. Il n’y a pas de jobs SSIS, il n’y a pas besoin d’une base de donn\u00e9es d\u00e9di\u00e9e et la consommation de ressource est moins \u00e9lev\u00e9e.<\/p>\n

Le but de cet article n’est pas de documenter l’int\u00e9gralit\u00e9 de son fonctionnement, mais de faire un focus sur la simplicit\u00e9 \u00e0 forcer un plan d’ex\u00e9cution.<\/p>\n

Pour rappel, on active le Query Store par base de donn\u00e9es :<\/p>\n

\"\"<\/p>\n

 <\/p>\n

On fera attention au “Capture Mode” : avant SQL Server 2019, le mode par d\u00e9faut est “all”. Cela veut dire que toutes les requ\u00eates seront catalogu\u00e9es, y compris si elles ne sont pas ex\u00e9cut\u00e9es fr\u00e9quemment et ont un temps de compilation\/ex\u00e9cution ridicule. On peut vite arriver \u00e0 des volumes captur\u00e9s tr\u00e8s elev\u00e9s. Et le code pour exploiter les rapports utilise des UDF, se retrouve donc mono-thread\u00e9, et le temps d’affichage des rapports peut devenir abominablement long.<\/p>\n

Le mode par d\u00e9faut “all” a donc \u00e9t\u00e9 remplac\u00e9 par le “auto” depuis 2019. Celui-ci se focalise sur les requ\u00eates ex\u00e9cut\u00e9es fr\u00e9quemment et avec des temps de compilation\/ex\u00e9cution longs. Si vous ne savez pas o\u00f9 vous mettez les pieds : mettez le “auto” !<\/strong><\/p>\n

Maintenant, pour identifier les r\u00e9gressions, il y a plusieurs approches. Traditionnellement, on approche les probl\u00e8mes de performances par le “Top Resource Consuming Queries”.\u00a0 Il est aussi possible de passer par le rapport “Regressed Queries” mais d’exp\u00e9rience il se r\u00e9v\u00e8le malheureusement moins efficace.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Dans l’exemple ci-dessus, on voit donc une requ\u00eate ayant consomm\u00e9 beaucoup de temps CPU. Il y a 4 plans d’ex\u00e9cutions identifi\u00e9s avec principalement 2 types. On a un type “rapide” et un type “lent”. On a beau regard\u00e9 le d\u00e9tail du plan d’ex\u00e9cution “lent” le subtree cost n’est pas radicalement diff\u00e9rent. Nous avons \u00e0 faire un probl\u00e8me d’estimation de cardinalit\u00e9 dans ce plan, \u00e0 l’int\u00e9rieur d’un traitement batch.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Dans l’id\u00e9al, je pr\u00e9f\u00e9rerais r\u00e9soudre le probl\u00e8me de cardinalit\u00e9 proprement, mais les d\u00e9lais de correction du code contrarient les besoins de l’\u00e9quipe m\u00e9tier. Il reste donc une solution : fixer le plan d’ex\u00e9cution manuellement.<\/p>\n

Pour cela, on choisi le plan d’ex\u00e9cution parmi les 4 qui nous int\u00e9resse, et on clique sur “force plan”<\/p>\n

\"\"<\/p>\n

 <\/p>\n

On voit donc que la requ\u00eate dispose d’un identifiant, et son plan \u00e9galement. Cela nous permettra de la retrouver \u00e0 l’avenir. On peut \u00e9galement cliquer sur le bouton “track query” pour la retrouver plus facilement \u00e0 l’avenir dans le rapport “tracked queries”.<\/p>\n

On voit \u00e9galement un tick sur le plan d’ex\u00e9cution dans le rapport des “top resource consuming queries” ainsi que la possibilit\u00e9 de cliquer sur “unforce plan”<\/p>\n

\"\"<\/p>\n

Et voil\u00e0 ! Nous avons forc\u00e9 le plan d’ex\u00e9cution d’une requ\u00eate probl\u00e9matique.<\/p>\n

Le but d\u00e9sormais est plut\u00f4t de corriger la source de l’erreur de cardinalit\u00e9 car maintenir la connaissance de cette op\u00e9ration en phase d’exploitation n’est pas chose ais\u00e9e. Il vaut mieux avoir un code et un mod\u00e8le de donn\u00e9es propre plut\u00f4t que de jouer sur les for\u00e7ages de plan r\u00e9guli\u00e8rement.<\/p>\n

 <\/p>\n

Plus d’information sur le Query Store et ses bonnes pratiques :<\/p>\n

https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/best-practice-with-the-query-store?view=sql-server-ver15<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"

Depuis SQL Server 2016, Microsoft propose dans toutes les \u00e9ditions un outil d’analyse de performance qui peut sembler un petit peu obscure \u00e0 premier abord. Il ne remplace pas r\u00e9ellement le Data Collection, qui existe toujours, mais propose une approche… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":8996,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[221,399,302,398,122,400],"class_list":["post-8983","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-performance","tag-plan-dexecution","tag-query-store","tag-server","tag-sql","tag-subtree-cost"],"yoast_head":"\nQuery Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Depuis SQL Server 2016, Microsoft propose dans toutes les \u00e9ditions un outil d’analyse de performance qui peut sembler un petit peu obscure \u00e0 premier abord. Il ne remplace pas r\u00e9ellement le Data Collection, qui existe toujours, mais propose une approche… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-06T06:34:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-08T08:46:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/titre.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1087\" \/>\n\t<meta property=\"og:image:height\" content=\"594\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Capdata team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Capdata team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\"},\"author\":{\"name\":\"Capdata team\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\"},\"headline\":\"Query Store : bloquer les r\u00e9gressions de plans d’ex\u00e9cution et Capture Mode\",\"datePublished\":\"2022-04-06T06:34:25+00:00\",\"dateModified\":\"2022-04-08T08:46:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\"},\"wordCount\":608,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"performance\",\"Plan d'ex\u00e9cution\",\"Query Store\",\"Server\",\"sql\",\"subtree cost\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\",\"name\":\"Query Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2022-04-06T06:34:25+00:00\",\"dateModified\":\"2022-04-08T08:46:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store : bloquer les r\u00e9gressions de plans d’ex\u00e9cution et Capture Mode\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\",\"name\":\"Capdata team\",\"sameAs\":[\"https:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/","og_locale":"fr_FR","og_type":"article","og_title":"Query Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG","og_description":"Depuis SQL Server 2016, Microsoft propose dans toutes les \u00e9ditions un outil d’analyse de performance qui peut sembler un petit peu obscure \u00e0 premier abord. Il ne remplace pas r\u00e9ellement le Data Collection, qui existe toujours, mais propose une approche… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2022-04-06T06:34:25+00:00","article_modified_time":"2022-04-08T08:46:34+00:00","og_image":[{"width":1087,"height":594,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/titre.png","type":"image\/png"}],"author":"Capdata team","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Capdata team","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/"},"author":{"name":"Capdata team","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9"},"headline":"Query Store : bloquer les r\u00e9gressions de plans d’ex\u00e9cution et Capture Mode","datePublished":"2022-04-06T06:34:25+00:00","dateModified":"2022-04-08T08:46:34+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/"},"wordCount":608,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["performance","Plan d'ex\u00e9cution","Query Store","Server","sql","subtree cost"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/","url":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/","name":"Query Store : bloquer les r\u00e9gressions de plans d'ex\u00e9cution et Capture Mode - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2022-04-06T06:34:25+00:00","dateModified":"2022-04-08T08:46:34+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/query-store-bloquer-les-regressions-de-plans-dexecution-et-capture-mode\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Query Store : bloquer les r\u00e9gressions de plans d’ex\u00e9cution et Capture Mode"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9","name":"Capdata team","sameAs":["https:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=8983"}],"version-history":[{"count":4,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8983\/revisions"}],"predecessor-version":[{"id":8995,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8983\/revisions\/8995"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/8996"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=8983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=8983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=8983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}