Meta tags:
description=Chapitre 09 - Optimiser les procédures stockées;

Headings (most frequently used words):

la, cache, des, les, recompilations, requêtes, le, plans, plan, optimiser, procédures, stockées, procédure, stockée, messages, done_in_proc, maîtriser, compilation, paramètres, typiques, forcer, recompilation, automatiques, ad, hoc, paramétrer, sql, dynamique, pression, sur, de, sp_recompile, keep, et, keepfixed, tracer, réutilisation, adhoc, tag, cloud, categories,

Text of the page (most frequently used words):
est (125), des (112), les (108), une (104), sql (87), plan (83), #procédure (78), exécution (75), dans (73), que (65), vous (62), cache (62), pour (60), qui (57), server (53), par (52), select (52), lastname (48), sys (47), from (45), nous (44), requête (39), plans (38), pas (37), sur (36), plus (34), person (32), sont (31), procédures (31), avec (30), requêtes (30), cette (29), where (28), table (27), set (26), code (25), plan_handle (25), contact (25), recompilations (25), peut (24), même (23), stockée (22), comme (22), dbo (22), pouvez (22), recompilation (22), tables (22), stockées (21), deux (21), option (21), donc (21), être (21), cas (20), exemple (19), serveur (19), valeur (19), compilation (19), cela (18), appel (17), paramètre (17), dbcc (16), options (16), elle (15), paramètres (15), objet (15), ces (15), firstname (15), lors (15), instruction (14), mémoire (14), lorsque (14), vue (14), chaque (13), plusieurs (13), sera (13), index (13), avons (13), session (13), exec (12), données (12), type (12), valeurs (12), peuvent (12), create (12), apply (11), dm_exec_cached_plans (11), nvarchar (11), commande (11), colonne (11), toutes (11), base (11), elles (11), dm_exec_sql_text (10), test (10), première (10), aide (10), aux (10), nocount (10), optimisation (10), temporaires (10), premier (10), like (10), procedure (10), client (9), fois (9), bien (9), size_in_bytes (9), permet (9), mais (9), optimiser (9), généré (9), aussi (9), recherche (9), défaut (9), soit (9), très (9), objets (9), stocké (9), tout (9), sql_handle (9), batch (9), end (9), utilisation (9), recompile (9), changé (9), begin (9), réutilisation (8), faire (8), usecounts (8), execute (8), taille (8), car (8), différents (8), dire (8), scan (8), compilé (8), with (8), également (8), dbid (8), performances (8), votre (7), possible (7), syntaxe (7), dm_exec_query_stats (7), ackerman (7), ainsi (7), for (7), alter (7), schéma (7), voir (7), cross (7), objtype (7), colonnes (7), emailaddress (7), besoin (6), fonctionnalité (6), text (6), paramétrer (6), optimize (6), hoc (6), utilisé (6), non (6), alors (6), paramétrage (6), provoquer (6), reste (6), comportement (6), autres (6), résultat (6), simplement (6), parce (6), concat_null_yields_null (6), éviter (6), changer (6), vos (6), user (6), temporaire (6), statistiques (6), lignes (6), variable (6), object_id (6), tous (5), permettent (5), exécutions (5), freeproccache (5), dynamique (5), quelle (5), système (5), appelée (5), types (5), simple (5), parameterization (5), adventureworks (5), mécanisme (5), hachage (5), clustered (5), ici (5), seek (5), fait (5), nombre (5), différentes (5), leur (5), fig (5), figure (5), toute (5), différent (5), allons (5), partir (5), événements (5), toujours (5), doit (5), travail (5), contexte (5), été (5), pourquoi (5), cacheobjtype (5), compilés (5), instructions (5), laquelle (5), depuis (5), gérer (5), trace (5), indicateur (5), keep (5), distribution (5), notamment (5), forcer (5), namepart (5), parameter (5), sniffing (5), temps (5), getcontactbylastname (5), revanche (5), objectid (5), message (5), sql_modules (5), messages (5), done_in_proc (5), souvent (4), utile (4), moins (4), trois (4), autant (4), sp_executesql (4), join (4), outer (4), declare (4), 8000 (4), reconfigure (4), avez (4), réutiliser (4), important (4), dessus (4), suivante (4), état (4), quel (4), comportent (4), sous (4), mldr (4), seule (4), contactid (4), séparément (4), 2005 (4), signifie (4), ceci (4), adhoc (4), entrées (4), dont (4), seulement (4), déjà (4), réseau (4), top (4), middlename (4), ils (4), fonction (4), audit (4), nom (4), son (4), utilisateurs (4), changement (4), etc (4), faut (4), raison (4), ont (4), profiler (4), isabelle (4), paul (4), else (4), utilisateur (4), tracer (4), keepfixed (4), deuxième (4), sans (4), null (4), vide (4), créée (4), loin (4), compte (4), réutilisé (4), solution (4), nameparttype (4), complexes (4), getcontactsparameter (4), appels (4), bonne (4), passé (4), envoyé (4), commandes (4), pression (4), vérification (4), administration (4), transactions (4), rudi (3), bruchez (3), expertise (3), accès (3), envoyer (3), coûteuse (3), allen (3), varchar (3), lieu (3), utiliser (3), voici (3), sp_configure (3), nommée (3), paramétrées (3), 2008 (3), utilisant (3), rien (3), implique (3), puisque (3), générer (3), nécessaire (3), forcé (3), database (3), casse (3), passée (3), vraiment (3), retourner (3), ligne (3), création (3), fortement (3), cachées (3), passés (3), maintenant (3), auto (3), dépend (3), cet (3), correspondance (3), différences (3), provoque (3), insertion (3), constater (3), lorsqu (3), envoyée (3), hachages (3), moindre (3), différence (3), rapport (3), gestion (3), quelques (3), off (3), cstmt (3), entre (3), référence (3), générés (3), mêmes (3), consistants (3), cours (3), nouveau (3), order (3), and (3), événement (3), voyez (3), int (3), résolution (3), abord (3), sessions (3), selon (3), informations (3), agit (3), stockage (3), cachestore_sqlcp (3), cachestore_objcp (3), single_pages_kb (3), caches (3), obtenir (3), enfin (3), hash (3), dm_os_memory_cache_entries (3), object (3), fonctions (3), contient (3), existe (3), résultats (3), déférée (3), début (3), structure (3), cardinalité (3), col (3), modifications (3), êtes (3), dues (3), aura (3), expressions (3), variables (3), ensuite (3), testtemptable (3), suppression (3), meilleure (3), modification (3), automatiques (3), long (3), instance (3), seront (3), sp_recompile (3), utiles (3), problème (3), travers (3), moyenne (3), lastnamestart (3), locale (3), abercrombie (3), getcontactslocalvariable (3), mylastname (3), chose (3), ssms (3), dm_exec_query_plan (3), coûteux (3), journal (3), errorlog (3), vider (3), version (3), todo (3), configuration (3), démarrage (3), retourne (3), existence (3), service (3), systèmes (3), maîtriser (3), 512 (3), performance (3), livre (3), docs (3), maintenance (3), transaction (3), alwayson (3), diagnostic (3), journaux (3), noter (2), bibliothèques (2), systématiquement (2), multiples (2), préférez (2), paramétrée (2), solutions (2), workloads (2), advanced (2), principalement (2), activer (2), exécutez (2), opération (2), possibilité (2), parameters (2), ado (2), net (2), recherchée (2), applique (2), maximum (2), génération (2), passant (2), littérales (2), forced (2), disposez (2), éventuellement (2), force (2), retours (2), sensible (2), notons (2), répondre (2), essaie (2), certaines (2), garantie (2), clé (2), primaire (2), était (2), unique (2), dessous (2), avant (2), certains (2), 2000 (2), tinyint (2), valable (2), constantes (2), attention (2), quatre (2), pouvons (2), ordre (2), trouver (2), sqlmgr (2), représentent (2), cachée (2), invalide (2), puisqu (2), produit (2), collation (2), entrée (2), potentiellement (2), serait (2), intéressante (2), avantages (2), trafic (2), sécurité (2), contraintes (2), particulier (2), entier (2), contenu (2), via (2), savoir (2), security (2), méthodes (2), connexion (2), telles (2), odbc (2), surtout (2), intérieur (2), recompiler (2), modifie (2), ansi_nulls (2), dateformat (2), littéraux (2), phase (2), comprendre (2), recalcul (2), attribute (2), dm_exec_plan_attributes (2), vérifier (2), baser (2), trouvé (2), après (2), trouverez (2), revert (2), current_user (2), grant (2), testid (2), default_schema (2), humanresources (2), appartenant (2), imaginons (2), manager (2), total (2), then (2), when (2), grouping (2), case (2), 1024 (2), versions (2), compiled (2), instances (2), rapidement (2), seul (2), déclencheur (2), cachestore_phdr (2), dm_os_memory_cache_counters (2), name (2), intéressent (2), execution (2), buckets (2), déclencheurs (2), environnement (2), query (2), eventsubclass (2), déclenchée (2), suivantes (2), nouvelles (2), notre (2), placer (2), diminue (2), fréquence (2), seuils (2), modifiées (2), identiques (2), changements (2), modifier (2), étant (2), assez (2), devez (2), inutiles (2), déclenche (2), normal (2), exécutons (2), default (2), nbinserts (2), smallint (2), prendre (2), mauvaise (2), effectuent (2), produisent (2), référencent (2), généralement (2), mauvaises (2), éléments (2), inutilement (2), seuil (2), recompilée (2), structures (2), ajout (2), raisons (2), entraînant (2), recalculs (2), lui (2), conserver (2), rester (2), modifié (2), utilisée (2), malheureusement (2), créer (2), générique (2), prenons (2), unknown (2), façon (2), vaut (2), mieux (2), coût (2), seconde (2), utilité (2), indiquée (2), manuellement (2), stratégie (2), cpu (2), reads (2), prend (2), connue (2), voyons (2), près (2), 000 (2), moteur (2), expliquer (2), créé (2), compilée (2), calculé (2), autre (2), examinons (2), opérateur (2), 911 (2), db_id (2), query_plan (2), créons (2), domaine (2), qualité (2), typiques (2), erreur (2), information (2), production (2), blog (2), évite (2), flushprocindb (2), entendu (2), jusqu (2), dropcleanbuffers (2), problèmes (2), avoir (2), memory_object_address (2), original_cost (2), current_cost (2), object_name (2), object_schema_name (2), db_name (2), idée (2), observer (2), étendus (2), verrons (2), eux (2), appelle (2), partie (2), privilèges (2), différée (2), definition (2), retrouver (2), définition (2), uspgetbillofmaterials (2), grâce (2), page (2), propriété (2), envoi (2), 2025 (2), shrink (2), sargabilité (2), installation (2), architecture (2), ressources (2), compteurs (2), analyser (2), sauvegardes (2), always (2), tempdb (2), français (2), droits, réservés, 2026, services, contactez, moi, parlons, projet, similaire, proposée, préparer, favoriser, oledb, expose, interface, icommandprepare, utilisez, réservez, sûr, 4000, facilité, offerte, contente, évaluer, telle, déclarer, chaîne, paramétrant, explicitement, présente, show, intègre, diminuer, consommation, dynamiques, forcée, collection, sqlcommand, explicites, allez, bucketization, noterez, passage, vérifié, littéral, panacée, remplace, simplicité, apparemment, légère, effort, correspondances, conservation, optimaux, limites, servent, justement, rencontrées, converties, revenir, généraliser, rationalisée, correspondre, comportant, espaces, chariot, toutefois, souple, comparaison, changera, considère, pouvoir, pratiquement, constructions, particulières, courantes, jointures, quoi, concurrencer, safe, égalité, précédent, avait, facile, influencer, déclenchant, limité, sp2, transformé, mettre, remplacer, transformer, critère, interne, appelait, automatique, nomméparamétrage, mailto, examen, montre, termes, fonctionnement, considérant, passées, clauses, libérés, caché, préfixés, syntaxiques, composé, opposition, texte, comparant, entrante, présents, chaînes, résumé, effectuer, rapide, espace, insensible, suffit, nouvelle, vérifions, seules, isolée, réutilisable, réaction, penser, rend, profiter, nettement, performante, abordés, diminution, centralisation, facilitée, soumis, extrait, précédentes, avions, totalité, individuels, vision, celle, expérience, observé, handle, md5, garantis, uniques, quelles, login, existingconnection, useroptions, concerne, variétés, anciennes, placent, modernes, maintenez, états, affectant, route, catégorie, aucune, change, préfixez, conseils, imposent, complètement, identifié, garantir, référencer, ansi_defaults, arithabort, datefirst, language, quoted_identifier, empêcher, influent, comparaisons, modifient, exprimés, évalue, tôt, modifiée, évaluée, constant, folding, testés, courants, venons, conditions, invalident, pourtant, obligent, compiler, situations, cherche, pénalisant, is_cache_key, x020000002f5cc820e0cc946dd76094543cf7aa299904c81a, value, afin, attribut, créés, sql_hande, traçant, cacheinsert, exécutée, exemples, combien, vertu, recherché, cherchera, exécutent, identique, touché, sait, avance, bon, déclarés, schémas, arrêter, influe, maintenir, memobj_sqlmgr, dm_os_memory_objects, générales, part, réellement, rollup, group, avg_usecounts, avg, max_usecounts, max, total_kb, sum, cnt, count, contiennent, tableau, génèrent, xstmts, runtime, statements, simultanément, références, lançant, inspecter, passez, venant, dm_exec_cached_plan_dependent_objects, quelque, sorte, ordres, envoyés, lot, ira, desc, entries_count, single_pages_mb, donne, mxc, examiner, dm_os_memory_cache_hash_tables, chacune, composée, bound, trees, arbres, analyse, batches, réalité, parties, principales, stockent, stores, demandée, curseur, partitionnée, notification, permission, browse, jeu, résultant, distant, lié, compilées, pourront, effectivement, crée, déclenchera, moment, signification, détecter, indiquent, possibles, stmtrecompile, encore, contraignant, empêche, apportées, entraînent, nombreuses, essayez, vérifiez, premiers, puis, 500, deviennent, ceux, permanentes, composer, détectez, fréquentes, lesquelles, capable, veut, modération, évitez, recours, exagéré, remplacées, cte, pire, petits, volumes, common, bout, sixième, petit, compliquée, meilleur, auparavant, tracez, verrez, ressemblant, values, into, insert, while, char, not, nonclustered, key, primary, identity, proc, générée, six, insertions, mise, jour, cardinalités, entraîner, situation, beaucoup, invalidé, interpolation, dml, ddl, susceptible, répercutée, pratiques, programmation, déclenchent, répétitives, parlé, considérer, avérer, coûteuses, mal, parfois, ralentissent, comporte, teste, dépasse, exactitude, jacentes, systématiques, adapter, nouvel, vit, susceptibles, évolue, choses, potentiel, attentif, sentir, déclencher, affectent, statement, level, déclenchées, volontairement, raisonnable, intouché, vie, active, années, redémarrée, marquer, faits, supprime, indiquer, référençant, recompilées, prochaine, aujourd, hui, peu, faisant, invalidant, automatiquement, vient, sélective, générera, branche, générale, génériques, conditionne, but, louable, getcontactsbywhatever, modulaire, aigu, branchements, conditionnels, locales, assurer, stabilité, utilisées, complété, désagrément, approche, évidemment, coder, dur, évoluer, disparaître, optimiseur, sélectionner, limiter, usage, petite, celui, inefficace, atypiques, particulière, méthode, particuliers, façons, premièrement, corps, indépendamment, résoudre, appliquant, preniez, créez, soucier, écrivant, différemment, modularisant, forçant, incite, choisir, second, cohérent, terme, voulions, montrer, actuellement, sommes, affecté, opérande, expression, filtre, basé, futures, génère, parcourir, emplacement, observons, directe, littéralement, flairage, détecte, fera, scannera, représentatifs, futurs, démonstration, permettra, démontrer, particularité, appelons, constatons, efficace, relop, nodeid, physicalop, logicalop, estimaterows, 466, estimateio, 540903, estimatecpu, 0221273, avgrowsize, 126, estimatedtotalsubtreecost, 56303, parallel, estimaterebinds, estimaterewinds, xml, trouvons, aisément, mis, comprenez, évidence, sélectivité, excellente, choisira, résolue, probablement, nix, person_contact, aider, passons, leurs, différer, miracle, optimisées, tenant, envoyées, impose, typique, extrêmes, produire, ultérieurs, confrontés, nettoyages, intempestifs, consultez, enregistré, issue, event, errors, warnings, freeprocache, trouve, vidées, check, appliquer, détachement, sp_detach_db, intégralement, opérations, exécuter, http, sqlblog, com, blogs, kalen_delaney, archive, 2007, geek, city, clearing, single, aspx, freesystemcache, all, moderne, auce, vieilles, scoped, clear, note, appliquent, spécifique, lancer, verrait, immédiatement, ses, dégrader, rempli, checkpoint, souhaitez, réaliser, tests, entièrement, général, conjointement, proche, écrivez, trop, longues, modularisez, assurez, disposer, suffisamment, verrou, posé, longue, attente, fin, pages_allocated_count, disk_ios_count, context_switches_count, normalement, limitée, arriver, oblige, nettoyer, place, efforcer, recréer, dès, ultérieur, économisant, calcul, facilement, évènements, inclut, compris, sollicité, exécute, gagnera, observant, fort, juger, indique, complexe, mégaoctets, ailleurs, complexité, varie, simultanées, cachés, usercounts, exécutant, constaterez, reviendrons, capacité, cacher, syntaxique, effectuée, réalisées, entendons, vive, anciennement, ultérieures, économise, étape, redémarrage, cause, nettoie, sérielle, monoprocesseur, parallélisée, examiné, alliée, détail, réside, distinguer, proprement, dit, existent, vertus, vues, syntaxiquement, conceptuellement, référencé, doivent, exister, deferred, resolution, system_sql_modules, object_definition, curieux, comment, appellent, interroge, requêtées, directement, source, métadonnées, courante, avantage, détailler, parlerons, gardez, esprit, udf, précompilés, manière, print, tester, malgré, écrire, remise, configurer, initialiser, ouverture, fenêtre, propriétés, ajouter, startup, section, drapeau, 3640, désactive, renvoi, désactiver, globalement, modifiant, dosomethinguseful, indépendant, jeux, paquet, tds, fasse, allers, plupart, utilise, gain, désactivant, tokens, pendant, adodb, recordset, recordcount, sqlrowcount, indiquant, affectées, onglet, forme, langue, us_english, row, affected, principe, chaînage, propriétaire, attribuer, autoriser, jacents, contrôler, précisément, points, lecture, concis, conservé, détaillerons, aspect, ownership, chaining, encapsuler, centraliser, endroit, logique, faites, choix, plutôt, côté, simples, hors, présenter, concentrer, avancés, permettront, élaborée, saisie, construite, dynamiquement, courant, offre, réels, donner, outils, objectif, minutes, lire, chapitre, tuning, categories, wsfc, windows, log, supervision, sauvegarde, restauration, parallelisme, maxdop, dba, backup, tag, cloud, entreprise, reference, verrous, indexation, modèle, matériel, règles, introduction, téléchargements, liens, réplication, parallélisme, rcsi, store, conversions, implicites, cardinality, estimator, bloquer, blocages, incréments, modélisation, kvm, docker, linux, standard, basic, availability, groups, bag, timeouts, dbatools, hadr, sp_whoisactive, deadlock, allocations, dev, fichiers, déplacer, bases, mongodb, analytique, bcp, prtg, monitoring, backups, compression, howtos, iqp, rationnelles, consomme, ram, articles, dark, light, english, formations,

Text of the page (random words):
cédure nous sommes passé par une variable locale à laquelle nous avons affecté la valeur du paramètre et nous avons ensuite utilisé cette variable locale comme opérande de l expression de filtre ce que nous voulions montrer c est que cette syntaxe ne permet pas le parameter sniffing dans ce cas le plan d exécution généré prend en compte une distribution moyenne des valeurs dans la colonne et non pas la valeur actuellement passée à la requête ceci simplement parce que cette valeur n est pas connue lors de l optimisation dans notre cas la distribution moyenne incite sql server à choisir un scan lors du premier appel cette stratégie est plus coûteuse que le seek en revanche lors du second appel le résultat est bien plus cohérent en terme de temps cpu et de reads dans le deuxième cas le scan est la bonne stratégie il est donc important que vous preniez ce comportement en compte lorsque vous créez des procédures stockées souvent les paramètres sont assez consistants et vous n avez pas à vous soucier du premier plan d exécution généré mais dans certains cas vous devez gérer ces différences soit en écrivant vos procédures stockées différemment en les modularisant par exemple soit en les forçant à se recompiler forcer la recompilation que faire pour résoudre le problème des appels de procédures stockées avec des paramètres s appliquant à des colonnes dont la distribution est très variable une solution est de provoquer manuellement la recompilation cela peut être fait de plusieurs façons premièrement avec l option with recompile peut être indiquée dans le corps de la procédure stockée ou indépendamment à chaque appel alter procedure person getcontactbylastname lastnamestart nvarchar 50 with recompile as begin ou exec person getcontactbylastname ackerman with recompile la première méthode qui force la recompilation à chaque appel est utile pour une procédure où les valeurs de paramètres sont très différentes chaque fois la seconde pour forcer la recompilation dans des cas particuliers si votre procédure n est appelée qu avec des paramètres atypiques préférez la première solution la seconde est d une utilité particulière un exec mldr la recompilation est une opération coûteuse c est pourquoi il vaut mieux limiter l usage du with recompile à des procédures de petite taille qui en ont vraiment besoin c est à dire où le coût de la recompilation est moindre que celui de l exécution avec un plan inefficace pour les procédures qui comportent de multiples instructions vous pouvez sélectionner une recompilation instruction par instruction à l aide de l indicateur de requête option recompile par exemple create procedure dbo getcontactsparameter lastname nvarchar 50 null as begin set nocount on select firstname lastname from person contact where lastname like lastname option recompile end une meilleure solution est de forcer toujours par indicateur de requête sur quelle valeur l optimiseur doit se baser select firstname lastname from person contact where lastname like lastname option optimize for lastname le désagrément de cette approche étant évidemment de coder en dur une valeur de la colonne qui peut évoluer à travers le temps ou disparaître sql server 2008 en sql server 2008 l indicateur optimize for est complété de la façon suivante option optimize for variable unknown ou option optimize for unknown qui permettent comme à travers l utilisation de variables locales de faire une optimisation générique à partir d une distribution moyenne des valeurs des colonnes et donc d assurer la stabilité du plan soit pour une variable soit pour toutes les variables utilisées dans la requête ces options sont très utiles dans les procédures stockées complexes où le problème du parameter sniffing se fait le plus aigu notamment lors de branchements conditionnels prenons le cas de cette procédure modulaire create procedure person getcontactsbywhatever nameparttype tinyint namepart nvarchar 50 as begin set nocount on if nameparttype 1 select firstname lastname emailaddress from person contact where firstname like namepart else if nameparttype 2 select firstname lastname emailaddress from person contact where lastname like namepart else if nameparttype 3 select firstname lastname emailaddress from person contact where emailaddress like namepart end le but louable au premier abord est de créer une procédure générique malheureusement la première valeur envoyée dans namepart conditionne le plan d exécution des trois instructions pour trois colonnes différentes dans ce cas la recompilation sélective est très intéressante car elle ne générera une compilation que de l instruction utilisée dans laquelle on se branche au lieu d une recompilation générale comme avec un with recompile notons tout de même que la meilleure solution reste d éviter la création de procédures génériques sp_recompile la procédure stockée système sp_recompile permet de marquer une procédure ou un déclencheur pour être recompilée dans les faits elle supprime la procédure du cache vous pouvez aussi indiquer une table ou une vue dans ce cas toutes les procédures référençant cet objet seront recompilées à leur prochaine exécution cette procédure est aujourd hui peu utile sql server faisant ce travail lui même notamment invalidant automatiquement les procédures d un objet qui vient d être modifié recompilations automatiques les recompilations ne sont pas toutes déclenchées volontairement pour plusieurs raisons il ne serait pas raisonnable de conserver un plan d exécution intouché en cache tout le long de la vie d une instance qui peut rester active des années sans être redémarrée le serveur sql vit non seulement les structures sont susceptibles de changer ce qui invalide le plan d exécution mais le contenu des tables évolue entraînant des recalculs de statistiques le contexte d exécution des utilisateurs peut lui aussi changer etc toutes choses entraînant un changement potentiel de plan d exécution sql server est attentif à ces modifications et peut lorsque le besoin s en fait sentir déclencher des recompilations automatiques lors de l exécution du code depuis sql server 2005 ces recompilations s effectuent par instruction statement level recompilation et n affectent donc pas la procédure ou le batch tout entier elles se produisent pour deux types de raisons exactitude des données la modification des structures sous jacentes comme l ajout ou la suppression de colonnes de table de contraintes la suppression d un index utilisé dans le plan etc ainsi que la modification d options de session notamment à l intérieur de la procédure stockée les options qui peuvent modifier le résultat des requêtes ou la valeur des constantes comme par exemple set ansi_nulls set concat_null_yields_null set dateformat etc peuvent provoquer des recompilations systématiques elles peuvent changer le contexte d exécution donc forcer le plan d exécution à s adapter à ce nouvel environnement optimisation du plan principalement lors d un recalcul de statistiques le plan compilé comporte une valeur de seuil de recompilation et teste à chaque exécution si le nombre de modifications dans la table dépasse ce seuil si c est le cas la requête est recompilée les recompilations peuvent s avérer coûteuses elles sont un mal ou un bien nécessaire mais parfois elles ralentissent inutilement l exécution de procédures stockées cela est généralement dû à de mauvaises pratiques de programmation qui déclenchent des recompilations répétitives à chaque exécution de la procédure stockée et même plusieurs fois par exécution nous avons parlé des options de sessions d autres éléments sont à considérer l interpolation de code dml et de code ddl est susceptible de provoquer des recompilations la modification de structure d objets doit être répercutée dans le plan d exécution des requêtes qui les référencent la mauvaise utilisation de tables temporaires peut entraîner des recompilations la situation depuis sql server 2005 est meilleure qu en sql server 2000 comme les recompilations s effectuent maintenant instruction par instruction beaucoup de recompilations dues aux tables temporaires ne se produisent plus car le plan de toute la procédure n est pas invalidé et le cache de chaque instruction peut être réutilisé lorsque la table temporaire est créée une recompilation de type compilation déférée voir plus loin les événements de trace est déclenchée ensuite l insertion la mise à jour ou la suppression de lignes dans la table temporaire peut rapidement provoquer d autres recompilations pour prendre en compte les nouvelles cardinalités la distribution des valeurs dans une colonne une recompilation est générée sur une table temporaire à partir de six insertions sur une table vide exemple alter proc dbo testtemptable nbinserts smallint 1 as begin declare i smallint set i 1 create table t id int identity 1 1 primary key nonclustered col char 8000 not null default e while i nbinserts begin insert into t default values select from t where col e or id 20 set i i 1 end end go exec dbo testtemptable go si vous tracez cette exécution avec le profiler vous verrez un résultat ressemblant à la figure 9 2 fig 9 2 recompilations sur tables temporaires c est à dire deux recompilations pour compilation déférée si nous exécutons ensuite exec dbo testtemptable 7 voici le résultat en figure 9 3 fig 9 3 recompilations sur tables temporaires 9 3 recompilations sur tables temporaires nous avons dû faire ici une requête select sur la table temporaire un tout petit plus compliquée que nécessaire parce que le comportement du cache sur des procédures stockées est bien meilleur qu auparavant la recompilation se déclenche au bout de la sixième insertion dans la table ce qui est le comportement normal de sql server à la deuxième exécution de la procédure il n y aura pas recompilation l instruction étant déjà cachée avec un plan valable sql server est donc capable de gérer assez bien les recompilations dues aux tables temporaires cela ne veut pas dire que vous devez les utiliser sans modération évitez donc le recours exagéré aux tables temporaires elles sont souvent inutiles et peuvent être remplacées par des sous requêtes des expressions de table cte common table expressions ou au pire des variables de table pour les petits volumes si vous êtes forcé de composer avec des tables temporaires et que vous détectez par le profiler des recompilations fréquentes dues à des changements de cardinalité dans les tables il vous reste deux options de requêtes avec lesquelles vous pouvez modifier la fréquence des recompilations keep plan et keepfixed plan keep plan et keepfixed plan l indicateur de requêtes keep plan modifie les seuils de compilation des tables temporaires premiers seuils à 6 lignes modifiées puis 500 lignes modifiées qui deviennent alors identiques à ceux des tables permanentes si les modifications apportées à des tables temporaires entraînent de nombreuses recompilations essayez de placer cette option dans votre procédure et vérifiez si cela diminue la fréquence de recompilation exemple de syntaxe pour notre procédure select from t where col e or id 20 option keep plan l indicateur keepfixed plan est encore plus contraignant il empêche simplement toute recompilation de l instruction pour raison d optimisation nouvelles statistiques changement de cardinalité mldr tracer les recompilations vous pouvez détecter les recompilations à l aide d une trace et des événements sp recompile et sql stmtrecompile ces événements vous indiquent dans la colonne eventsubclass de la trace pour quelle raison la recompilation a été déclenchée les valeurs possibles sont les suivantes eventsubclass signification 1 la structure de l objet a changé 2 les statistiques ont changé 3 compilation déférée par exemple lors de l utilisation d une table temporaire les instructions de la procédure utilisant cette table ne peuvent être compilées au début de l exécution de la procédure elles ne le pourront que lorsque la table sera effectivement crée cela déclenchera à ce moment une recompilation de ce type 4 une option de session a changé 5 une table temporaire a changé 6 un jeu de résultant distant serveur lié a changé 7 une permission for browse a changé 8 l environnement de query notification a changé 9 une vue partitionnée a changé 10 les options de curseur ont changé 11 la recompilation a été demandée par l option de requête recompile cache des requêtes ad hoc le cache contient en réalité bien plus que les plans d exécution des procédures stockées il existe trois parties principales du cache cache stores qui nous intéressent ici et qui stockent des résultats de compilation object plans cachestore_objcp plans de procédures stockées déclencheurs et fonctions sql plans cachestore_sqlcp plans de batches bound trees cachestore_phdr arbres d analyse d une requête elles comportent chacune une table de hachage qui permet de gérer les entrées du cache une hash table composée de hash buckets vous pouvez obtenir des informations sur les caches par la vue de gestion dynamique sys dm_os_memory_cache_counters et examiner les tables de hachage par la vue sys dm_os_memory_cache_hash_tables et enfin les hash buckets par sys dm_os_memory_cache_entries il y a également plusieurs types d objets dans le cache les deux qui nous intéressent ici sont les plans compilés compiled plans cp et les plans d exécution execution plans mxc la requête suivante vous donne la taille de ces caches select name type single_pages_kb single_pages_kb 1024 as single_pages_mb entries_count from sys dm_os_memory_cache_counters where type in cachestore_sqlcp cachestore_objcp cachestore_phdr order by single_pages_kb desc les plans compilés représentent la compilation d une procédure ou d un batch de requêtes c est à dire d ordres sql envoyés en un seul lot depuis le client s il s agit d une procédure procédure stockée fonction déclencheur il est stocké dans cachestore_objcp s il s agit d un batch il ira dans cachestore_sqlcp les plans d exécution sont en quelque sorte des instances des plans compilés ils sont générés rapidement à l exécution à partir d un plan compilé il y a un plan d exécution par utilisateur lançant la procédure ou le batch vous pouvez inspecter ces plans à l aide de la fonction sys dm_exec_cached_plan_dependent_objects à laquelle vous passez en paramètre un plan_handle venant de sys dm_exec_cached_plans si la procédure est en cours d exécution plusieurs fois simultanément vous trouverez plusieurs références au même plan_handle les plans compilés contiennent un tableau d instructions sql chaque ordre sql dans la procédure ou le batch sont compilés séparément dans des cstmt ou compiled statements les plans d exécution génèrent des xstmts les versions runtime des cstmt 33 voici une requête pour voir la taille et l utilisation des différents objets select count as cnt sum size_in_bytes 1024 as total_kb max usecounts as max_usecounts avg usecounts as avg_usecounts case grouping cacheobjtype when 1 then total else cacheobjtype end as ...