Delen via

Het gedrag van query-parameterisatie specificeren met behulp van plangidsen

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Wanneer de optie PARAMETERIZATION-database is ingesteld op SIMPLE, kan de SQL Server-queryoptimalisatie ervoor kiezen om de query's te parameteriseren. Dit betekent dat letterlijke waarden die zijn opgenomen in een query, worden vervangen door parameters. Dit proces wordt eenvoudige parameterisatie genoemd. Wanneer SIMPLE-parameterisatie van kracht is, kunt u niet bepalen welke query's worden geparameteriseerd en welke query's dat niet zijn. U kunt echter opgeven dat alle query's in een database worden geparameteriseerd door de optie PARAMETERISATIE-database in te stellen op GEFORCEERD. Dit proces wordt geforceerde parameterisatie genoemd.

U kunt het gedrag van parameters van een database op de volgende manieren overschrijven met behulp van planhandleidingen:

  • Wanneer de optie PARAMETERISATIE-database is ingesteld op SIMPLE, kunt u opgeven dat geforceerde parameterisatie wordt uitgevoerd op een bepaalde klasse query's. U doet dit door een sjabloon plangids te maken voor de geparameteriseerde vorm van de query, en de PARAMETERIZATION FORCED query-hint op te geven in de stored procedure sp_create_plan_guide. U kunt dit soort planningsgids beschouwen als een manier om geforceerde parameters alleen in te schakelen voor een bepaalde klasse query's, in plaats van alle query's. Zie de Architectuurhandleiding voor queryverwerkingvoor meer informatie over eenvoudige parameters.

  • Wanneer de optie PARAMETERIZATION-database is ingesteld op GEFORCEERD, kunt u opgeven dat voor een bepaalde klasse query's alleen eenvoudige parameterisatie wordt geprobeerd, niet geforceerde parameterisatie. U doet dit door een sjabloonplangids te maken voor de geforceerde geparameteriseerde vorm van de query en de query-hint voor eenvoudige parameterisatie in sp_create_plan_guideop te geven. Zie de Architectuurhandleiding voor queryverwerkingvoor meer informatie over geforceerde parameterisatie.

Houd rekening met de volgende query op de AdventureWorks2022-database:

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;

Als databasebeheerder hebt u vastgesteld dat u geen geforceerde parameterisatie wilt inschakelen voor alle query's in de database. U wilt echter wel compilatiekosten voorkomen voor alle query's die syntactisch gelijk zijn aan de vorige query, maar alleen verschillen in hun constante letterlijke waarden. Met andere woorden, u wilt dat de query wordt geparameteriseerd, zodat een queryplan voor dit type query opnieuw wordt gebruikt. Voer in dit geval de volgende stappen uit:

  1. Haal de geparameteriseerde vorm van de query op. De enige veilige manier om deze waarde te verkrijgen voor gebruik in sp_create_plan_guide is met behulp van de sp_get_query_template systeem opgeslagen procedure.

  2. Maak de planhandleiding voor de geparameteriseerde vorm van de query, waarbij u de hint PARAMETERIZATION FORCED-query opgeeft.

    Belangrijk

    Als onderdeel van het parameteriseren van een query wijst SQL Server een gegevenstype toe aan de parameters die de letterlijke waarden vervangen, afhankelijk van de waarde en grootte van de letterlijke waarde. Hetzelfde proces treedt op voor de constante waarden die aan de uitvoerparameter @stmt van sp_get_query_templatedoorgegeven worden. Omdat het gegevenstype dat is opgegeven in het argument @params van sp_create_plan_guide moet overeenkomen met dat van de query omdat deze wordt geparameteriseerd door SQL Server, moet u mogelijk meer dan één planhandleiding maken om het volledige bereik van mogelijke parameterwaarden voor de query te behandelen.

Het volgende script kan worden gebruikt om de geparameteriseerde query te verkrijgen en er vervolgens een planhandleiding voor te maken:

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)';

Op dezelfde manier kunt u ervoor zorgen dat in een database waarin geforceerde parameterisatie al is ingeschakeld, de voorbeeldquery en andere query's die syntactisch gelijkwaardig zijn, met uitzondering van hun constante letterlijke waarden, worden geparameteriseerd volgens de regels van eenvoudige parameterisatie. Hiervoor geeft u PARAMETERISATIE EENVOUDIG op in plaats van PARAMETERISATIE GEFORCEERD in de OPTION-clausule.

Notitie

Een sjabloonplan helpt bij het matchen van statements met query's die in batches worden ingediend, welke uitsluitend uit één enkele instructie bestaan. Instructies in batches met meerdere staten komen niet in aanmerking om te worden vergeleken met sjabloonplanhandleidingen.