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):
dure dbo dosomethinguseful as begin set nocount on ce qui désactive le renvoi des messages done_in_proc pour le reste de la session vous pouvez également désactiver globalement ces messages pour toutes les sessions en modifiant le paramètre de serveur user options exec sp_configure user options 512 reconfigure ou à l aide du drapeau de trace 3640 à ajouter à la ligne de commande de démarrage du serveur sql dans les paramètres du service propriété startup parameters du service dans sql server configuration manager voir section 7 5 2 malgré cela c est une bonne idée d écrire systématiquement la commande set nocount on au début de toutes vos procédures stockées car l option a pu être remise à off dans la session vous pouvez notamment configurer ssms pour initialiser cette option à l ouverture de session voyez la fenêtre de propriétés de la requête dans la page advanced enfin vous pouvez tester quel est l état de cette option de la façon suivante exemple d utilisation if options 512 512 print set nocount est à on maîtriser la compilation un avantage important de la procédure stockée est la réutilisation de son plan d exécution nous allons détailler ce mécanisme ci dessous nous ne parlerons que de procédures mais gardez à l esprit que le mécanisme est le même pour les fonctions utilisateur udf et pour les déclencheurs qui sont précompilés de la même manière lorsque la procédure est créée via une instruction create procedure son code source est stocké dans une table système de métadonnées de la base courante vous pouvez retrouver ce code grâce à la vue système sys sql_modules select definition from sys sql_modules where object_id object_id dbo uspgetbillofmaterials cette requête retourne la définition de la procédure nommée dbo uspgetbillofmaterials la vue sys sql_modules interroge des tables systèmes qui ne peuvent plus être requêtées directement depuis sql server 2005 si vous êtes curieux de savoir comment ces tables systèmes s appellent vous pouvez retrouver la définition de cette vue comme de tous les objets système à l aide d un de ces commandes select object_definition object_id sys sql_modules ou select definition from sys system_sql_modules where object_id object_id sys sql_modules ce stockage n implique en rien une compilation ou une optimisation il faut distinguer en sql server la phase de compilation à proprement dit la vérification des privilèges de l utilisateur et de l existence des objets de l optimisation l optimisation est la génération d un plan d exécution pour le code sql lorsque la procédure est créée rien de tout cela n est fait même pas la vérification de l existence des objets on peut créer une procédure qui référence des tables qui n existent pas par les vertus de la fonctionnalité de résolution de nom différée deferred name resolution todo cette résolution différée ne s applique qu aux tables et aux vues qui sont syntaxiquement et conceptuellement identiques aux tables tout autre objet référencé comme une procédure stockée appelée par un execute ou une colonne d une table doivent exister ainsi à la création de la procédure on peut dire que seule une vérification syntaxique du code sql est effectuée la vérification de l existence des tables comme l optimisation ne seront réalisées que lors de la première exécution de la procédure stockée par première exécution nous entendons première exécution depuis le démarrage de l instance sql le plan d exécution généré pour la procédure lors de sa première exécution sera stocké en mémoire vive dans ce qu on appelle le cache de plans ou plus anciennement cache de procédures lors des exécutions ultérieures de la procédure ce plan sera utilisé ce qui économise l étape d optimisation le plan reste dans le cache jusqu au redémarrage du service il est également possible que à cause d une pression sur la mémoire le cache se nettoie il ne contient au maximum que deux versions d un plan pour une procédure la version sérielle monoprocesseur et éventuellement la version parallélisée de plus ce plan est stocké sans information d utilisateur nous verrons dans la partie 9 1 2 ce que cela implique le cache de procédure peut être examiné à l aide d une vue de gestion dynamique sys dm_exec_cached_plans alliée aux fonctions tables sys dm_exec_sql_text et sys dm_exec_query_plan elle vous permet d observer en détail ce qui réside dans le cache select cp usecounts cp size_in_bytes st text db_name st dbid as db object_schema_name st objectid st dbid object_name st objectid st dbid as object qp query_plan cp cacheobjtype cp objtype from sys dm_exec_cached_plans cp cross apply sys dm_exec_sql_text cp plan_handle st cross apply sys dm_exec_query_plan cp plan_handle qp en exécutant cette requête vous constaterez que la colonne cp objtype contient différents types de requêtes et pas seulement des procédures stockées nous reviendrons sur la capacité de sql server à cacher d autres plan d exécution plus loin vous pouvez constater en observant cette requête que la vue sys dm_exec_cached_plans retourne les colonnes usecounts et size_in_bytes fort utiles pour juger de la taille et de l utilité du cache bien entendu usercounts indique le nombre de réutilisation du plan d exécution et size_in_bytes sa taille en mémoire le cache d une procédure complexe peut prendre plusieurs mégaoctets la taille indiquée ne dépend d ailleurs pas seulement de la complexité du plan d exécution elle varie aussi selon le nombre d exécutions simultanées de la procédure ou du batch car comme nous le verrons des plans en rapport avec le contexte d exécution sont générés à l exécution à partir de ce plan compilé et eux mêmes cachés on aura compris une chose un serveur sql fortement sollicité et qui exécute des requêtes complexes gagnera fortement à avoir le plus de mémoire de travail possible dès que le plan d exécution de la procédure est en cache il sera réutilisé à chaque appel ultérieur économisant ainsi le calcul coûteux du plan d exécution vous pouvez facilement observer les différences de temps d exécution entre le premier appel d une procédure et les suivantes avec le profiler ou les évènements étendus et notamment sur la valeur en temps cpu qui inclut le temps de compilation normalement la procédure va rester dans le cache en revanche comme la mémoire est limitée il peut arriver qu une pression sur la mémoire oblige sql server à nettoyer le cache 32 pour faire de la place pour la mémoire de travail dans ce cas sql server va tout de même s efforcer de conserver les plans les plus coûteux à recréer vous pouvez vous faire une idée du coût d un plan à l aide des colonnes original_cost et current_cost de sys dm_os_memory_cache_entries exemple de requête select db_name st dbid as db object_schema_name st objectid st dbid object_name st objectid st dbid as object cp objtype cp usecounts cp size_in_bytes ce disk_ios_count ce context_switches_count ce pages_allocated_count ce original_cost ce current_cost from sys dm_exec_cached_plans cp join sys dm_os_memory_cache_entries ce on cp memory_object_address ce memory_object_address cross apply sys dm_exec_sql_text cp plan_handle st pression sur le cache de plans un verrou de compilation est posé sur une procédure lors de sa compilation il ne peut y avoir qu une seule compilation de la même procédure à la fois ce qui signifie que si la compilation est longue tous les utilisateurs seront en attente de la fin de la compilation pour éviter ce type de problèmes n écrivez pas de procédures trop longues ou complexes modularisez au besoin et assurez vous de disposer de suffisamment de mémoire de travail si vous souhaitez vider le cache par exemple pour réaliser des tests consistants de performances vous disposez de la commande dbcc freeproccache qui vide entièrement le cache de plans elle est en général utilisée conjointement avec dbcc dropcleanbuffers pour obtenir un état de la mémoire proche d un démarrage de l instance sql checkpoint dbcc dropcleanbuffers dbcc freeproccache il vaut mieux bien entendu éviter de lancer ces commandes sur un serveur de production qui verrait immédiatement ses performances se dégrader jusqu à ce que le cache soit de nouveau rempli deux autres commandes dbcc moins connue permettent de vider le cache pour les plans qui s appliquent à une base de données spécifique dbcc flushprocindb dbid select db_id adventureworks dbcc flushprocindb 5 note une version plus moderne de cette commande existe maintenant qui évite l appel auce vieilles commandes dbcc alter database scoped configuration clear procedure cache todo pour vider plus généralement les caches de sql server dbcc freesystemcache all plus d informations sur cette entrées de blog http sqlblog com blogs kalen_delaney archive 2007 09 29 geek city clearing a single plan from cache aspx il est également à noter que le cache se vide intégralement lors de quelques opérations qu il faut donc éviter d exécuter inutilement sur un serveur de production détachement d une base de données sp_detach_db utilisation de la commande reconfigure pour appliquer les changements d une option de serveur lorsqu une vue est créée avec check option toutes les entrées du cache qui référencent la base de données dans laquelle se trouve la vue sont vidées si vous êtes confrontés à des nettoyages de cache intempestifs consultez le journal d erreur errorlog de sql server un message d information y est enregistré lorsque le cache se vide aussi à l issue d un dbcc freeprocache vous pouvez également tracer l événement sql trace errors and warnings errorlog l événement security audit audit dbcc event se déclenche également à l exécution de toute commande dbcc paramètres typiques sur quelle base le plan d une procédure est il calculé lorsque nous passons des paramètres leurs valeurs peuvent différer et générer des plans d exécution différents qu en est il alors du comportement de la procédure stockée malheureusement il n y a en ce domaine pas de miracle toutes les instructions de la procédure sont optimisées en tenant compte des valeurs de paramètres envoyées le premier appel impose donc la qualité du plan d exécution si la valeur des paramètres est typique le plan sera de bonne qualité en revanche si des paramètres extrêmes sont passés cela peut produire de mauvaises performances lors des appels ultérieurs prenons l exemple de ces deux requêtes créons un index pour aider la recherche create index nix person_contact lastname on person contact lastname go 2 lignes à retourner select firstname lastname emailaddress from person contact where lastname like ackerman 911 lignes à retourner select firstname lastname emailaddress from person contact where lastname like a à l évidence le plan d exécution sera différent la sélectivité de l index est excellente pour répondre à la première requête et sql server choisira un seek en revanche la deuxième requête sera résolue par un scan probablement moins coûteux vous comprenez déjà le problème si nous créons une procédure stockée de ce type create procedure person getcontactbylastname lastnamestart nvarchar 50 as begin set nocount on select firstname lastname emailaddress from person contact where lastname like lastnamestart end nous pouvons aisément vérifier que le plan d exécution mis en cache dépend du paramètre envoyé lors du premier appel de la procédure exécutons une première fois la procédure et examinons le plan en cache exec person getcontactbylastname a go select cp size_in_bytes qp query_plan from sys dm_exec_cached_plans cp cross apply sys dm_exec_sql_text cp plan_handle st cross apply sys dm_exec_query_plan cp plan_handle qp where st dbid db_id adventureworks and st objectid object_id adventureworks person getcontactbylastname dans le plan xml nous trouvons l opérateur de scan d index clustered donc de table relop nodeid 0 physicalop clustered index scan logicalop clustered index scan estimaterows 911 466 estimateio 0 540903 estimatecpu 0 0221273 avgrowsize 126 estimatedtotalsubtreecost 0 56303 parallel 0 estimaterebinds 0 estimaterewinds 0 si nous appelons la procédure avec le paramètre ackerman et que nous examinons le plan d exécution dans ssms nous constatons que l opérateur est toujours un scan le plan a été réutilisé alors qu il n est de loin pas dans ce cas par rapport au paramètre envoyé le plus efficace la procédure est compilée en utilisant une fonctionnalité appelée parameter sniffing littéralement flairage de paramètre le moteur d optimisation détecte la valeur du paramètre passé le plan sera donc calculé selon le paramètre envoyé lors du premier appel de la procédure si c est ackerman la procédure fera toujours un seek si c est a elle scannera toujours la table le parameter sniffing est une bonne chose lorsque le premier appel est passé avec des paramètres représentatifs des futurs appels de la procédure en revanche c est une mauvaise chose lorsque le premier appel est un cas particulier nous allons en faire la démonstration ce qui nous permettra de démontrer une autre particularité du parameter sniffing procédure avec utilisation directe du paramètre create procedure dbo getcontactsparameter lastname nvarchar 50 null as begin set nocount on select firstname lastname from person contact where lastname like lastname end go procédure avec variable locale create procedure dbo getcontactslocalvariable lastname nvarchar 50 null as begin set nocount on declare mylastname nvarchar 50 set mylastname lastname select firstname lastname from person contact where lastname like mylastname end go utilisation exec dbo getcontactsparameter lastname abercrombie go exec dbo getcontactslocalvariable lastname abercrombie go exec dbo getcontactsparameter lastname go exec dbo getcontactslocalvariable lastname go avant d expliquer la raison pour laquelle nous avons créé deux procédures observons les résultats de l exécution en figure 9 1 nous y voyons les statistiques d exécution des quatre appels de procédure ainsi que le plan d exécution généré du premier appel fig 9 1 recompilations nous avons passé d abord le nom abercrombie la compilation de la procédure a donc produit un plan basé sur un seek d index ce plan sera réutilisé tout au long des exécutions futures de la procédure ce que nous voyons plus loin un appel avec le paramètre génère près de 60 000 reads le moteur de stockage a été forcé de parcourir près de 20 000 l index pour trouver l emplacement de chaque ligne de la table et qu en est il de la deuxième procé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 ...
|
