Freigeben über

  • Artikel

Verwenden des USE PLAN-Abfragehinweises für Abfragen mit Cursorn

Sie können den USE PLAN-Abfragehinweis für Abfragen verwenden, die Cursoranforderungen angeben. Die folgende Tabelle zeigt die Cursorscroll-Optionskombinationen, die mit USE PLAN für API-Servercursor unterstützt werden, Transact-SQL-Cursor, die erweiterte Transact-SQL-Syntax verwenden und Transact-SQL-Cursor, die ISO-Syntax verwenden.

Scrolloption (@scrollopt-Wert für API-Servercursor)

USE PLAN unterstützt für API-Servercursor

USE PLAN unterstützt für Transact-SQL Cursor, die erweiterte Transact-SQL-Syntax verwenden

USE PLAN unterstützt für Transact-SQL-Cursor, die erweiterte ISO-Syntax verwenden

STATIC

J

J

Nicht anwendbar

DYNAMIC

N

N

Nicht anwendbar

KEYSET

N

N

Nicht anwendbar

FORWARD_ONLY

N

N

Nicht anwendbar

FAST_FORWARD

J

J

Nicht anwendbar

FORWARD_ONLY STATIC

Nicht anwendbar

J

Nicht anwendbar

INSENSITIVE

Nicht anwendbar

Nicht anwendbar

J

Abfragen mit Cursorn sind zwei Abfragepläne statt des einzelnen Plans zugeordnet, der Abfragen zugeordnet ist, die ohne Cursor übermittelt werden. Diese Pläne können abhängig vom Typ des Cursors vom Typ OPEN, FETCH oder REFRESH sein.

Einer der beiden Pläne für einen Cursor wird direkt aus der Eingabeabfrage generiert, der andere Plan wird automatisch generiert. Diese Pläne werden als Eingabeabfrageplan bzw. generierter Plan bezeichnet. Die folgende Tabelle zeigt die Pläne, die für FAST_FORWARD- und STATIC (INSENSITIVE)-Cursor generiert werden.

Cursortyp

Open-Cursorplan

Fetch-Cursorplan

Refresh-Cursorplan

FAST_FORWARD

Nicht anwendbar

Eingabeabfrage

Generierter Plan

STATIC

Eingabeabfrage

Generierter Plan

Nicht anwendbar

Die XML-Abfragepläne für eine Cursorabfrage sind manchmal in einem einzigen XML-Dokument gespeichert, das beide Pläne enthält. Diese Pläne werden als zweiteilige Pläne bezeichnet.

Die Pläne für einen Cursor werden manchmal auch als zwei separate Pläne gespeichert. In einer SQL Server Profiler-Ablaufverfolgung für einen STATIC API- oder Transact-SQL-Cursorabfrageplan können Sie z. B. erkennen, dass zwei verschiedene Showplan XML For Query Compile-Ereignisse generiert werden. Nur der Eingabeabfrageplan (OPEN) ist in diesem Fall für die Planerzwingung wichtig. Sie sollten den Eingabeabfrageplan in einem USE PLAN-Hinweis verwenden. Ein einfacher generierter (FETCH) Plan wird ebenfalls erstellt, ist jedoch für die Planerzwingung nicht erforderlich bzw. zulässig. Sie können den Eingabeabfrageplan (OPEN) erkennen, weil es sich um den Plan handelt, der zuerst den Satz von Zeilen abruft, die der Cursorabfrage entsprechen.

Wichtiger HinweisWichtig

Versuchen Sie nicht, einen Nichtcursorplan für eine Cursorabfrage zu erzwingen oder umgekehrt. Die Planerzwingung kann in diesem Fall selbst dann fehlschlagen, wenn die Cursorabfrage und die Nichtcursorabfrage identisch sind.

Die folgenden Typen von XML-Abfrageplanausgaben, die Cursorpläne beschreiben, können zum Erzwingen eines Plans mit USE PLAN für bestimmte Cursortypen verwendet werden:

  • Ein zweiteiliger Plan für den Cursor.

  • Ein einteiliger Eingabeabfrageplan für den Cursor.

Der Cursorplan, den Sie erzwingen, kann ein Plan sein, der durch einen der folgenden Mechanismen zum Abrufen eines XML-Abfrageplans abgerufen wurde:

  • XML-basierte SQL Server Profiler-Ablaufverfolgungsereignisse. Bei diesen Ereignissen kann es sich um Showplan XML, Showplan XML For Query Compile und Showplan XML Statistics Profile handeln.

  • SET SHOWPLAN_XML ON

  • SET STATISTICS XML ON

  • Dynamische Verwaltungssichten und -funktionen wie z. B. die folgende Abfrage:

    SELECT *
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Beobachten der API-Servercursorverwendung durch Anwendungen

DB Library-, ODBC-, ADO- und OLE DB-Anwendungen interagieren häufig mithilfe von API-Servercursorn mit SQL Server. Sie können die Aufrufe anzeigen, die an gespeicherte API-Servercursorprozeduren übermittelt werden, indem Sie die SQL Server ProfilerRPC:Starting-Ereignisse untersuchen, wenn eine Anwendung ausgeführt wird, die mithilfe einer dieser Schnittstellen erstellt wurde.

Beispiel: Erzwingen eines Plans für eine Abfrage mit einem Cursor

Dieses Beispiel geht davon aus, dass Sie eine Anwendung verwenden, die mit der AdventureWorks-Datenbank mithilfe von ODBC-Cursorn interagiert, und dass Sie den Plan für eine an SQL Server übermittelte Abfrage mithilfe einer API-Servercursorroutine erzwingen möchten. Um den Plan zu erzwingen, ermitteln Sie einen Plan für eine Abfrage, der über eine Cursor-API-Routine übermittelt wird, und erstellen Sie dann einen Planhinweis, um den Plan für die betreffende Abfrage zu erzwingen. Lassen Sie die Anwendung die Abfrage nochmals ausführen, und untersuchen Sie dann den Plan daraufhin, ob er erzwungen wurde.

Schritt 1: Ermitteln des Plans

Starten Sie eine SQL Server Profiler-Ablaufverfolgung, und wählen Sie die Showplan XML- und RPC:Starting-Ereignisse aus. Lassen Sie die Anwendung die Abfrage ausführen, für die der Plan erzwungen werden soll. Klicken Sie auf das RPC:Starting-Ereignis, das generiert wird. Angenommen, das RPC:Starting-Ereignis weist die folgenden Textdaten auf:

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

Ermitteln Sie den Plan für die Abfrage, indem Sie mit der rechten Maustaste auf das Showplan XML-Ablaufverfolgungsereignis klicken, das den Eingabeabfrageplan für die Abfrage enthält, die als Argument für die sp_cursorprepexec-Anweisung oben angezeigt wird, und wählen Sie dann Extract Event Data aus. Speichern Sie die Ereignisdaten (XML-Showplan) in einer Datei namens CursorPlan.SQLPlan auf dem Desktop. Kopieren Sie die Datei CursorPlan.SQLPlan nach CursorPlan.txt. Öffnen Sie die Datei CursorPlan.txt in SQL Server Management Studio in einem Editorfenster. Verwenden Sie die Funktion Suchen und Ersetzen, um jedes einzelne Anführungszeichen (') im Plan durch vier einfache Anführungszeichen ('''') zu ersetzen, damit Sie später Zeit sparen. Speichern Sie die Datei CursorPlan.txt.

Schritt 2: Erstellen des Planhinweises zum Erzwingen des Plans

Erstellen Sie einen Planhinweis, indem Sie die folgende sp_create_plan_guide-Anweisung zum Erzwingen des Plans schreiben und ausführen. Die Planhinweisdefinition enthält den im vorherigen Schritt erfassten XML-Plan in einem USE PLAN-Abfragehinweis im Planhinweis.

Wenn Sie diese Planhinweisdefinition schreiben, fügen Sie den Inhalt der Datei CursorPlan.txt an der entsprechenden Position in das @hints-Argument ein (direkt hinter 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>'')'

Schritt 3: Ausführen der Abfrage und Überprüfen, ob der Planhinweis darauf angewendet wurde

Lassen Sie die Anwendung die Abfrage nochmals ausführen, und ermitteln Sie den XML-Ausführungsplan mithilfe des XML Showplan-Ereignisses in SQL Server Profiler.

Klicken Sie auf das XML Showplan-Ereignis für den Plan. Sie sollten erkennen können, dass der Plan der im Planhinweis erzwungene Plan ist.

Parametrisierte Cursorabfragen

Wenn die API-Servercursorabfrage, für die Sie einen Planhinweis erstellen möchten, parametrisiert ist, vergewissern Sie sich, dass die Planhinweisdefinition sowohl die Anweisungszeichenfolge als auch die Parameterdefinitions-Zeichenfolge enthält, die im SQL Server ProfilerRPC:Starting-Ereignis angezeigt wird. Die Parameterdefinitions-Zeichenfolge ist auch zum Abrufen einer erfolgreichen Planhinweiszuordnung erforderlich, ebenso wie bei parametrisierten Abfragen, die mithilfe von sp_executesql übermittelt werden.