sp_create_plan_guide_from_handle (Transact-SQL)

Artículo
06/18/2013

Crea una o varias guías de plan a partir de un plan de consulta en la memoria caché del plan. Puede utilizar este procedimiento almacenado para asegurarse de que el optimizador de consultas siempre utiliza un plan de consulta concreto para la consulta especificada. Para obtener más información acerca de las guías de plan, vea Guías de plan.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

[ @name = ] N'plan_guide_name'
Es el nombre de la guía de plan. Los nombres de guía de plan se encuentran en el ámbito de la base de datos actual. plan_guide_name debe cumplir las reglas de los identificadores y no puede empezar por un signo de almohadilla (#). La longitud máxima de plan_guide_name es de 124 caracteres.
[ @plan\_handle = ] plan_handle
Identifica un lote en la memoria caché del plan. plan_handle es de tipo varbinary(64). plan_handle puede obtenerse a partir de la vista de administración dinámica sys.dm_exec_query_stats.
[ @statement\_start\_offset = ] { statement_start_offset | NULL } ]
Identifica la posición inicial de la instrucción dentro del lote del plan_handle especificado. statement_start_offset es de tipo int y su valor predeterminado es NULL.

El desplazamiento de la instrucción corresponde a la columna statement_start_offset en la vista de administración dinámica sys.dm_exec_query_stats.

Si se especifica NULL o no se especifica un desplazamiento de instrucción, se crea una guía de plan para cada instrucción del lote utilizando el plan de consulta para el identificador de plan especificado. Las guías de plan resultantes son equivalentes a las guías de plan que utilizan la sugerencia de consulta USE PLAN para forzar el uso de un plan concreto.

Comentarios

No se puede crear una guía de plan para todos los tipos de instrucción. Si no puede crearse una guía de plan para una instrucción del lote, el procedimiento almacenado omite la instrucción y continúa en la instrucción siguiente del lote. Si una instrucción aparece varias veces en el mismo lote, se habilita el plan para la última aparición y se deshabilitan los planes anteriores para la instrucción. Si no se puede utilizar ninguna instrucción del lote en una guía de plan, se producirá el error 10532 y la instrucción producirá un error. Se recomienda obtener siempre el identificador de plan a partir de la vista de administración dinámica sys.dm_exec_query_stats para evitar en lo posible este error.

Nota de seguridad
sp_create_plan_guide_from_handle crea guías de plan basadas en planes como aparecen en la memoria caché del plan. Esto significa que el texto por lotes, las instrucciones de Transact-SQL y el plan de presentación XML se toman carácter a carácter (incluidos los valores literales pasados a la consulta) desde la caché del plan hasta la guía de plan resultante. Estas cadenas de texto pueden contener información confidencial que se almacena en los metadatos de la base de datos. Los usuarios con permisos adecuados pueden ver esta información mediante la vista de catálogo sys.plan_guides y el cuadro de diálogo Propiedades de la guía de plan en SQL Server Management Studio. Para asegurarse de que dicha información confidencial no se divulga a través de una guía de plan, se recomienda revisar las guías de plan creadas a partir de la memoria caché del plan.

sp_create_plan_guide_from_handle crea guías de plan basadas en planes como aparecen en la memoria caché del plan. Esto significa que el texto por lotes, las instrucciones de Transact-SQL y el plan de presentación XML se toman carácter a carácter (incluidos los valores literales pasados a la consulta) desde la caché del plan hasta la guía de plan resultante. Estas cadenas de texto pueden contener información confidencial que se almacena en los metadatos de la base de datos. Los usuarios con permisos adecuados pueden ver esta información mediante la vista de catálogo sys.plan_guides y el cuadro de diálogo Propiedades de la guía de plan en SQL Server Management Studio. Para asegurarse de que dicha información confidencial no se divulga a través de una guía de plan, se recomienda revisar las guías de plan creadas a partir de la memoria caché del plan.

Crear guías de plan para varias instrucciones dentro de un plan de consulta

Al igual que sp_create_plan_guide, sp_create_plan_guide_from_handle quita el plan de consulta para el módulo o lote concreto de la memoria caché del plan. Esto se hace para asegurarse de que todos los usuarios empiezan a utilizar la nueva guía de plan. Al crear una guía de plan para varias instrucciones dentro de un único plan de consulta, puede posponer la eliminación del plan de caché mediante la creación de todas las guías de plan en una transacción explícita. Este método permite al plan permanecer en la memoria caché hasta que se completa la transacción y crear una guía de plan para cada instrucción especificada. Vea el ejemplo B.

Permisos

Necesita el permiso VIEW SERVER STATE. Además, se necesitan permisos individuales para cada guía de plan creada mediante sp_create_plan_guide_from_handle. Para crear una guía de plan de tipo OBJECT, se requiere el permiso ALTER en el objeto al que se hace referencia. Para crear una guía de plan de tipo SQL o TEMPLATE, se requiere el permiso ALTER en la base de datos actual. Para determinar el tipo de guía de plan que se va a crear, ejecute la consulta siguiente:

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;

En la fila que contiene la instrucción para la que está creando la guía de plan, examine la columna objtype en el conjunto de resultados. Un valor de Proc indica que la guía de plan es de tipo OBJECT. Otros valores como AdHoc o Prepared indican que la guía de plan es de tipo SQL.

Ejemplos

A.Crear una guía de plan a partir de un plan de consulta en la caché del plan

En el ejemplo siguiente se especifica un plan de consulta desde la caché del plan para crear una guía de plan para una única instrucción SELECT. El ejemplo comienza ejecutando una sencilla instrucción SELECT para la que se creará la guía de plan. El plan para esta consulta se examina mediante las vistas de administración dinámica sys.dm_exec_text_query_plan y sys.dm_exec_sql_text. A continuación, se crea la guía de plan para la consulta después de especificar el plan de consulta en la caché del plan asociada a la consulta. La última instrucción del ejemplo comprueba que la guía de plan existe.

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.Crear varias guías de plan para un lote de varias instrucciones

El ejemplo siguiente crea una guía de plan para dos instrucciones dentro de un lote de varias instrucciones. Las guías de plan se crean dentro de una transacción explícita de modo que el plan de consulta para el lote no se quite de la caché del plan una vez creada la primera guía de plan. El ejemplo comienza ejecutando un lote de varias instrucciones. El plan para el lote se examina mediante las vistas de administración dinámica. Observe que se devuelve una fila por cada instrucción del lote. A continuación, se crea una guía de plan para la primera y tercera instrucciones del lote mediante el parámetro @statement\_start\_offset. La última instrucción del ejemplo comprueba que las guías de plan existen.

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