Migrieren von Abfrageplänen

Artikel
03/06/2017

In den meisten Fällen führt ein Upgrade einer Datenbank auf die neueste Version von SQL Server zu einer verbesserten Abfrageleistung. Bei unternehmenswichtigen Abfragen, deren Leistung sorgfältig optimiert wurde, möchten Sie jedoch möglicherweise vor dem Upgrade die entsprechenden Abfragepläne aufzeichnen. Zu diesem Zweck können Sie für jede Abfrage eine Planhinweisliste erstellen. Falls der Abfrageoptimierer nach der Aktualisierung für immer mehr Abfragen einen weniger effizienten Plan wählt, können Sie die Planhinweislisten aktivieren und den Abfrageoptimierer zwingen, die alten Pläne zu verwenden.

Führen Sie die folgenden Schritte durch, um vor der Aktualisierung Planhinweislisten zu erstellen:

Notieren Sie den aktuellen Plan für jede unternehmenskritische Abfrage, indem Sie die sp_create_plan_guide gespeicherte Prozedur verwenden und den Abfrageplan im USE PLAN-Abfragehinweis angeben.
Vergewissern Sie sich, dass die Planhinweisliste auf die Abfrage angewendet wird.
Aktualisieren Sie die Datenbank auf die neuere Version von SQL Server.

Die Pläne werden in der aktualisierten Datenbank in den Planhinweislisten persistent gespeichert und können gegebenenfalls herangezogen werden, falls es nach dem Upgrade zu einer Regression bei der Planverwendung kommt.

Wir empfehlen jedoch, die Planhinweislisten nicht sofort nach der Aktualisierung zu aktivieren, da Sie sonst eventuell nicht die Vorzüge besserer Pläne in der neuen Version oder die Vorteile von Neukompilierungen aufgrund aktualisierter Statistiken nutzen können.
Falls nach der Aktualisierung weniger effiziente Pläne gewählt werden, können Sie alle oder einen Teil der Planhinweislisten aktivieren, um die neuen Pläne zu überschreiben.

Beispiel

Im folgenden Beispiel wird gezeigt, wie Sie vor der Aktualisierung durch Erstellen einer Planhinweisliste den Plan für eine Abfrage aufzeichnen können.

Schritt 1: Abrufen des Plans

Der in der Planhinweisliste aufgezeichnete Abfrageplan muss im XML-Format vorliegen. Abfragepläne im XML-Format können auf folgende Weise erstellt werden:

Mit SET SHOWPLAN_XML
SET STATISTICS XML
Abfragen der query_plan Spalte der sys.dm_exec_query_plan dynamischen Verwaltungsfunktion.
Die SQL Server Profiler Showplan XML, Showplan XML Statistics Profile und Showplan XML For Query Compile-Ereignisklassen.

Im folgenden Beispiel wird der Abfrageplan für die Anweisung SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC; abgerufen, indem dynamische Verwaltungssichten abgefragt werden.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO

Schritt 2: Erstellen des Planhinweises zum Erzwingen des Plans

Fügen Sie den in der Planhinweisliste enthaltenen (und mit einer der oben beschriebenen Methoden bezogenen) Abfrageplan im XML-Format als Zeichenfolgenliteral in den USE PLAN-Abfragehinweis ein, der in der OPTION-Klausel von sp_create_plan_guide angegeben ist.

Grenzen Sie alle im XML-Plan enthaltenen Anführungszeichen (') durch ein zweites Anführungszeichen ab, bevor Sie die Planhinweisliste erstellen. Ein Plan, der WHERE A.varchar = 'This is a string' enthält, muss z. B. abgegrenzt werden, indem der Code zu WHERE A.varchar = ''This is a string'' geändert wird.

Im folgenden Beispiel wird eine Planhinweisliste für den in Schritt 1 abgerufenen Abfrageplan erstellt und der XML-Showplan für die Abfrage in den @hints-Parameter eingefügt. Aus Platzgründen ist im Beispiel nur ein Teil des Showplans angegeben.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@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>'')';  
GO

Schritt 3: Überprüfen, ob die Planhinweisliste auf die Abfrage angewendet wird

Führen Sie die Abfrage noch einmal aus, und überprüfen Sie den erzeugten Abfrageplan. Dieser Plan sollte mit dem in der Planhinweisliste angegebenen Plan übereinstimmen.

Weitere Informationen

sp_create_plan_guide (Transact-SQL)
Abfragehinweise (Transact-SQL)
Planhinweislisten

Freigeben über