Utilisation de l'indicateur de requête USE PLAN sur les requêtes avec curseurs
Vous pouvez utiliser l'indicateur de requête USE PLAN avec des requêtes spécifiant des demandes de curseur. Le tableau suivant présente les options de défilement de curseur prises en charge en cas d'utilisation de USE PLAN pour les curseurs côté serveur d'API (curseurs Transact-SQL qui utilisent la syntaxe étendue Transact-SQL) et les curseurs Transact-SQL qui utilisent la syntaxe ISO.
Option de défilement (valeur @scrollopt pour les curseurs de serveur API) |
USE PLAN pris en charge pour les curseurs de serveur API |
USE PLAN pris en charge pour les curseurs Transact-SQL qui utilisent la syntaxe étendue Transact-SQL |
USE PLAN pris en charge pour les curseurs Transact-SQL qui utilisent la syntaxe ISO |
|---|---|---|---|
STATIC |
O |
O |
Non applicable |
DYNAMIC |
N |
N |
Non applicable |
KEYSET |
N |
N |
Non applicable |
FORWARD_ONLY |
N |
N |
Non applicable |
FAST_FORWARD |
O |
O |
Non applicable |
FORWARD_ONLY STATIC |
Non applicable |
O |
Non applicable |
INSENSITIVE |
Non applicable |
Non applicable |
Y |
Il est possible d'associer deux plans de requête aux requêtes avec curseurs tandis qu'un seul est disponible pour les requêtes soumises sans curseur. Ces plans peuvent être de type OPEN, FETCH ou REFRESH, selon le type du curseur.
L'un des deux plans existants pour un curseur est directement généré d'après la requête d'entrée, l'autre est automatiquement généré en conséquence. Ces plans sont appelés, respectivement, dans le plan de requête d'entrée et dans le plan généré. Le tableau suivant montre les plans qui sont générés pour les curseurs FAST_FORWARD et STATIC (INSENSITIVE).
Type de curseur |
Plan de curseur OPEN |
Plan de curseur FETCH |
Plan de curseur REFRESH |
|---|---|---|---|
FAST_FORWARD |
Non applicable |
Requête d'entrée |
Généré |
STATIC |
Requête d'entrée |
Généré |
Non applicable |
Il arrive que les plans de requête XML d'une requête curseur apparaissent sous forme d'un seul document où sont réunis les deux plans. Ces plans sont alors dits plans en deux parties.
Les plans de curseur apparaissent quelquefois sous forme de deux plans distincts. Par exemple, dans une trace du SQL Server Profiler établie pour un plan de requête de curseur API ou Transact-SQL statique, vous pouvez constater que deux événements Showplan XML For Query Compile sont générés. Dans ce cas, seul le plan (OPEN) de la requête d'entrée peut être pris en compte en cas d'application forcée d'un plan. Vous devez mentionner le plan de requête d'entrée dans un indicateur de requête USE PLAN. Un unique plan (FETCH) généré est également créé, mais n'est pas requis, ni autorisé, pour l'application forcée de plan. Le plan (OPEN) de la requête d'entrée est facile à reconnaître puisque c'est le plan qui regroupe en premier l'ensemble de lignes qui correspond à la requête de curseur.
Important
Ne tentez pas d'appliquer de force un plan non-curseur pour une requête de curseur, ou inversement. L'application forcée risque d'échouer même si les requêtes non-curseur et curseur sont identiques.
Les types de sorties de plan de requête XML suivants, qui décrivent les plans de curseur, peuvent servir à appliquer de force un plan pour des types de curseur spécifiques :
Plan en deux parties pour le curseur
Plan de requête d'entrée en une partie pour le curseur
Le plan de curseur que vous appliquez de force peut être un plan obtenu par le biais de l'un des mécanismes suivants :
Événements de trace du SQL Server Profiler basés sur XML. Ces événements peuvent inclure Showplan XML, Showplan XML For Query Compile et Showplan XML Statistics Profile.
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
Vues et fonctions de gestion dynamique telles que la requête suivante :
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Étude de l'utilisation de curseur de serveur API par les applications
Les applications DB Library, ODBC, ADO et OLEDB communiquent fréquemment avec SQL Server à l'aide de curseurs de serveur API. Vous pouvez voir les appels passés aux procédures stockées de curseur de serveur API en examinant les événements RPC:Starting du SQL Server Profiler lors de l'exécution d'une application construite à l'aide de l'une de ces interfaces.
Exemple : Application forcée d'un plan sur une requête avec curseur
Cet exemple part du principe que vous utilisez une application qui communique avec la base de données AdventureWorks2008R2 par le biais de curseurs ODBC et que vous voulez forcer l'application d'un plan pour une requête soumise à SQL Server via une routine de curseur de serveur API. Pour appliquer de force le plan, récupérez un plan pour une requête soumise par une routine API de curseur, puis créez un repère de plan pour forcer l'application du plan lors de cette requête. Demandez à l'application d'exécuter une nouvelle fois l'application et étudiez le plan pour vérifier qu'il a été appliqué de force.
Étape 1 : récupération du plan
Lancez une trace du SQL Server Profiler, puis sélectionnez les événements Showplan XML et RPC:Starting. Demandez à l'application d'exécuter la requête pour laquelle vous voulez forcer l'application du plan. Cliquez sur l'événement RPC:Starting qui est généré. Supposons que l'événement RPC:Starting contienne les données texte suivantes :
DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=8
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 OUTPUT,@p2 OUTPUT,NULL,N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT
SELECT @p1, @p2, @p5, @p6, @p7
Récupérez le plan pour la requête. Pour cela, cliquez avec le bouton droit sur l'événement de trace Showplan XML contenant le plan de requête d'entrée pour la requête qui apparaît comme argument de l'instruction sp_cursorprepexec ci-dessous, puis sélectionnez Extraire les données d'événement. Enregistrez les données d'événement (XML showplan) dans un fichier CursorPlan.SQLPlan sur le Bureau. Copiez le fichier CursorPlan.SQLPlan dans CursorPlan.txt. Dans SQL Server Management Studio, ouvrez CursorPlan.txt dans une fenêtre de l'éditeur. Pour gagner du temps par la suite, utilisez la fonction Rechercher et remplacer pour remplacer chaque apostrophe (') du plan par quatre apostrophes (''''). Enregistrez CursorPlan.txt.
Étape 2 : création du repère de plan nécessaire à l'application forcée du plan
Créez un repère de plan en écrivant et en exécutant l'instruction sp_create_plan_guide suivante pour forcer le plan. Cette définition intègre le plan XML capturé au cours de l'étape précédente dans un indicateur de requête USE PLAN présent dans le repère de plan.
Lors de l'élaboration de la définition du repère de plan, collez le contenu de CursorPlan.txt dans l'argument @hints à la position voulue (soit juste après OPTION(USE PLAN N'').
exec sp_create_plan_guide
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan'''' Version=''''0.5'''' Build=''''9.00.1116''''><BatchSequence><Batch><Statements><StmtSimple>
…
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'')'
Étape 3 : exécution de la requête pour vérifier que le repère de plan a bien été appliqué
Demandez à l'application d'exécuter une nouvelle fois la requête, puis récupérez son plan d'exécution XML à l'aide de l'événement XML Showplan dans le SQL Server Profiler.
Cliquez sur l'événement XML Showplan correspondant au plan. Vous devez constater que le plan est bien celui que vous avez appliqué de force dans le repère de plan.
Requêtes curseur paramétrées
Si la requête de curseur de serveur API pour laquelle vous voulez créer un repère de plan est paramétrée, veillez à bien inclure dans la définition du repère de plan la chaîne d'instruction et la chaîne de définition du paramètre que vous voyez dans l'événement RPC:Starting du SQL Server Profiler. La chaîne de définition du paramètre est également requise pour obtenir une parfaite correspondance du repère de plan, comme dans le cas des requêtes paramétrées soumises à l'aide de sp_executesql.