Udostępnij za pośrednictwem

Określanie zachowania parametryzacji zapytań za pomocą przewodników planu

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Jeśli opcja bazy danych PARAMETRYZACJI jest ustawiona na SIMPLE, optymalizator zapytań programu SQL Server może zdecydować się na sparametryzowanie zapytań. Oznacza to, że wszystkie wartości literału zawarte w zapytaniu są zastępowane parametrami. Ten proces jest określany jako prosta parametryzacja. Gdy parametryzacja SIMPLE jest w mocy, nie można kontrolować, które zapytania są sparametryzowane i które zapytania nie są. Można jednak określić, że wszystkie zapytania w bazie danych mają być sparametryzowane, ustawiając opcję PARAMETRYZACJA bazy danych na FORCED. Ten proces jest określany jako wymuszona parametryzacja.

Zachowanie parametryzacji bazy danych można zastąpić za pomocą przewodników dotyczących planów w następujący sposób:

  • Jeśli opcja bazy danych PARAMETRYZACJI jest ustawiona na SIMPLE, możesz określić, że wymuszona parametryzacja jest podejmowana w określonej klasie zapytań. W tym celu należy utworzyć przewodnik po planie SZABLONu w sparametryzowanej formie zapytania i określić wskazówkę zapytania WYMUSZONE PARAMETRYZACJA w procedurze składowanej sp_create_plan_guide. Możesz rozważyć ten rodzaj instrukcji planowania jako sposób włączenia wymuszonej parametryzacji zapytań tylko w określonym typie zapytań, zamiast wszystkich zapytań. Aby uzyskać więcej informacji na temat prostej parametryzacji, zapoznaj się z przewodnikiem po architekturze przetwarzania zapytań .

  • Jeśli opcja bazy danych PARAMETRYZACJI jest ustawiona na WYMUSZONE, można określić, że dla określonej klasy zapytań jest podejmowana tylko prosta parametryzacja, a nie wymuszona parametryzacja. W tym celu należy utworzyć przewodnik planu SZABLONu dotyczący wymuszonej sparametryzowanej formy zapytania i określić wskazówkę zapytania PARAMETRYZACJA SIMPLE w sp_create_plan_guide. Aby uzyskać więcej informacji na temat parametryzacji wymuszonej, zobacz przewodnik po architekturze przetwarzania zapytań .

Rozważ następujące zapytanie w bazie danych AdventureWorks2022:

SELECT pi.ProductID, SUM(pi.Quantity) AS Total  
FROM Production.ProductModel AS pm   
    INNER JOIN Production.ProductInventory AS pi   
        ON pm.ProductModelID = pi.ProductID   
WHERE pi.ProductID = 101   
GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 50;

Jako administrator bazy danych ustaliliśmy, że nie chcesz włączać wymuszonej parametryzacji dla wszystkich zapytań w bazie danych. Jednak należy unikać kosztów kompilacji dla wszystkich zapytań, które są składniowo równoważne poprzedniemu zapytaniu, ale różnią się tylko w ich stałej wartości literału. Innymi słowy, chcesz, aby zapytanie było sparametryzowane, aby plan zapytania dla tego rodzaju zapytania był ponownie używany. W takim przypadku wykonaj następujące czynności:

  1. Pobierz sparametryzowaną formę zapytania. Jedynym bezpiecznym sposobem uzyskania tej wartości do użycia w sp_create_plan_guide jest użycie procedury składowanej systemu sp_get_query_template.

  2. Utwórz przewodnik dla planu w sparametryzowanej formie zapytania, określając wskazówkę zapytania PARAMETRYZACJA WYMUSZONA.

    Ważny

    W ramach parametryzacji zapytania SQL Server przypisuje typ danych parametrom, które zastępują wartości literałów, w zależności od wartości i rozmiaru tych literałów. Ten sam proces dotyczy wartości stałych literałów przekazywanych do parametru wyjściowego @stmtsp_get_query_template. Ponieważ typ danych określony w argumencie @paramssp_create_plan_guide musi być zgodny z typem zapytania, ponieważ jest sparametryzowany przez program SQL Server, może być konieczne utworzenie więcej niż jednego przewodnika po planie, aby pokryć pełny zakres możliwych wartości parametrów dla zapytania.

Poniższy skrypt może służyć do uzyskania sparametryzowanego zapytania, a następnie utworzyć na nim przewodnik planu:

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total   
      FROM Production.ProductModel AS pm   
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID   
      WHERE pi.ProductID = 101   
      GROUP BY pi.ProductID, pi.Quantity   
      HAVING sum(pi.Quantity) > 50',  
    @stmt OUTPUT,   
    @params OUTPUT;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';

Podobnie w bazie danych, w której jest już włączona wymuszona parametryzacja, można upewnić się, że przykładowe zapytanie i inne, które są składniowo równoważne, z wyjątkiem ich wartości literałów stałych, są sparametryzowane zgodnie z regułami prostej parametryzacji. W tym celu określ PARAMETRYZACJA PROSTA zamiast PARAMETRYZACJI WYMUSZONA w klauzuli OPTION.

Notatka

Plany szablonów dopasowują deklaracje do zapytań przesłanych w partiach, które składają się tylko z jednej deklaracji. Instrukcje wewnątrz pakietów wieloinstrukcyjnych nie kwalifikują się do dopasowania przez przewodniki planów TEMPLATE.