Utilizzo dell'hint USE PLAN nelle query con cursori
È possibile utilizzare l'hint USE PLAN nelle query che specificano richieste di cursori. Nella tabella seguente vengono illustrate le combinazioni di opzioni di scorrimento dei cursori supportate con l'hint USE PLAN per i cursori API del server, i cursori Transact-SQL che utilizzano la sintassi estesa Transact-SQL e i cursori Transact-SQL che utilizzano la sintassi ISO.
Opzioni di scorrimento (valore @scrollopt per cursori API del server) |
Hint USE PLAN supportato per cursori API del server |
Hint USE PLAN supportato per cursori Transact-SQL che utilizzano la sintassi estesa Transact-SQL |
Hint USE PLAN supportato per cursori Transact-SQL che utilizzano la sintassi ISO |
|---|---|---|---|
STATIC |
S |
S |
Non applicabile |
DYNAMIC |
N |
N |
Non applicabile |
KEYSET |
N |
N |
Non applicabile |
FORWARD_ONLY |
N |
N |
Non applicabile |
FAST_FORWARD |
S |
S |
Non applicabile |
FORWARD_ONLY STATIC |
Non applicabile |
S |
Non applicabile |
INSENSITIVE |
Non applicabile |
Non applicabile |
S |
Alle query con cursori sono associati due piani di query, anziché il piano singolo associato alle query inviate senza cursori. Tali piani possono essere di tipo OPEN, FETCH o REFRESH, in base al tipo di cursore.
Uno dei due piani di un cursore viene generato direttamente dalla query di input, mentre l'altro piano viene generato automaticamente. Tali piani vengono chiamati rispettivamente piano della query di input e piano generato. Nella tabella seguente vengono illustrati i piani generati per i cursori FAST_FORWARD e STATIC (INSENSITIVE).
Tipo di cursore |
Piano di cursore OPEN |
Piano di cursore FETCH |
Piano di cursore REFRESH |
|---|---|---|---|
FAST_FORWARD |
Non applicabile |
Query di input |
Generato |
STATIC |
Query di input |
Generato |
Non applicabile |
Talvolta, i piani di query XML per una query basata su cursori vengono visualizzati come un singolo documento XML contenente entrambi i piani. Tali piani vengono chiamati piani in due parti.
In alcuni casi, inoltre, i piani per un cursore vengono visualizzati come due piani separati. Ad esempio, in una traccia di SQL Server Profiler per un piano di query con cursori API o Transact-SQL di tipo STATIC, è possibile notare che vengono generati due diversi eventi Showplan XML For Query Compile. In tal caso, soltanto il piano della query di input (OPEN) è significativo per l'utilizzo forzato. È consigliabile utilizzare il piano della query di input in un hint USE PLAN. Viene creato inoltre un semplice piano generato (FETCH) ma non è né necessario né consentito per l'utilizzo forzato. È possibile riconoscere il piano della query di input (OPEN) in quanto è il piano che raccoglie innanzitutto il set di righe corrispondente alla query basata su cursori.
.gif "Nota importante") Importante |
|---|
Non provare a forzare l'utilizzo di un piano non di cursore per una query basata su cursori o viceversa. In tal caso, l'utilizzo forzato del piano potrebbe avere esito negativo, anche se la query basata su cursori e la query non basata su cursori sono identiche. |
Per forzare l'utilizzo di un piano con l'hint USE PLAN per tipi di cursori specifici, è possibile utilizzare i tipi seguenti di output dei piani di query XML che descrivono i piani di cursore:
Un piano in due parti per il cursore
Un piano della query di input costituito da un'unica parte per il cursore
Il piano di cursore di cui si forza l'utilizzo può essere un piano di query XML ottenuto tramite uno dei meccanismi seguenti:
Eventi di traccia di SQL Server Profiler basati sul linguaggio XML, inclusi gli eventi Showplan XML, Showplan XML For Query Compile e Showplan XML Statistics Profile.
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
Viste e funzioni a gestione dinamica, come ad esempio la query seguente:
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Analisi dell'utilizzo di cursori API del server nelle applicazioni
Spesso, le applicazioni DB Library, ODBC, ADO e OLEDB interagiscono con SQL Server tramite i cursori API del server. È possibile visualizzare le chiamate inoltrate alle stored procedure dei cursori API del server esaminando gli eventi SQL Server ProfilerRPC:Starting quando è in esecuzione un'applicazione creata tramite una di queste interfacce.
Esempio: Utilizzo forzato di un piano in una query con un cursore
In questo esempio si presuppone che venga utilizzata un'applicazione in grado di interagire con il database AdventureWorks tramite cursori ODBC e che si desideri forzare l'utilizzo di un piano per una query inviata a SQL Server tramite una routine dei cursori API del server. Per forzare l'utilizzo di un piano per una query inviata tramite una routine dei cursori API, raccogliere il piano e quindi creare una guida di piano corrispondente. Impostare l'applicazione in modo che esegua di nuovo la query ed esaminare il piano per verificare che sia stato forzato il relativo utilizzo.
Passaggio 1: Raccolta del piano
Avviare una traccia di SQL Server Profiler e selezionare gli eventi Showplan XML e RPC:Starting. Impostare l'applicazione in modo che esegua la query per la quale si desidera forzare l'utilizzo del piano. Fare clic sull'evento RPC:Starting generato. Si supponga che l'evento RPC:Starting includa i dati seguenti:
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
Per raccogliere il piano per la query, fare clic con il pulsante destro del mouse sull'evento di traccia Showplan XML contenente il piano della query di input visualizzata come argomento nell'istruzione sp_cursorprepexec sopra indicata e quindi scegliere Estrai dati eventi. Salvare i dati dell'evento Showplan XML in un file CursorPlan.SQLPlan sul desktop. Copiare il file CursorPlan.SQLPlan in CursorPlan.txt. In SQL Server Management Studio aprire CursorPlan.txt in una finestra dell'editor. Al fine di velocizzare le operazioni successive, utilizzare Trova e sostituisci per sostituire ogni virgoletta singola (') nel piano con quattro virgolette singole (''''). Salvare CursorPlan.txt.
Passaggio 2: Creazione della guida di piano per l'utilizzo forzato del piano
Creare una guida di piano scrivendo ed eseguendo l'istruzione sp_create_plan_guide seguente per forzare l'utilizzo del piano. La definizione della guida di piano include il piano XML acquisito nel passaggio precedente in un hint per la query USE PLAN all'interno di tale guida.
Durante la scrittura della definizione della guida di piano, incollare il contenuto del file CursorPlan.txt nella posizione appropriata all'interno dell'argomento @hints, subito dopo 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>'')'
Passaggio 3: Esecuzione della query e verifica dell'applicazione della guida di piano
Impostare l'applicazione in modo che esegua di nuovo la query e raccolga il piano di esecuzione XML corrispondente tramite l'evento Showplan XML in SQL Server Profiler.
Fare clic sull'evento Showplan XML relativo al piano. Verificare che il piano corrisponda a quello di cui è stato forzato l'utilizzo nella guida.
Query basate su cursori con parametri
Se la query basata su cursori API del server per la quale si desidera creare una guida di piano contiene parametri, verificare di aver incluso sia la stringa dell'istruzione che la stringa di definizione dei parametri visualizzata nell'evento SQL Server ProfilerRPC:Starting all'interno della definizione della guida di piano. La stringa di definizione dei parametri è necessaria inoltre per ottenere una corrispondenza valida per la guida di piano, come nel caso delle query con parametri inviate tramite la stored procedure sp_executesql.
Vedere anche