.png)
.png)
|
Dieser Artikel wurde manuell übersetzt. Bewegen Sie den Mauszeiger über die Sätze im Artikel, um den Originaltext anzuzeigen.
|
Übersetzung
Original
|
sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
, [ @module_or_batch = ]
{
N'[ schema_name. ] object_name'
| N'batch_text'
| NULL
}
, [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
, [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
| N'XML_showplan'
| NULL }
Hinweis
|
|---|
|
|
Voraussetzungen für den Planhinweislistenabgleich
-
Leerzeichen (Tabstopps, Leerzeichen, Wagenrücklauf oder Zeilenvorschub) innerhalb der Zeichenfolge. -
Kommentare (-- oder /* */). -
Nachfolgende Semikolons
N'SELECT *
FROM T
WHERE a=10'
N'SELECT * FROM T WHERE b = 10'
Auswirkungen von Planhinweislisten auf den Plancache
A.Erstellen einer Planhinweisliste vom Typ OBJECT für eine Abfrage in einer gespeicherten Prozedur
IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry
(@Country_region nvarchar(60))
AS
BEGIN
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country_region;
END
GO
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
B.Erstellen einer Planhinweisliste vom Typ SQL für eine eigenständige Abfrage
SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;
EXEC sp_create_plan_guide
@name = N'Guide1',
@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)';
C.Erstellen einer Planhinweisliste vom Typ TEMPLATE für die parametrisierte Form einer Abfrage
SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
EXEC sp_create_plan_guide
@name = N'TemplateGuide1',
@stmt = N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = @0',
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = N'@0 int',
@hints = N'OPTION(PARAMETERIZATION FORCED)';
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;',
@stmt OUTPUT,
@params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';
| Wichtig |
|---|
D.Erstellen einer Planhinweisliste für eine Abfrage, die über eine API-Cursoranforderung übermittelt wird
DECLARE @p1 int; SET @p1=-1; DECLARE @p2 int; SET @p2=0; DECLARE @p5 int; SET @p5=4104; DECLARE @p6 int; SET @p6=8193; DECLARE @p7 int; SET @p7=0; EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101' SELECT @p1, @p2, @p5, @p6, @p7;
EXEC sp_create_plan_guide
@name = N'APICursorGuide',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate BETWEEN @P1 AND @P2',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 varchar(255),@P2 varchar(255)',
@hints = N'OPTION(HASH JOIN)';
E.Erstellen einer Planhinweisliste durch Abrufen des XML-Showplans aus einem zwischengespeicherten Plan
USE AdventureWorks2012;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');
EXEC sp_create_plan_guide
@name = N'Guide1_from_XML_showplan',
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints =@xml_showplan;
GO
