sp_create_plan_guide_from_handle (Transact-sql)

Makale
10/29/2012

Bir sorgu planı plan önbelleğinde bir veya daha fazla planı kılavuzları oluşturur. Bu saklı yordam, query optimizer, her zaman belirli sorgu planı için belirtilen bir sorgu kullanır emin olmak için kullanabilirsiniz. Plan kılavuzları hakkında daha fazla bilgi için bkz: Plan kılavuzları.

Konu bağlantısı simgesi Transact-SQL Sözdizim Kuralları

Sözdizimi

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'
    , [ @plan_handle = ] plan_handle
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]

Bağımsız değişkenler

@name =n'plan_guide_name'
Plan Kılavuzu adını oluşturur. Plan Kılavuzu adları geçerli veritabanına kapsamına eklenir. plan_guide_namekurallarına uymak zorundadır tanımlayıcıları ve sayı işareti (#) ile başlayamaz. En uzun plan_guide_name124 karakterden.
@ plan_handle =plan_handle
Bir toplu iş planı önbellekte tanımlar. plan_handleis varbinary(64). plan_handleelde edilebilir--dan sys.dm_exec_query_stats yönetimi görüntüleyin.
@ statement_start_offset = {statement_start_offset | null}]
Deyimi içinde belirtilen toplu başlangıç konumunu tanımlayan plan_handle. statement_start_offsetise int, null varsayılan.

Deyimi uzaklık karşılık statement_start_offsetsütununda sys.dm_exec_query_stats yönetimi görüntüleyin.

Bir deyimi Uzaklık belirtilmezse veya null belirtilirse, bir plan Kılavuzu için her sorgu planı için belirtilen planı tutamacını kullanarak toplu deyiminde oluşturulur. Elde edilen planı kılavuzları, belirli bir plan kullanımını zorlamak için use plan sorgu ipucu kullanma kılavuzları plan için eşdeğerdir.

Açıklamalar

Bir plan Kılavuzu tüm ifade türleri için oluşturulamıyor. Bir plan Kılavuzu için toplu deyiminde oluşturulamazsa, saklı yordam deyimi yoksayar ve sonraki deyimi içinde toplu işlem devam ediyor. Bir deyimi aynı kümede birden çok kez oluşursa, plan son oluşumu için etkindir ve önceki planları deyimi için devre dışı bırakılır. Hiçbir deyimi toplu bir plan Kılavuzu kullanılabilir hata 10532 oluşturulur ve deyimi başarısız olur. Her zaman planı tanıtıcıdan elde öneririz sys.dm_exec_query_statsBu hata olasılığını önlemek için dinamik yönetimi görünümü.

Güvenlik Notu
sp_create_plan_guide_from_handleplan kılavuzları plan önbelleğinde göründükleri gibi planları temel oluşturur. Bu demektir ki toplu metni, Transact-SQLbildirimleri ve xml Showplan karakter karakter (sorgu için geçirilen herhangi bir hazır bilgi değerleri dahil) alınır içine elde edilen plan Kılavuzu planı önbellekten. Bu metin dizeleri, ardından veritabanı meta verileri depolanan önemli bilgileri içerebilir. Uygun izinlere sahip kullanıcılar bu bilgileri kullanarak görüntüleyebileceği sys.plan_guideskatalog görünüm ve Plan Kılavuzu özellikleri iletişim kutusunda SQL Server Management Studio. Hassas bilgileri bir plan Kılavuzu başkalarına değildir sağlamak için oluşturulan planı önbellekten planı kılavuzları gözden geçirmenizi öneririz.

sp_create_plan_guide_from_handleplan kılavuzları plan önbelleğinde göründükleri gibi planları temel oluşturur. Bu demektir ki toplu metni, Transact-SQLbildirimleri ve xml Showplan karakter karakter (sorgu için geçirilen herhangi bir hazır bilgi değerleri dahil) alınır içine elde edilen plan Kılavuzu planı önbellekten. Bu metin dizeleri, ardından veritabanı meta verileri depolanan önemli bilgileri içerebilir. Uygun izinlere sahip kullanıcılar bu bilgileri kullanarak görüntüleyebileceği sys.plan_guideskatalog görünüm ve Plan Kılavuzu özellikleri iletişim kutusunda SQL Server Management Studio. Hassas bilgileri bir plan Kılavuzu başkalarına değildir sağlamak için oluşturulan planı önbellekten planı kılavuzları gözden geçirmenizi öneririz.

Plan kılavuzları için bir sorgu planı içinde birden çok deyimleri oluşturma

Gibi sp_create_plan_guide, sp_create_plan_guide_from_handlesorgu planı hedeflenen toplu ya da modül planı önbellekten kaldırır. Bu, tüm kullanıcıların yeni plan Kılavuzu kullanarak başlamak sağlamak için yapılır. Birden çok deyimleri içinde bir tek sorgu planı için bir plan Kılavuzu oluştururken, tüm planı kılavuzları açık işlem içinde oluşturarak kaldırılması planı önbellekten erteleyebilirsiniz. Bu yöntem, planın hareket tamamlandıktan ve belirtilen her cümlesi için bir plan Kılavuzu oluşturulan kadar önbellekte kalmasını sağlar. Örnek b. bkz.

İzinler

VIEW server state izni gerektirir. Ayrıca, tek tek izinler kullanılarak oluşturulan her plan Kılavuzu için gerekli sp_create_plan_guide_from_handle. Bir plan Kılavuzu türü oluşturmak için nesne başvurulan nesne üzerinde alter izni gerektirir. Bir plan Kılavuzu türü oluşturmak için sql veya template geçerli veritabanını alter izni gerektirir. Oluşturulacak plan Kılavuzu türü belirlemek için aşağıdaki sorguyu çalıştırın:

SELECT cp.plan_handle, sql_handle, st.text, objtype 
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;

SELECT cp.plan_handle, sql_handle, st.text, objtype 
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;

İncelemek için plan Kılavuzu oluşturduğunuz deyimi içeren satırda, objtypesonuç kümesinde sütun. Değeri Procplan Kılavuzu türü nesne olduğunu gösterir. Gibi diğer değerler AdHocya Preparedplan Kılavuzu türü sql gösterir.

Örnekler

A.Bir sorgu planı plan önbelleğinde bir plan Kılavuzu oluşturma

Aşağıdaki örnek, bir sorgu planı plan önbelleğinden belirterek tek bir select deyimi için bir plan Kılavuzu oluşturur. Örnek basit yürüterek başlıyor SELECTdeyimi plan Kılavuzu oluşturulacaktır. Bu sorgu planını kullanarak incelenir sys.dm_exec_sql_textve sys.dm_exec_text_query_plandinamik yönetimi görünümleri. Plan Kılavuzu, planı önbellek sorgu ile ilişkili olan sorgu planı belirterek sorgunun ardından oluşturulur. Örneğin son ifade, plan Kılavuzu bulunduğunu doğrular.

USE AdventureWorks2012;
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM Production.WorkOrder AS w 
JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO
-- Inspect the query plan by using dynamic management views.
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide1',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO
-- Verify that the plan guide is created.
SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

B.Birden çok deyimli bir toplu iş planı kılavuzları oluşturma

Aşağıdaki örnek, iki deyimleri içinde çoklu deyimli bir toplu iş için bir plan Kılavuzu oluşturur. Böylece toplu iş sorgu planı planı önbellekten kaldırılmaz, bu ilk plan Kılavuzu oluşturulduktan sonra planı kılavuzları açık işlem içinden oluşturulur. Örneğin, çoklu deyimli toplu yürüterek başlar. Dinamik yönetimi görünümleri kullanarak toplu iş planı incelenir. Fark toplu iş her tablo için bir satır döndürdü. Bir plan Kılavuzu sonra birinci ve üçüncü deyimleri toplu belirterek oluşturulur @statement\_start\_offsetparametresi. Örneğin son ifade, planı kılavuzları bulunduğunu doğrular.

USE AdventureWorks2012;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO