Comment faire pour résoudre les problèmes de performances de requêtes ad-hoc

Traductions disponibles Traductions disponibles
Numéro d'article: 243588 - Voir les produits auxquels s'applique cet article
Ancien nº de publication de cet article : F243588
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article explique comment résoudre la lenteur excessive d'un grand nombre de requêtes ad-hoc simultanées dans Microsoft SQL Server. Si vous n'avez pas pu identifier la cause exacte du problème, consultez l'article suivant de la Base de connaissances Microsoft avant de poursuivre :
224587 INF : Résolution des problèmes de performance d'applications sous SQL Server

Cet article part du principe que vous avez suivi les instructions de l'article 224587 pour cibler l'étendue du problème et que vous avez capturé un journal de l'Analyseur de performances Windows NT ainsi qu'une trace du Générateur de profils SQL Server comportant les détails des compteurs, événements et colonnes de données spécifiques.

Caractéristiques des problèmes de performances

Les problèmes de performances présentent les caractéristiques suivantes :
  • Les requêtes ad-hoc courtes qui durent normalement peu de temps provoquent un ralentissement des performances du système lorsqu'elles sont exécutées simultanément par de nombreux utilisateurs.
  • Utilisation très élevée ou à 100 % de l'unité centrale.
  • Aucun blocage associé pendant les périodes de faibles performances.

    Vous pouvez rapidement identifier les blocages en vérifiant la colonne BLK dans la sortie de la procédure stockée sp_who du système. Si la colonne BLK est différente de zéro pour certains ID de processus système (SPID), il s'agit d'un blocage.
  • Dans certains cas, la mémoire du serveur est trop sollicitée et vous pouvez recevoir des erreurs telles que celles-ci :
    Erreur : 701, Gravité : 17, État : 1
    Mémoire système insuffisante pour exécuter cette requête.
    - ou -
    Msg 8645, Niveau 17, État 1, Procédure, ligne 1
    Le délai a été dépassé pendant l'attente de ressources mémoire pour exécuter la requête. Relancez la requête.

Améliorations des compilations de requêtes

Grâce aux améliorations d'architecture système implémentées dans SQL Server 7.0, en particulier en ce qui concerne l'optimiseur de requêtes, vous pourrez constater une différence d'utilisation des ressources système de la part des applications, par rapport aux versions précédentes de SQL Server. Plus précisément, SQL Server 7.0 peut afficher une utilisation plus intense de l'unité centrale ou de la mémoire, alors que les versions précédentes de SQL Server avaient tendance à dépendre des E/S disque. Ces modifications peuvent être attribuées à deux facteurs :
  • les jointures de fusion et de hachage et
  • les temps de compilation des requêtes.
Les versions précédentes de SQL Server se basaient uniquement sur des itérations de boucles imbriquées pour effectuer des jointures. Les jointures de boucles imbriquées utilisent naturellement l'E/S disque. À partir de SQL Server 7.0, les jointures de fusion et de hachage ont été ajoutées. Les jointures de fusion et de hachage effectuent davantage de traitements intramémoires que les jointures de boucles imbriquées. La conséquence logique est une utilisation plus élevée de l'unité centrale et de la mémoire en cas d'utilisation de ces techniques de jointure. Pour plus d'informations sur les jointures de hachage et de fusion, reportez-vous aux rubriques « Comprendre les jointures de hachage » et « Description des jointures de fusion » dans la documentation en ligne de SQL Server 7.0.

Les temps de compilation des requêtes sont affectés, car l'optimiseur de requêtes dispose d'options et d'informations supplémentaires par rapport aux versions précédentes de SQL Server, notamment de nouvelles techniques de jointure de fusion et de hachage, des algorithmes de recherche et des statistiques de colonne améliorés. Ces informations supplémentaires permettent à l'optimiseur de requêtes de sélectionner la méthode la plus efficace pour récupérer des données de requête. Toutefois, l'analyse et l'étude de ces nouvelles techniques et informations requièrent du temps de traitement. Cette augmentation de l'utilisation centrale peut entraîner des temps de compilation de requête plus longs que dans les versions précédentes de SQL Server.

Pour la majorité des requêtes, cette hausse du temps de compilation est compensée par une baisse du temps d'exécution. La requête est donc plus rapide que dans les versions précédentes de SQL Server. Il existe néanmoins une exception, à savoir les requêtes de type OLTP, simples et de petite taille, qui ont des temps d'exécution très faibles. Dans le cas de ces requêtes, le processus de génération d'un plan de requête dure aussi longtemps, sinon plus, que l'exécution de la requête elle-même. La requête peut donc être légèrement plus lente que dans les versions précédentes de SQL Server. Étant donné que la différence se mesure généralement en millisecondes, les effets ne seront pas perceptibles pour une requête exécutée individuellement. Toutefois, vous constaterez peut-être que l'utilisation de l'unité centrale est plus élevée que dans les versions précédentes de SQL Server lorsqu'un grand nombre de requêtes ad-hoc sont exécutées simultanément par de nombreux utilisateurs.

Développement de requêtes paramétrées

SQL Server 7.0 utilise plusieurs nouvelles techniques, telles que la mise en cache des requêtes ad-hoc et le paramétrage automatique. Cependant, seul un nombre limité de requêtes peuvent être paramétrées automatiquement par SQL Server 7.0. Utilisez les méthodes suivantes pour vérifier que les plans de requête sont paramétrés et qu'ils peuvent être réutilisés de manière plus efficace :
  • Marqueurs de paramètres Les API OLE DB et ODBC permettent de spécifier des paramètres avec un point d'interrogation lors de la remise d'une requête. Cette méthode peut être très utile avec tout type d'application, particulièrement avec les applications intermédiaires qui comportent des modules de génération de requête, où les procédures stockées ne sont pas disponibles. Le plan de requête généré pour les requêtes exécutées avec des marqueurs de paramètres pourra être réutilisé par tout client exécutant la même requête, même en cas de spécification de valeurs de paramètres différentes. Pour plus d'informations, reportez-vous à la rubrique « Marqueurs de paramètres » dans la documentation en ligne de SQL Server 7.0.
  • sp_executesql La procédure stockée sp_executesql est appelée par le fournisseur OLE DB ou par le pilote ODBC lorsque des marqueurs de paramètres sont utilisés dans une application. Cependant, elle peut également être appelée directement par l'application ou à l'intérieur d'une autre procédure stockée de manière à paramétrer explicitement les requêtes ad-hoc. Cela peut s'avérer très utile dans les applications ou les fichiers de commandes dans lesquels l'instruction EXECUTE est utilisée pour exécuter des instructions SQL dynamiques. Contrairement à sp_executesql, l'instruction EXECUTE ne permet pas le paramétrage, ce qui limite la probabilité de réutilisation du plan de requête. Pour plus d'informations, reportez-vous aux rubriques « p_executesql (T-SQL) » et « Utilisation de sp_executesql » dans la documentation en ligne de SQL Server 7.0.
  • Procédures stockées Les procédures stockées présentent de nombreux avantages, y compris la possibilité de paramétrer des requêtes et de réutiliser des plans d'exécution. Pour plus d'informations, reportez-vous aux rubriques « Procédures stockées » et « Programmation de procédures stockées » dans la documentation en ligne de SQL Server 7.0.

Affichage des données de l'Analyseur de performances

Utilisez le journal de l'Analyseur de performances pour identifier les ressources système provoquant l'engorgement. Ce journal devrait vous donner une vue d'ensemble du système, vous permettant de vous concentrer sur des points précis lors de l'affichage des données du Générateur de profils. Examinez les données de l'Analyseur de performances à partir du moment où les performances étaient bonnes, jusqu'au moment de la baisse des performances. Identifiez le premier compteur affecté, puis déterminez lequel des problèmes suivants correspond le mieux à votre cas :
  • Objet : Processus
    Compteur : Processeur
    Instance : SQL Server
  • Objet : Processeur
    Compteur : %Temps processeur
    Instance : Vérifier chaque instance de processeur
  • Objet : SQL Server : Gestionnaire de mémoire tampon
    Compteur : Tampons libres
  • Objet : SQL Server : Gestionnaire de mémoire tampon
    Compteur : Nombre de pages occultées
  • Objet : SQL Server : Gestionnaire de mémoire
    Compteur : Demandes de mémoire en attente
  • Objet : SQL Server : Statistiques SQL
    Compteur : Compilations SQL/seconde
Si l'utilisation de l'unité centrale, les compilations SQL/seconde et les tampons libres ont des valeurs élevées et que les valeurs de demandes de mémoire en attente et de nombre de pages occultées soient faibles, cela signifie que l'unité centrale est à l'origine de l'engorgement. Identifiez en priorité comment paramétrer et réutiliser de manière efficace les plans de requête de manière à éviter les coûts engendrés par la génération d'un plan de requête ; reportez-vous à la section « Regroupement de la trace du Générateur de profils par classe d'événements » de cet article. Si les valeurs de tampons libres et de compilations SQL/seconde sont faibles et que celles de nombre de pages occultées et de demandes de mémoire en attente soient élevées, cela signifie que SQL Server est limité en mémoire. Recherchez avant tout des requêtes utilisant des jointures de hachage et susceptibles d'être transformées en jointures de boucles ; reportez-vous à la section « Regroupement de la trace du Générateur de profils par durée » de cet article. Pour plus d'informations sur les compteurs mentionnés ci-dessus, effectuez une recherche à l'aide du nom du compteur dans la documentation en ligne de SQL Server 7.0.

Affichage des données du Générateur de profils

Lors de la résolution de problèmes de performances, il est primordial d'afficher les données du Générateur de profils. Il n'est pas nécessaire d'examiner toutes les données capturées ; soyez sélectif. Le Générateur de profils vous permet d'afficher les données capturées de manière optimale. Sous l'onglet Propriétés (dans le menu Fichier, cliquez sur Propriétés), le Générateur de profils SQL vous permet de restreindre les données affichées en supprimant des colonnes de données ou des événements, en groupant ou triant les données par colonne et en appliquant des filtres. Vous pouvez effectuer une recherche sur toute la trace ou seulement rechercher des données particulières dans une colonne spécifique (dans le menu Edition, cliquez sur Rechercher.) Vous pouvez aussi enregistrer les données du Générateur de profils SQL dans une table SQL Server (dans le menu Fichier, pointez sur Enregistrer sous, puis cliquez sur Table de trace) et exécuter des requêtes SQL sur cette table.

Remarque Assurez-vous d'effectuer le filtrage sur un fichier de trace déjà enregistré uniquement. En effectuant l'opération sur une trace active, vous risquez de perdre les données capturées depuis le démarrage de la trace. Enregistrez d'abord la trace active dans un fichier ou une table (dans le menu Fichier, cliquez sur Enregistrer sous) et ouvrez-la de nouveau (dans le menu Fichier, cliquez sur Ouvrir) avant de continuer. Lorsque vous travaillez sur un fichier de trace enregistré, le filtrage ne supprime pas définitivement les données ; elles sont simplement masquées. Vous pouvez ajouter et supprimer des événements et des colonnes de données afin de cibler vos recherches.

Vous devez également concentrer vos efforts sur les domaines dont vous tirez le plus d'avantages. Les facteurs suivants vous aideront à augmenter les performances des applications, mais pas nécessairement au même degré. Avant d'implémenter vos modifications, déterminez leur impact en fonction des facteurs suivants :
  • Fréquence d'exécution de la requête
  • Amélioration possible de la requête
Par exemple, la réduction de la durée d'une seule requête de 1,5 à 1,2 secondes n'est peut-être pas utile si la requête est rarement exécutée durant une journée. Cependant, si la requête est souvent exécutée simultanément par un grand nombre d'utilisateurs, l'amélioration des performances peut être significative. De la même manière, l'optimisation d'une seule requête de 6 minutes à 3 secondes peut ne pas engendrer d'amélioration notable des performances si elle est rarement utilisée. Utilisez les techniques de regroupement et de filtrage du Générateur de profils SQL ainsi que vos connaissances de l'application pour estimer l'impact d'une requête ou d'une procédure spécifique avant d'implémenter toute modification. Concentrez-vous tout d'abord sur les modifications ayant le plus d'impact et poursuivez avec des itérations sur d'autres requêtes et procédures jusqu'au moment où les performances auront été suffisamment améliorées.

Après avoir enregistré une trace du Générateur de profils SQL dans un fichier ou une table, ouvrez-la de nouveau dans le Générateur de profils et analysez son contenu. Pour regrouper la trace du Générateur de profils, procédez comme suit :
  • Regroupement de la trace du Générateur de profils par durée :
    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Cliquez sur l'onglet Colonnes de données, puis sous Groupes, à l'aide du bouton HAUT, déplacez la colonne Duration. Utilisez le bouton BAS pour supprimer toutes les autres colonnes.
    3. Cliquez sur l'onglet Événements, puis supprimez tous les événements sauf TSQL SQL:StmtCompleted et TSQL RPC:Completed. Vous pourrez ainsi vous concentrer sur les requêtes en cours d'exécution.
    4. Cliquez sur OK.
    Le regroupement par durée permet d'identifier facilement les instructions SQL, les lots et les procédures les plus lents. Analysez la trace au moment où le problème se produit et créez une ligne de base correspondant à de bonnes performances. Vous pouvez filtrer les données par heure de démarrage afin de séparer la trace en sections, à savoir des sections pour les périodes où les performances sont bonnes et des sections pour les périodes où les performances sont mauvaises. Recherchez les requêtes dont la durée est la plus longue pour les périodes où les performances sont bonnes. Elles sont vraisemblablement à l'origine du problème. Lorsque les performances du système baissent, même les requêtes correctes peuvent avoir une longue durée, étant donné qu'elles attendent des ressources système.

    Examinez les plans d'exécution pour les requêtes présentant le plus souvent de longues durées. Si vous voyez qu'une jointure de hachage est utilisée, pensez à forcer une jointure de boucle imbriquée à l'aide de l'indicateur LOOP JOIN. Si le temps d'exécution de la requête utilisant une jointure de boucle est inférieur, égal ou légèrement supérieur au temps d'exécution avec une jointure de hachage, la jointure de boucle peut constituer une option plus avantageuse si l'ordinateur doit faire face à une utilisation élevée de la mémoire et de l'unité centrale. En réduisant la sollicitation au niveau de l'engorgement des ressources (UC et mémoire), vous pouvez améliorer les performances du système. Pour plus d'informations sur l'indicateur LOOP JOIN, reportez-vous à la rubrique « SELECT (T-SQL) » dans la documentation en ligne de SQL Server 7.0.
  • Regroupement de la trace du Générateur de profils SQL par classe d'événements :
    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Cliquez sur l'onglet Colonnes de données, puis sous l'en-tête Groupes, à l'aide du bouton HAUT, déplacez les colonnes Event Class et Text, de manière à placer Event Class en premier. Utilisez le bouton BAS pour supprimer toutes les autres colonnes sous l'en-tête Groupes.
    3. Cliquez sur l'onglet Événements et assurez-vous que tous les événements sont inclus.
    4. Cliquez sur OK.

Types d'événements

Le regroupement à l'aide de la colonne Classe d'événements permet d'identifier les types d'événements ayant lieu sur l'ordinateur exécutant SQL, de même que leur fréquence. Recherchez les événements suivants dans cette colonne :
  • MISC: Prepare SQL et Exec Prepared SQL; CURSORS: Cursorprepare Un événement Prepare SQL indique qu'une instruction SQL a été préparée pour être utilisée avec un jeu de résultats par défaut (curseur côté client) à l'aide de SQLPrepare/SQLExecute (pour ODBC) ou ICommandText::Prepare/ICommandText::Execute (pour OLE DB) avec les options de curseur par défaut : défilement avant, lecture seule, taille de rowset = 1. Un événement Cursorprepare indique qu'un curseur côté serveur a été préparé sur une instruction SQL en utilisant SQLPrepare/SQLExecute (pour ODBC) ou ICommandText::Prepare/ICommandText::Execute (pour OLE DB) avec l'une des options de curseur ci-dessus réglée sur une valeur autre qu'une valeur par défaut. Un événement Exec Prepared SQL indique que l'un de ces types d'instructions préparées et existantes a été exécuté. Si ces événements apparaissent fréquemment, votre application utilise le modèle prepare/execute pour ouvrir les jeux de résultats. Dans ce cas, vous devez déterminer si vous utilisez le modèle prepare/execute correctement.

    Idéalement, une application doit préparer une instruction SQL une fois et l'exécuter à de nombreuses reprises. Cela évite à l'optimiseur de devoir compiler un nouveau plan à chaque exécution de l'instruction. Chaque fois que vous exécutez une instruction préparée, vous évitez ainsi la compilation de requête. Si vous prévoyez de n'exécuter une requête qu'une seule fois, Microsoft recommande de ne pas la préparer. La préparation et l'exécution d'une instruction SQL nécessitent trois boucles réseau : une pour préparer l'instruction, une pour l'exécuter et une pour supprimer la préparation. La préparation d'un curseur côté serveur nécessite au moins cinq boucles : une pour le préparer, une pour l'exécuter (ouvrir), une (ou plus) pour en extraire des éléments, une pour le fermer et une pour supprimer la préparation. Une simple exécution de requête ne requiert qu'une seule boucle réseau.

    Pour vérifier l'efficacité de l'utilisation du modèle prepare/execute par votre application, comparez le nombre d'occurrences de ces deux événements (prepare et execute). Le nombre d'événements Exec Prepared SQL doit être largement supérieur au nombre d'événements Prepare SQL et CursorPrepare (une estimation raisonnable est au moins trois à cinq fois plus nombreux). Ce nombre indique que les instructions préparées sont réutilisées assez souvent pour compenser le supplément de travail nécessaire à leur création. Si le nombre d'événements Prepare SQL et CursorPrepare est à peu près équivalent au nombre d'événements Exec Prepared SQL, cela peut signifier que l'application n'utilise pas correctement le modèle prepare/execute. Essayez de préparer une instruction à une reprise, puis de la réutiliser autant de fois que possible. Vous pouvez également modifier votre application de manière à ce qu'elle prépare une instruction une fois puis la réutilise aussi souvent que possible.

    L'application doit être écrite de manière spécifique pour utiliser le modèle prepare/execute de manière efficace. La durée de vie du handle d'une instruction préparée est contrôlée par la durée d'ouverture du HSTMT dans ODBC ou de l'objet ICommandText dans OLE DB. Une méthode courante consiste à obtenir un HSTMT, préparer une instruction SQL, exécuter l'instruction préparée, puis libérer le HSTMT, en perdant ainsi le handle du plan préparé. Dans ce cas, le modèle prepare/execute ne présente aucun avantage. En fait, vous constaterez peut-être une baisse des performances en raison du nombre supplémentaire de boucles réseau. L'application doit disposer d'une méthode de mise en mémoire cache du HSTMT ou de l'objet avec le handle de l'instruction préparée et d'une méthode permettant d'y accéder en vue de sa réutilisation. Ceci n'est pas effectué automatiquement par le pilote ou le fournisseur ; il appartient à l'application d'implémenter, de maintenir et d'utiliser ces informations. Si l'application ne peut pas effectuer ces opérations, pensez à utiliser des marqueurs de paramètres plutôt que la méthode prepare/execute.
  • Utilisation des marqueurs de paramètres Grâce aux marqueurs de paramètres, les applications peuvent optimiser l'exécution d'une même instruction Transact-SQL à plusieurs reprises, avec différentes valeurs d'entrée et de sortie. La première fois qu'une requête est exécutée, elle est préparée en tant que requête paramétrée, et SQL Server génère et met en mémoire cache un plan paramétré pour la requête. Pour les appels suivants à la même requête (avec des paramètres identiques ou différents), SQL Server n'a pas besoin de générer de nouveau plan de requête ; il peut réutiliser le plan de requête existant en substituant les paramètres courants.

    Si l'application utilise des marqueurs de paramètres avec des appels à SQLExecDirect (pour ODBC) ou ICommandText::Execute (pour OLE DB), le pilote ou le fournisseur empaquette automatiquement l'instruction SQL et l'exécute en tant qu'appel sp_executesql. Il n'est pas nécessaire de préparer et d'exécuter séparément l'instruction. Lorsque SQL Server reçoit un appel à sp_executesql, il recherche automatiquement un plan correspondant dans le cache de procédures, puis utilise ce plan ou en génère un nouveau.

    Pour déterminer si votre application utilise actuellement des marqueurs de paramètres, vous pouvez rechercher « sp_executesql » dans la colonne Texte de la trace du Générateur de profils. Cependant, dans le mesure où sp_executesql peut être appelé directement, toutes les instances n'indiquent pas qu'elles utilisent des marqueurs de paramètres.

    Pour plus d'informations sur le modèle prepare/execute, reportez-vous à la rubrique « Mise en mémoire cache et réutilisation du plan d'exécution » dans la documentation en ligne de SQL Server 7.0. Pour plus d'informations sur les marqueurs de paramètres, reportez-vous à la rubrique « Marqueurs de paramètres » dans la documentation en ligne de SQL Server 7.0.
  • SP:Completed Les instructions SQL dynamiques exécutées avec la commande EXECUTE s'afficheront en tant qu'événement SP:Completed avec le texte « SQL dynamique ». Développez l'événement SP:Completed et recherchez toutes les occurrences contenant la mention « SQL dynamique ». Si ces événements sont nombreux, vous pouvez améliorer les performances de l'application en utilisant sp_executesql au lieu de l'instruction EXECUTE. La procédure stockée sp_executesql permettra à SQL Server de réutiliser des plans d'exécution si la même requête est exécutée à nouveau avec des paramètres différents. Lors de l'utilisation de l'instruction EXECUTE, le plan n'est pas paramétré et il n'est pas réutilisé, à moins que la requête ne soit de nouveau exécutée avec les mêmes paramètres.

    Pour identifier les requêtes ou les procédures utilisant des événements SQL dynamiques avec l'instruction EXECUTE, notez l'ID de connexion et l'heure de démarrage de chaque événement. Dégroupez ensuite la trace (en supprimant Classe d'événements et Texte de l'en-tête Groupes ). Une fois dégroupée, la trace affiche un classement chronologique. Vous pouvez filtrer la trace par ID de connexion (sous l'onglet Filtres), puis supprimer toutes les classes d'événements à l'exception des événements SP:Starting et SP:Complete pour une meilleure lisibilité. Recherchez ensuite l'heure de démarrage de l'événement (dans le menu Edition, cliquez sur Rechercher). Vous pouvez ainsi voir quand l'événement SQL dynamique a démarré. Si l'événement s'est produit dans une procédure stockée, il apparaît entre les événements SP:Starting et SP:Completed de la procédure. Dans le cas contraire, il a été exécuté en tant que requête ad-hoc et vous pouvez utiliser d'autres colonnes de données (Nom de l'application, Nom d'utilisateur NT, etc.) pour déterminer l'emplacement à partir duquel la commande a été exécutée. Vous pouvez également ajouter des classes d'événements, telles que SQL:BatchCompleted et SQL:RPCCompleted pour déterminer le texte de la commande et son contexte d'exécution.

    Après avoir déterminé l'emplacement où l'instruction EXECUTE est utilisée, pensez à la remplacer par un appel à sp_executesql. Par exemple, examinez le scénario suivant, dans lequel la commande EXECUTE est utilisée avec le SQL dynamique. Une procédure prend une valeur Nom de table, ID et idValue comme paramètres d'entrée, puis exécute une instruction SELECT à partir de la table basée sur la valeur ID. Avec une instruction EXECUTE, la procédure devrait ressembler au code suivant :
    drop proc dynamicUsingEXECUTE
    		  go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
    		  @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
    		  with parameter. -- Notice the use of escape quotes. select @query = 'select *
    		  from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
    		  go
    En partant du principe que la requête n'est pas paramétrée automatiquement, si vous exécutez deux fois cette procédure sur la table titles de l'exemple de base de données pubs avec des valeurs différentes pour le paramètre @idValue, SQL Server devra générer un plan de requête séparé pour chaque exécution. Par exemple :
    exec dynamicUsingEXECUTE
    		  'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
    		  'title_id', 'BU7832'
    Remarque Dans cet exemple, la requête est assez simple pour que SQL Server puisse paramétrer automatiquement la requête et réutiliser le plan d'exécution. Toutefois, s'il s'agissait d'une requête complexe que SQL Server n'est pas en mesure de paramétrer automatiquement, SQL Server ne pourra pas réutiliser le plan pour la deuxième exécution si le paramètre @idValue a été modifié. La requête simple suivante permet de réduire la complexité de l'exemple.

    Vous pouvez réécrire cette procédure de manière à utiliser sp_executesql au lieu de l'instruction EXECUTE. La prise en charge de la substitution des paramètres rend sp_executesql plus efficace, car elle génère des plans d'exécution qui sont plus susceptibles d'être réutilisés par SQL Server. Par exemple :
    drop proc dynamicUsingSP_EXECUTESQL go create proc
    		  dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
    		  varchar(10) as declare @query nvarchar(4000) -- Build query string with
    		  parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
    		  @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
    		  varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'BU7832'
    Dans cet exemple, la première fois que l'instruction sp_executesql est exécutée, SQL Server génère un plan paramétré pour l'instruction SELECT à partir de titles avec title_id comme paramètre. Lors de la deuxième exécution, il réutilise le plan avec la nouvelle valeur de paramètre. Pour plus d'informations sur sp_executesql, reportez-vous aux rubriques « sp_executesql (T-SQL) » et « Utilisation de sp_executesql » dans la documentation en ligne de SQL Server 7.0.
  • SP:RECOMPILES Cet événement indique qu'une procédure stockée a été recompilée pendant l'exécution. Un grand nombre d'événements de recompilation indique que SQL Server utilise des ressources pour la compilation de requêtes plutôt que pour l'exécution de requêtes.
Si aucun de ces événements n'apparaît, l'application exécute uniquement des requêtes ad-hoc sur SQL Server. À moins que SQL Server ne détermine qu'il peut paramétrer automatiquement certaines requêtes ou que les mêmes paramètres soient utilisés à plusieurs reprises, chaque requête exécutée nécessitera la génération d'un nouveau plan d'exécution par SQL Server. L'Analyseur de performances SQL Server doit indiquer un nombre élevé de compilations SQL/seconde, ce qui peut représenter une charge importante pour l'unité centrale dans le cas d'un grand nombre d'utilisateurs concurrents. Pour remédier à cette situation, identifiez les requêtes les plus fréquemment exécutées et étudiez les avantages de créer des procédures stockées pour ces requêtes ou d'utiliser des marqueurs de paramètres ou sp_executesql.

Références

Pour plus d'informations sur l'analyse et la résolution des problèmes de performances dans SQL Server, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft.
224587 INF : Résolution des problèmes de performance d'applications sous SQL Server
224453 INF : Compréhension et résolution des problèmes de blocage sous SQL Server 7.0 ou 2000
243586 INF : Dépannage des problèmes recompilation de procédures stockées
243589 INF : Dépannage des problèmes de lenteur d'exécution des requêtes dans SQL Server 7.0 ou versions ultérieures
251004 INF : comment analyser le blocage SQL Server 7.0

Propriétés

Numéro d'article: 243588 - Dernière mise à jour: lundi 24 avril 2006 - Version: 5.2
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2000 Édition 64 bits
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Mots-clés : 
kbhowtomaster kbhowto kbinfo KB243588
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com