Delen via

Planhandleidingen

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Belangrijk

Query Store-hints een eenvoudiger te gebruiken methode bieden voor het vormgeven van queryplannen zonder toepassingscode te wijzigen. Query Store-hints zijn eenvoudiger dan planhandleidingen. Query Store-hints zijn beschikbaar in Azure SQL Database, SQL-database in Microsoft Fabric, Azure SQL Managed Instance en in SQL Server 2022 (16.x) en latere versies.

Met planhandleidingen kunt u de prestaties van query's optimaliseren wanneer u de tekst van de werkelijke query in SQL Server niet of niet rechtstreeks wilt wijzigen. Plangidsen beïnvloeden de optimalisatie van query's door aanwijzingen voor query's of een vaste query-planning eraan toe te voegen. Planhandleidingen kunnen handig zijn wanneer een kleine subset van query's in een databasetoepassing die wordt geleverd door een externe leverancier, niet werkt zoals verwacht. In de plangids specificeert u de Transact-SQL instructie die u wilt optimaliseren en ofwel een OPTION-component met de queryhints die u wilt gebruiken, of een specifiek queryplan dat u wilt gebruiken om de query te optimaliseren. Wanneer de query wordt uitgevoerd, komt de Transact-SQL-instructie overeen met de planhandleiding en koppelt de OPTION-component aan de query tijdens de uitvoering of gebruikt het opgegeven queryplan. Omdat sql Server Query Optimizer doorgaans het beste uitvoeringsplan voor een query selecteert, raden we u aan alleen planhandleidingen te gebruiken als laatste redmiddel voor ervaren ontwikkelaars en databasebeheerders.

Het totale aantal planhandleidingen dat u kunt maken, is alleen beperkt door beschikbare systeembronnen. Planhandleidingen moeten echter worden beperkt tot bedrijfskritieke query's die gericht zijn op verbeterde of gestabiliseerde prestaties. Planhandleidingen mogen niet worden gebruikt om de meeste querybelasting van een geïmplementeerde toepassing te beïnvloeden.

Het resulterende uitvoeringsplan dat door deze functie wordt gedwongen, is hetzelfde of vergelijkbaar met het plan dat wordt gedwongen. Omdat het resulterende plan mogelijk niet identiek is aan het plan dat is opgegeven in de planhandleiding, kunnen de prestaties van de plannen variëren. In zeldzame gevallen kan het prestatieverschil aanzienlijk en negatief zijn; In dat geval moet de beheerder het geforceerde plan verwijderen.

Planhandleidingen kunnen niet worden gebruikt in elke editie van Microsoft SQL Server. Zie Functies die worden ondersteund door de edities van SQL Server 2016voor een lijst met functies die worden ondersteund door de edities van SQL Server 2016. Planhandleidingen zijn zichtbaar in elke editie. U kunt ook een database met planhandleidingen toevoegen aan elke editie. Planhandleidingen blijven intact wanneer u een database herstelt of koppelt aan een bijgewerkte versie van SQL Server.

Typen planhandleidingen

De volgende typen planhandleidingen kunnen worden gemaakt.

Handleiding voor OBJECTplan

Een handleiding voor objectplannen komt overeen met query's die worden uitgevoerd in de context van Transact-SQL opgeslagen procedures, scalaire door de gebruiker gedefinieerde functies, tabelgedefinieerde functies met meerdere instructies en DML-triggers.

Stel dat de volgende opgeslagen procedure, die de parameter @Country_region gebruikt, zich in een databasetoepassing bevindt die is geïmplementeerd op basis van de AdventureWorks2022-database:

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;

Stel dat deze opgeslagen procedure is gecompileerd en geoptimaliseerd voor @Country_region = N'AU' (Australië). Omdat er echter relatief weinig verkooporders zijn die afkomstig zijn uit Australië, nemen de prestaties af wanneer de query wordt uitgevoerd met behulp van parameterwaarden van landen/regio's met meer verkooporders. Omdat de meeste verkooporders afkomstig zijn uit de Verenigde Staten, zou een queryplan dat wordt gegenereerd voor @Country_region = N'US' waarschijnlijk beter presteren voor alle mogelijke waarden van de parameter @Country_region.

U kunt dit probleem oplossen door de opgeslagen procedure te wijzigen om de OPTIMIZE FOR queryhint toe te voegen aan de query. Omdat de opgeslagen procedure zich echter in een geïmplementeerde toepassing bevindt, kunt u de toepassingscode niet rechtstreeks wijzigen. In plaats daarvan kunt u de volgende planhandleiding maken in de AdventureWorks2022-database.

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

Wanneer de query die is opgegeven in de instructie sp_create_plan_guide wordt uitgevoerd, wordt de query gewijzigd voordat de optimalisatie de OPTIMIZE FOR (@Country = N''US'') component opneemt.

Handleiding voor SQL-plan

Een SQL-planhandleiding komt overeen met query's die worden uitgevoerd in de context van zelfstandige Transact-SQL-instructies en batches die geen deel uitmaken van een databaseobject. Op SQL gebaseerde planhandleidingen kunnen ook worden gebruikt om query's te vinden die worden geparameteriseerd naar een opgegeven formulier. SQL-planhandleidingen zijn van toepassing op zelfstandige Transact-SQL instructies en batches. Deze instructies worden vaak door een toepassing ingediend met behulp van de in het systeem opgeslagen procedure sp_executesql. Denk bijvoorbeeld aan de volgende zelfstandige batch:

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

Als u wilt voorkomen dat een parallel uitvoeringsplan voor deze query wordt gegenereerd, maakt u de volgende planhandleiding en stelt u de MAXDOP queryhint in op 1 in de parameter @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)';

Denk bijvoorbeeld aan de volgende SQL-instructie die is ingediend met behulp van sp_executesql.

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

Als u een uniek plan wilt maken voor elke uitvoering van deze query, maakt u de volgende planhandleiding en gebruikt u de OPTION (RECOMPILE) queryhint in de parameter @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)';

Belangrijk

De waarden die worden opgegeven voor de argumenten @module_or_batch en @params van de instructie sp_create_plan guide moeten overeenkomen met de bijbehorende tekst die in de werkelijke query is ingediend. Zie sp_create_plan_guide (Transact-SQL) en Gebruik SQL Server Profiler om Plan Guides te maken en te testen.

SQL plan-gidsen kunnen ook worden gemaakt op query's die parameteriseren naar hetzelfde formulier wanneer de PARAMETERIZATION databaseoptie is ingesteld op GEFORCEERD, of wanneer een sjabloonplan-gids wordt gemaakt die een geparameteriseerde klasse van query's specificeert.

Handleiding sjabloonplan

Een sjabloonplangids komt overeen met zelfstandige zoekopdrachten die geparametreerd zijn naar een opgegeven formulier. Deze plangidsen worden gebruikt om de huidige PARAMETERIZATION-database SET-optie van een database voor een klasse van query's te overschrijven.

U kunt een sjabloonplanhandleiding maken in een van de volgende situaties:

  • De PARAMETERIZATION-databaseoptie is INGESTELD op GEFORCEERDE, maar er zijn query's die u wilt compileren volgens de regels van Eenvoudige parameterisatie.

  • De databaseoptie voor PARAMETERIZATION is ingesteld op SIMPLE (de standaardinstelling), maar u wilt geforceerde parameterisatie proberen op een reeks van query's.

Overeenkomende vereisten voor planhandleiding

Planhandleidingen zijn gericht op de database waarin ze worden gemaakt. Daarom kunnen alleen planhandleidingen die zich in de database bevinden die actueel zijn wanneer een query wordt uitgevoerd, worden vergeleken met de query. Als AdventureWorks2022 bijvoorbeeld de huidige database is en de volgende query wordt uitgevoerd:

SELECT FirstName, LastName FROM Person.Person;

Alleen planhandleidingen in de AdventureWorks2022-database komen in aanmerking voor overeenkomst met deze query. Als AdventureWorks2022 echter de huidige database is en de volgende instructies worden uitgevoerd:

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

Alleen plangidsen in DB1 komen in aanmerking om te worden gekoppeld aan de query omdat de query wordt uitgevoerd binnen de context van DB1.

Voor SQL- of sjabloonbasishandleidingen vergelijkt SQL Server de waarden voor de @module_or_batch en @params argumenten met een query door de waarden karakter voor karakter te vergelijken. Dit betekent dat u de tekst precies moet opgeven zoals SQL Server deze in de werkelijke batch ontvangt.

Wanneer @type = 'SQL' en @module_or_batch is ingesteld op NULL, wordt de waarde van @module_or_batch ingesteld op de waarde van @stmt. Dit betekent dat de waarde voor statement_text moet worden opgegeven in dezelfde notatie, teken-voor-teken, zoals deze wordt verzonden naar SQL Server. Er wordt geen interne conversie uitgevoerd om deze aansluiting te vergemakkelijken.

Wanneer zowel een standaard planhandleiding (SQL of OBJECT) als een SJABLOON planhandleiding op een instructie van toepassing is, wordt alleen de standaard planhandleiding gebruikt.

Notitie

De batch die de instructie bevat waarvoor u een planhandleiding wilt maken, mag geen USE database instructie bevatten.

Effect van de plangids op de plancache

Door een planhandleiding voor een module te maken, verwijdert u het queryplan voor die module uit de plancache. Wanneer u een planhandleiding van het type OBJECT of SQL in een batch aanmaakt, wordt het queryplan verwijderd voor een batch met dezelfde hash-waarde. Als u een planhandleiding van het type SJABLOON maakt, worden alle batches met één instructie uit de plancache in die database verwijderd.

Taak Onderwerp
Hierin wordt beschreven hoe u een planhandleiding maakt. een nieuwe planhandleiding maken
Beschrijft hoe u een planhandleiding maakt voor geparameteriseerde query's. een planhandleiding maken voor geparameteriseerde query's
Hierin wordt beschreven hoe u het gedrag van queryparameterisatie kunt beheren met behulp van planhandleidingen. Specificeer query-parameterisatiegedrag met behulp van plan-gidsen
Hierin wordt beschreven hoe u een vast queryplan opneemt in een planhandleiding. Een vast queryplan toepassen op een plangids
Hierin wordt beschreven hoe u queryhints opgeeft in een planhandleiding. queryhints toevoegen aan een planhandleiding
Hierin wordt beschreven hoe u eigenschappen van planhandleidingen kunt weergeven. Eigenschappen van planhandleiding weergeven
Hierin wordt beschreven hoe u SQL Server Profiler gebruikt om handleidingen voor plannen te maken en te testen. SQL Server Profiler gebruiken om planhandleidingen te maken en te testen
Hierin wordt beschreven hoe u planhandleidingen valideert. Planhandleidingen valideren na de upgrade

Zie ook

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)