Dela via

Planeringsguider

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Viktig

Query Store-ledtrådar tillhandahåller en enklare metod för att forma frågeplaner utan att ändra applikationskoden. Query Store-tips är enklare än planguider. Query Store-tips finns i Azure SQL Database, SQL Database i Microsoft Fabric, Azure SQL Managed Instance och i SQL Server 2022 (16.x) och senare versioner.

Med planguider kan du optimera prestandan för frågor när du inte kan eller inte vill ändra texten i den faktiska frågan direkt i SQL Server. Planguider påverkar optimeringen av frågor genom att koppla frågetips eller en fast frågeplan till dem. Planguider kan vara användbara när en liten delmängd frågor i ett databasprogram som tillhandahålls av en tredjepartsleverantör inte fungerar som förväntat. I planguiden anger du den Transact-SQL-instruktion som du vill optimera och antingen en OPTION-sats som innehåller de frågetips som du vill använda eller en specifik frågeplan som du vill använda för att optimera frågan. När frågeuttrycket körs matchar SQL Server Transact-SQL-instruktionen till planguiden och bifogar OPTION-satsen till frågeuttrycket vid körningen eller använder den angivna frågeplanen. Eftersom SQL Server Query Optimizer vanligtvis väljer den bästa körningsplanen för en fråga rekommenderar vi att du bara använder planguider som en sista utväg för erfarna utvecklare och databasadministratörer.

Det totala antalet planguider som du kan skapa begränsas endast av tillgängliga systemresurser. Planguider bör dock begränsas till verksamhetskritiska frågor som är avsedda för bättre eller stabiliserad prestanda. Planguider bör inte användas för att påverka det mesta av frågebelastningen för ett distribuerat program.

Den resulterande exekveringsplanen som tvingas av den här funktionen kommer att vara densamma eller likna den plan som tvingas. Eftersom den resulterande planen kanske inte är identisk med den plan som anges i planguiden kan planernas prestanda variera. I sällsynta fall kan prestandaskillnaden vara betydande och negativ. I så fall måste administratören ta bort den framtvingade planen.

Planguider kan inte användas i varje utgåva av Microsoft SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i funktioner som stöds av versionerna av SQL Server 2016. Planguider visas i alla utgåvor. Du kan också bifoga en databas som innehåller planguider till valfri utgåva. Planguiderna förblir intakta när du återställer eller kopplar en databas till en uppgraderad version av SQL Server.

Typer av planguider

Följande typer av planguider kan skapas.

objektplansguide

En OBJECT-plan guide matchar frågor som exekveras inom kontexten av Transact-SQL lagrade procedurer, skalära användardefinierade funktioner, användardefinierade funktioner som returnerar tabellvärden i flera steg och DML-triggers.

Anta att följande lagrade procedur, som tar parametern @Country_region, finns i ett databasprogram som distribueras mot AdventureWorks2022-databasen:

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;

Anta att den här lagrade proceduren har kompilerats och optimerats för @Country_region = N'AU' (Australien). Men eftersom det finns relativt få försäljningsorder som kommer från Australien minskar prestandan när frågan körs med hjälp av parametervärden för länder/regioner med fler försäljningsorder. Eftersom de flesta försäljningsorder kommer från USA skulle en frågeplan som genereras för @Country_region = N'US' sannolikt fungera bättre för alla möjliga värden för parametern @Country_region.

Du kan åtgärda det här problemet genom att ändra den lagrade proceduren så att OPTIMIZE FOR-frågehinten läggs till i frågan. Men eftersom den lagrade proceduren finns i ett distribuerat program kan du inte ändra programkoden direkt. I stället kan du skapa följande planguide i databasen 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''))';

När frågan som anges i sp_create_plan_guide-instruktionen körs ändras frågan före optimering för att inkludera OPTIMIZE FOR (@Country = N''US'')-satsen.

SQL-planguide

En SQL-planguide matchar frågor som körs i kontexten för fristående Transact-SQL-instruktioner och batchar som inte ingår i ett databasobjekt. SQL-baserade planguider kan också användas för att matcha frågor som parameteriseras till ett angivet formulär. SQL-planguider gäller fristående Transact-SQL-instruktioner och batchar. Dessa instruktioner skickas ofta av en applikation med hjälp av den systemlagrade proceduren sp_executesql. Tänk till exempel på följande fristående batch:

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

Om du vill förhindra att en parallell exekveringsplan genereras för den här frågan, skapa följande planguide och ställ in frågetipset MAXDOP till 1 i parametern @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)';

Som ett annat exempel bör du överväga följande SQL-instruktion som skickas med hjälp av sp_executesql.

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

Skapa en unik plan för varje körning av denna fråga genom att skapa följande planguide och använd OPTION (RECOMPILE) som en frågehint i parametern @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)';

Viktig

De värden som anges för @module_or_batch- och @params argumenten i sp_create_plan guide-instruktionen måste matcha motsvarande text som skickas i den faktiska frågan. Mer information finns i sp_create_plan_guide (Transact-SQL) och Använda SQL Server Profiler för att skapa och testa planguider.

SQL-planguider kan också skapas på frågor som parameteriseras till samma formulär när parameteriseringsdatabasalternativet är INSTÄLLT på FORCED, eller när en mallplansguide skapas som anger att en parameteriserad frågeklass.

Mallplansguide

En mallplansguide matchar fristående frågor som parameteriseras till ett angivet formulär. Dessa planguider används för att åsidosätta det aktuella alternativet för databasinställningar PARAMETERIZATION för en specifik klass av frågor.

Du kan skapa en mall för en planguide i någon av följande situationer:

  • Parameteriseringsdatabasalternativet är INSTÄLLT på FORCED, men det finns frågor som du vill kompilera enligt reglerna för Simple Parameterization.

  • Alternativet PARAMETERISERINGSDATABAS är inställt på SIMPLE (standardinställningen), men du vill att tvingad parameterisering provas på en grupp av frågeställningar.

Matchningskrav för planguide

Planguider är begränsade till databasen där de skapas. Därför kan endast planguider som finns i databasen som är aktuella när en fråga körs matchas med frågan. Om AdventureWorks2022 till exempel är den aktuella databasen och följande fråga körs:

SELECT FirstName, LastName FROM Person.Person;

Endast planguider i AdventureWorks2022-databasen kan matchas med den här frågan. Men om AdventureWorks2022 är den aktuella databasen och följande instruktioner körs:

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

Endast planguider i DB1 kan matchas med frågan eftersom den körs i kontexten av DB1.

För SQL- eller TEMPLATE-baserade planguider matchar SQL Server värdena för argumenten @module_or_batch och @params för en fråga genom att jämföra de två värdena tecken för tecken. Det innebär att du måste ange texten exakt som SQL Server tar emot den i den faktiska batchen.

När @type = "SQL" och @module_or_batch är inställt på NULL anges värdet för @module_or_batch till värdet för @stmt. Det innebär att värdet för statement_text måste anges i identiskt format, tecken för tecken, eftersom det skickas till SQL Server. Ingen intern konvertering utförs för att underlätta den här matchning.

När både en vanlig planguide (SQL eller OBJECT) och en mallplansguide kan tillämpas på en instruktion används endast den vanliga planguiden.

Not

Batchen som innehåller den instruktion för vilken du vill skapa en planguide får inte innehålla en databasinstruktion USE .

Planguidens effekt på plancachen

När du skapar en planguide för en modul tar du bort frågeplanen för modulen från plancachen. När du skapar en planguide av typen OBJECT eller SQL i en batch, tar du bort frågeplanen för en batch som har samma hash-värde. Att skapa en planguide av typen TEMPLATE tar bort alla batchar som endast innehåller enstaka satser från plancachen i databasen.

Uppgift Ämne
Beskriver hur du skapar en planguide. Skapa en ny planguide
Beskriver hur du skapar en planguide för parametriserade frågor. Skapa en planguide för parametriserade frågor
Beskriver hur du styr beteendet för frågeparameterisering med hjälp av planguider. Ange beteende för frågeparameterisering med hjälp av planguider
Beskriver hur du inkluderar en fast frågeplan i en planguide. Tillämpa en fast frågeplan på en planguide
Beskriver hur du anger frågetips i en planguide. Bifoga frågetips till en planguide
Beskriver hur du visar egenskaper för planguiden. egenskaper för Visa planguide
Beskriver hur du använder SQL Server Profiler för att skapa och testa planguider. Använda SQL Server Profiler för att skapa och testa planguider
Beskriver hur du validerar planguider. Verifiera Planguider Efter Uppgradering

Se även

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)