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):
s 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 cacheobjtype case grouping objtype when 1 then total else objtype end as objtype from sys dm_exec_cached_plans group by cacheobjtype objtype with rollup le code de la procédure ou du batch est stocké dans un cache à part le sql manager cache sqlmgr il s agit d un stockage différent du plan réellement utilisé à l exécution vous pouvez voir les informations générales de ce cache à l aide de cette requête select from sys dm_os_memory_objects where type memobj_sqlmgr pourquoi maintenir le code de la requête séparément du plan compilé simplement parce que ce plan peut changer selon l état de la session c est à dire le contexte d exécution il est important de s y arrêter car cela influe sur la possibilité qu a sql server de réutiliser un plan en cache réutilisation des plans imaginons que nous exécutions le même code dans deux sessions qui comportent des options de session différentes set mldr set concat_null_yields_null on go select top 10 firstname middlename lastname from person contact go set concat_null_yields_null off go select top 10 firstname middlename lastname from person contact go ou imaginons que nous avons deux utilisateurs paul et isabelle qui sont déclarés dans la base avec deux schémas par défaut différents par exemple person et humanresources s ils exécutent tous deux un code identique qui ne référence pas le schéma de l objet un plan d exécution différent doit être généré même si l objet qui sera touché est le même simplement parce que sql server ne sait pas à l avance quel va être le bon objet en vertu du mécanisme de résolution de nom un objet appartenant au schéma par défaut sera d abord recherché et s il n est pas trouvé sql server cherchera un objet appartenant au schéma dbo exemple create table dbo test testid int go alter user isabelle with default_schema person alter user paul with default_schema humanresources go create table dbo test testid int go grant select on dbo test to paul grant select on dbo test to isabelle go execute as user paul select current_user go select from test go revert go execute as user isabelle select current_user go select from test go revert go combien avons nous de plans vous trouverez sur la figure 9 4 le résultat de la requête select st text qs sql_handle qs plan_handle from sys dm_exec_query_stats qs cross apply sys dm_exec_sql_text qs sql_handle st order by qs sql_handle exécutée après les deux exemples ci dessus fig 9 4 plusieurs plan_handle pour le même sql_handle vous voyez que pour le même sql_handle vous avez chaque fois deux plan_handle cela signifie que sql server a généré deux plans différents et qu il a donc stocké deux plans en cache vous pouvez aussi le constater en traçant avec le profiler l événement sp cacheinsert afin de vérifier pour quelle raison quel attribut du plan des plans différents ont été créés vous pouvez vous baser sur la vue sys dm_exec_plan_attributes comme ceci par exemple en passant un sql_hande trouvé select st text qs sql_handle qs plan_handle pa attribute pa value from sys dm_exec_query_stats qs cross apply sys dm_exec_sql_text qs plan_handle st outer apply sys dm_exec_plan_attributes qs plan_handle pa where qs sql_handle 0 x020000002f5cc820e0cc946dd76094543cf7aa299904c81a and pa is_cache_key 1 order by pa attribute ce qu il faut comprendre ici c est que sql server cherche à réutiliser autant que possible les plans d exécution en cache car le recalcul de plans peut être très pénalisant nous venons de voir que certaines conditions invalident pourtant le plan en cache et obligent sql server à compiler à nouveau le batch ou la procédure il faut éviter autant que possible de provoquer de telles situations les deux cas que nous avons testés ci dessus sont les plus courants un changement d options de session en cours de travail modifie le contexte d exécution des options comme ansi_nulls ansi_defaults concat_null_yields_null arithabort datefirst dateformat language quoted_identifier etc peuvent empêcher la réutilisation d un plan parce qu elles influent sur les comparaisons et qu elles modifient potentiellement la valeur des littéraux exprimés dans la requête sql server évalue très tôt dans la phase de compilation la valeur de ces littéraux une fonctionnalité nommée constant folding si une option est modifiée qui peut changer cette valeur déjà évaluée le code doit être compilé à nouveau lorsqu un objet n est pas complètement identifié par son schéma sql server ne peut pas garantir que l appel par des utilisateurs dont le schéma par défaut est différent va référencer le même objet il doit donc recompiler deux conseils s imposent donc 1 maintenez des états de session consistants en affectant les options à la connexion et éviter de les changer en cours de route surtout à l intérieur des procédures stockées le set nocount n entre pas dans cette catégorie il ne provoque aucune recompilation puisqu il ne change pas le comportement des requêtes 2 préfixez toujours vos objets par leur nom de schéma en ce qui concerne les options de session attention aux différentes variétés de bibliothèques client les anciennes méthodes de connexion telles que odbc ne placent pas par défaut les mêmes valeurs d option que les méthodes plus modernes comme ado net pour savoir quelles sont les valeurs des options de session vous pouvez utiliser la commande dbcc useroptions ou les événements session existingconnection et security audit audit login la différence entre le plan compilé et le plan d exécution peut être observé via la vue sys dm_exec_query_stats qui référence le plan compilé dans la colonne sql_handle et le plan d exécution dans la colonne plan_handle les handle sont des hachages md5 générés à partir du plan entier ils sont donc garantis uniques par plan ils peuvent être passés à la fonction sys dm_exec_sql_text pour voir le contenu du plan lorsque nous avons extrait dans les requêtes précédentes des plans d exécution du cache à l aide de la vue sys dm_exec_cached_plans et de la colonne plan_handle nous avions accès à la totalité du plan compilé et non aux cstmt individuels cette vision était celle d un cache particulier vous pouvez en faire l expérience avec cette requête d exemple dbcc freeproccache go set concat_null_yields_null on go select top 10 firstname middlename lastname from person contact go set concat_null_yields_null off go select top 10 firstname middlename lastname from person contact go plusieurs plan_handle pour le même sql_handle select cp usecounts cp size_in_bytes st text from sys dm_exec_cached_plans cp outer apply sys dm_exec_sql_text cp plan_handle st join sys dm_exec_query_stats qs on qs plan_handle cp plan_handle go cache des requêtes adhoc nous l avons vu les procédures ne sont pas seules à être cachées tout plan d exécution d une requête isolée est potentiellement réutilisable une première réaction serait de penser que cette fonctionnalité rend la procédure stockée moins intéressante puisque toutes les requêtes peuvent profiter d un cache de leur plan d exécution ce n est pas vraiment le cas la procédure reste nettement plus performante non seulement par les avantages que nous avons déjà abordés diminution du trafic réseau centralisation du code sécurité facilitée mais aussi parce que le cache de requêtes est soumis à quelques contraintes nous allons le voir lorsqu une requête ad hoc c est à dire un ordre sql composé par le client par opposition à du code stocké sur le serveur comme une procédure est envoyée au serveur sql server essaie de trouver une correspondance de texte de requête dans le cache sqlmgr cette correspondance est recherchée en comparant le hachage généré par la requête entrante avec les hachages présents dans le cache puisque ces chaînes de hachages représentent un résumé de toute la requête cachée cela permet d effectuer une recherche très rapide mais cela signifie aussi que la moindre différence de syntaxe invalide la recherche puisqu elle produit un résultat de hachage différent une espace de plus une différence de casse dans la requête même sur une serveur dont la collation par défaut est insensible à la casse cela n a pas de rapport avec la gestion du cache suffit à générer une nouvelle entrée dans le cache vérifions le simplement dbcc freeproccache go select from dbo test go select from dbo test go select from dbo test go select from dbo test go select cp usecounts cp size_in_bytes st text from sys dm_exec_cached_plans cp outer apply sys dm_exec_sql_text cp plan_handle st join sys dm_exec_query_stats qs on qs plan_handle cp plan_handle go le code ci dessus provoque l insertion de quatre entrées de cache comme nous pouvons le constater dans la figure 9 5 fig 9 5 plusieurs plans sur des différences syntaxiques attention la correspondance avec un plan adhoc caché n est possible que si les objets sont préfixés par leur schéma comme pour le cache de procédure ces plans d exécution ne sont libérés qu en cas de besoin mémoire mais ce fonctionnement est valable même en considérant des valeurs constantes différentes passées dans les clauses where pour la recherche en d autres termes des requêtes comme select from person contact where contactid 20 go select from person contact where contactid 40 go sont elles cachées séparément cela dépend dans cet exemple un examen de sys dm_exec_query_stats montre ceci 1 tinyint select from person contact where contactid 1 mailto 3d 1 ce qui signifie que sql server a transformé la requête avant de la mettre dans le cache pour remplacer les valeurs littérales par un paramètre dans certains cas sql server peut donc paramétrer la requête et transformer la valeur passée comme critère de recherche en paramètre en interne cela permet de réutiliser le plan si d autres paramètres sont passés ce mécanisme s appelait paramétrage automatique auto parameterization en sql server 2000 et est maintenant nomméparamétrage simple simple parameterization le comportement par défaut de sql server fait qu un nombre limité de requêtes peuvent être ainsi paramétrées par exemple les deux requêtes ci dess...
|
