Udostępnij za pośrednictwem

Poradniki planu

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Ważny

wskazówki magazynu zapytań zapewniają łatwiejszą metodę kształtowania planów zapytań bez zmieniania kodu aplikacji. Wskazówki dotyczące magazynu zapytań są prostsze niż przewodniki dotyczące planu. Wskazówki dotyczące magazynu zapytań są dostępne w usłudze Azure SQL Database, bazie danych SQL w usłudze Microsoft Fabric, usłudze Azure SQL Managed Instance i w programie SQL Server 2022 (16.x) i nowszych wersjach.

Przewodniki dotyczące planu umożliwiają optymalizowanie wydajności zapytań, gdy nie można lub nie chcesz bezpośrednio zmieniać tekstu rzeczywistego zapytania w programie SQL Server. Przewodniki dotyczące planu wpływają na optymalizację zapytań, dołączając do nich wskazówki dotyczące zapytań lub stały plan zapytania. Przewodniki planu mogą być przydatne, gdy niewielki podzbiór zapytań w aplikacji bazy danych dostarczonej przez dostawcę innej firmy nie działa zgodnie z oczekiwaniami. W przewodniku po planie należy określić instrukcję Transact-SQL, która ma zostać zoptymalizowana, oraz klauzulę OPTION zawierającą wskazówki dotyczące zapytań, których chcesz użyć, lub określony plan zapytania, którego chcesz użyć do optymalizacji zapytania. Po wykonaniu zapytania program SQL Server dopasowuje instrukcję Transact-SQL do przewodnika planowania i dołącza do zapytania klauzulę OPTION w czasie wykonywania lub używa określonego planu zapytania. Ponieważ optymalizator zapytań programu SQL Server zazwyczaj wybiera najlepszy plan wykonania zapytania, zalecamy używanie tylko przewodników planu jako ostateczności dla doświadczonych deweloperów i administratorów baz danych.

Łączna liczba przewodników dotyczących planu, które można utworzyć, jest ograniczona tylko przez dostępne zasoby systemowe. Niemniej jednak przewodniki dotyczące planu powinny być ograniczone do zapytań o znaczeniu krytycznym, które są przeznaczone do poprawy lub stabilizacji wydajności. Przewodniki planu nie powinny być używane do wywierania wpływu na większość obciążenia zapytań wdrożonej aplikacji.

Wynikowy plan wykonania wymuszony przez tę funkcję będzie taki sam lub podobny do wymuszanego planu. Ponieważ plan wynikowy może nie być identyczny z planem określonym w przewodniku planu, wydajność planów może się różnić. W rzadkich przypadkach różnica wydajności może być znacząca i ujemna; w takim przypadku administrator musi usunąć wymuszony plan.

Przewodniki planowania nie mogą być używane w każdej wersji programu Microsoft SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Features Supported by the Editions of SQL Server 2016. Przewodniki planu są widoczne w dowolnej wersji. Możesz również dołączyć bazę danych zawierającą przewodniki planu do dowolnej edycji. Przewodniki planu pozostają nienaruszone podczas przywracania lub dołączania bazy danych do uaktualnionej wersji programu SQL Server.

Rodzaje przewodników planu

Można utworzyć następujące typy przewodników planu.

Przewodnik po planie OBIEKTÓW

Przewodnik po planie OBIEKTÓW jest zgodny z zapytaniami wykonywanymi w kontekście Transact-SQL procedur składowanych, funkcji zdefiniowanych przez użytkownika skalarnych, funkcji zdefiniowanych przez wiele instrukcji tabeli zdefiniowanych przez użytkownika i wyzwalaczy DML.

Załóżmy, że następująca procedura składowana, która przyjmuje parametr @Country_region, znajduje się w aplikacji bazy danych wdrożonej w bazie danych AdventureWorks2022:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Załóżmy, że ta procedura składowana została skompilowana i zoptymalizowana dla @Country_region = N'AU' (Australia). Jednak ze względu na to, że istnieje stosunkowo niewiele zamówień sprzedaży pochodzących z Australii, wydajność spada, gdy zapytanie wykonuje przy użyciu wartości parametrów krajów/regionów z większą liczbą zamówień sprzedaży. Ponieważ większość zamówień sprzedaży pochodzi ze Stanów Zjednoczonych, plan zapytania wygenerowany dla @Country_region = N'US' prawdopodobnie będzie działał lepiej dla wszystkich możliwych wartości parametru @Country_region.

Ten problem można rozwiązać, modyfikując procedurę składowaną, aby dodać wskazówkę OPTIMIZE FOR do zapytania. Jednak ponieważ procedura składowana znajduje się w wdrożonej aplikacji, nie można bezpośrednio zmodyfikować kodu aplikacji. Zamiast tego możesz utworzyć następujący przewodnik po planie w bazie danych AdventureWorks2022.

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Po wykonaniu zapytania określonego w instrukcji sp_create_plan_guide zapytanie jest modyfikowane przed optymalizacją w celu uwzględnienia klauzuli OPTIMIZE FOR (@Country = N''US'').

Przewodnik po planie SQL

Przewodnik planu SQL odpowiada zapytaniom, które są wykonywane w kontekście autonomicznych instrukcji Transact-SQL oraz wsadów, które nie należą do żadnego obiektu bazy danych. Przewodniki planu oparte na języku SQL mogą być również używane do dopasowywania zapytań, które są parametryzowane do określonego formatu. Przewodniki dotyczące planu SQL dotyczą autonomicznych instrukcji Transact-SQL i partii. Często aplikacja przesyła te zapytania za pomocą procedury składowanej systemu sp_executesql. Rozważmy na przykład następującą samodzielną partię:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Aby zapobiec generowaniu równoległego planu wykonywania dla tego zapytania, utwórz następujący przewodnik po planie i ustaw wskazówkę zapytania MAXDOP na 1 w parametrze @hints.

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';

W innym przykładzie rozważmy następującą instrukcję SQL przesłaną przy użyciu sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Aby utworzyć unikatowy plan dla każdego wykonania tego zapytania, utwórz następujący przewodnik po planie i użyj wskazówki OPTION (RECOMPILE) zapytania w parametrze @hints.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Ważny

Wartości podane dla @module_or_batch i @params argumentów instrukcji sp_create_plan guide muszą być zgodne z odpowiednim tekstem przesłanym w rzeczywistym zapytaniu. Aby uzyskać więcej informacji, zobacz sp_create_plan_guide (Transact-SQL) i Używanie profilera programu SQL Server do tworzenia i testowania przewodników dotyczących planu.

Przewodniki planu SQL można również utworzyć dla zapytań, które przyjmują tę samą postać, gdy opcja PARAMETRYZACJI bazy danych jest USTAWIONA na WYMUSZENIE, lub gdy zostanie utworzony przewodnik planu typu SZABLON, który określa sparametryzowaną klasę zapytań.

Przewodnik po planie szablonu

Przewodnik planu szablonu odpowiada na samodzielne zapytania, które są parametryzowane do określonej postaci. Te przewodniki planu są używane do zastępowania bieżącej opcji PARAMETRYZACJI bazy danych dla klasy zapytań.

Przewodnik planu szablonu można utworzyć w jednej z następujących sytuacji:

  • Opcja bazy danych PARAMETRIZATION jest ustawiona na FORCED, ale istnieją zapytania, które chcesz skompilować zgodnie z regułami Simple Parameterization.

  • Opcja bazy danych PARAMETRYZACJI to SIMPLE (ustawienie domyślne), ale chcesz, aby wymuszonej parametryzacji być wypróbowana w klasie zapytań.

Przewodnik dopasowywania planów do wymagań

Przewodniki planowania są powiązane z bazą danych, w której są tworzone. W związku z tym do zapytania można dopasować tylko te przewodniki planu, które znajdują się w bazie danych aktualnej w momencie wykonywania zapytania. Jeśli na przykład AdventureWorks2022 jest bieżącą bazą danych, a następujące zapytanie wykonuje:

SELECT FirstName, LastName FROM Person.Person;

Jedynie przewodniki planu w bazie danych AdventureWorks2022 mogą być dopasowane do tego zapytania. Jeśli jednak AdventureWorks2022 jest bieżącą bazą danych, a następujące instrukcje są uruchamiane:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Tylko przewodniki planowe w DB1 mogą być dopasowane do zapytania, ponieważ zapytanie jest wykonywane w kontekście DB1.

W przypadku przewodników planu opartych na języku SQL lub szablonie, SQL Server dopasowuje wartości argumentów @module_or_batch i @params do zapytania, porównując je znak po znaku. Oznacza to, że należy podać tekst dokładnie tak, jak program SQL Server odbiera go w rzeczywistej partii.

Gdy @type = "SQL" i @module_or_batch jest ustawiona na wartość NULL, wartość @module_or_batch jest ustawiona na wartość @stmt. Oznacza to, że wartość statement_text musi być podana w identycznym formacie, znak dla znaku, ponieważ jest przesyłana do programu SQL Server. W celu ułatwienia tego dopasowania nie jest wykonywana żadna konwersja wewnętrzna.

Jeśli zarówno zwykły przewodnik planu (SQL lub OBJECT), jak i przewodnik planu szablonu mogą mieć zastosowanie do instrukcji, będzie używany tylko zwykły przewodnik planu.

Notatka

Pakiet zawierający instrukcję, do której chcesz utworzyć przewodnik planu, nie może zawierać instrukcji USE database.

Wpływ przewodnika planowania na pamięć podręczną planu

Utworzenie przewodnika dotyczącego planu w module spowoduje usunięcie planu zapytania dla tego modułu z pamięci podręcznej planu. Utworzenie przewodnika planu typu OBJECT lub SQL w partii usuwa plan zapytania dla partii, która ma tę samą wartość skrótu. Tworzenie przewodnika planu typu TEMPLATE usuwa wszystkie partie zawierające pojedyncze instrukcje z pamięci podręcznej planu w tej bazie danych.

Zadanie Temat
Opisuje sposób tworzenia przewodnika po planie. Tworzenie Nowego Przewodnika po Planie
Opisuje sposób tworzenia przewodnika planu dla sparametryzowanych zapytań. Tworzenie przewodnika planu dla sparametryzowanych zapytań
Opisuje sposób kontrolowania zachowania parametryzacji zapytań przy użyciu przewodników kompilacji. określanie zachowania parametryzacji zapytań przy użyciu przewodników planu
Opisuje sposób dołączania utrwalonego planu zapytania w przewodniku planu. Zastosuj Stały Plan Zapytania do Przewodnika po Planie
Opisuje sposób określania wskazówek dotyczących zapytań w przewodniku planowania. Dołączanie wskazówek dotyczących zapytań do Przewodnika po Planie
Opisuje sposób wyświetlania właściwości planu przewodnika. Właściwości przewodnika po planie
W tym artykule opisano sposób używania programu SQL Server Profiler do tworzenia i testowania przewodników dotyczących planu. Użyj profilera SQL Server do tworzenia i testowania przewodników planu
Opisuje sposób sprawdzania poprawności przewodników dotyczących planu. przewodniki weryfikacji planu po uaktualnieniu

Zobacz też

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)