Compartir vía

sp_create_plan_guide_from_handle (Transact-SQL)

  • Artículo

Se aplica a: SQL Server

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 Plan Guides.

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Argumentos

[ @name = ] N'name'

Nombre de la guía de plan. @name es sysname, sin ningún valor predeterminado. Los nombres de guía de plan se encuentran en el ámbito de la base de datos actual. @name deben cumplir las reglas de los identificadores y no pueden empezar con el signo de número (#). La longitud máxima de @name es de 124 caracteres.

[ @plan_handle = ] plan_handle

Identifica un lote en la memoria caché del plan. @plan_handle es varbinary(64), sin ningún valor predeterminado. @plan_handle se pueden obtener de la vista de administración dinámica de sys.dm_exec_query_stats.

[ @statement_start_offset = ] statement_start_offset

Identifica la posición inicial de la instrucción dentro del lote del @plan_handle especificado. @statement_start_offset es int, con un valor predeterminado de NULL.

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

Cuando NULL se especifica o no se especifica un desplazamiento de instrucciones, se crea una guía de plan para cada instrucción del lote mediante 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 usan la USE PLAN sugerencia de consulta para forzar el uso de un plan específico.

Comentarios

No se puede crear una guía de plan para todos los tipos de instrucciones. Si no se puede crear una guía de plan para una instrucción en el lote, el procedimiento almacenado omite la instrucción y continúa con la siguiente instrucción 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 del plan de la sys.dm_exec_query_stats vista de administración dinámica para ayudar a evitar la posibilidad de este error.

Importante

sp_create_plan_guide_from_handle crea guías de plan basadas en planes a medida que aparecen en la memoria caché del plan. Esto significa que el texto por lotes, las instrucciones Transact-SQL y el plan de presentación XML toman caracteres por carácter (incluidos los valores literales que se pasan a la consulta) de la memoria caché del plan en la guía de plan resultante. Estas cadenas de texto pueden contener información confidencial que, a continuación, se almacena en los metadatos de la base de datos. Los usuarios con permisos adecuados pueden ver esta información mediante la vista de sys.plan_guides catálogo y el cuadro de diálogo Propiedades de la Guía de plan en SQL Server Management Studio. Para asegurarse de que la 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.

Creación de 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 del lote o módulo de destino de la 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

Requiere el permiso VIEW SERVER STATE. Además, se requieren permisos individuales para cada guía de plan que se crea mediante sp_create_plan_guide_from_handle. La creación de una guía de plan de tipo OBJECT requiere ALTER permiso para el objeto al que se hace referencia. La creación de una guía de plan de tipo SQL o TEMPLATE requiere ALTER permiso 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
INNER 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 se crea la guía de plan, examine la objtype columna del conjunto de resultados. Un valor de Proc indica que la guía de plan es de tipo OBJECT. Otros valores como Ad hoc o Prepared indican que la guía de plan es de tipo SQL.

Ejemplos

A Creación de una guía de plan a partir de un plan de consulta en la caché de planes

En el ejemplo siguiente se crea una guía de plan para una sola SELECT instrucción especificando un plan de consulta de la memoria caché del plan. 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_sql_text y sys.dm_exec_text_query_plan. 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 AdventureWorks2022;
GO

SELECT WorkOrderID,
    p.Name,
    OrderQty,
    DueDate
FROM Production.WorkOrder AS w
INNER 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. Creación de varias guías de plan para un lote de varios estados

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 para que el plan de consulta del lote no se quite de la memoria caché del plan después de crear 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. Se devuelve una fila para 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 AdventureWorks2022;
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

Contenido relacionado