sp_create_plan_guide (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Crea una guía de plan para asociar sugerencias de consulta o planes de consulta actuales a las consultas de una base de datos. 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 [ @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 }  

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 con el signo de número (#). La longitud máxima de plan_guide_name es de 124 caracteres.

[ @stmt = ] N'statement_text'
Es una instrucción transact-SQL con la que se va a crear una guía de plan. Cuando el optimizador de consultas de SQL Server reconoce una consulta que coincide con statement_text, plan_guide_name surte efecto. Para que la creación de una guía de plan se realice correctamente, statement_text debe aparecer en el contexto especificado por los parámetros @type, @module_or_batch y @params.

statement_text debe proporcionarse de una manera que permita que el optimizador de consultas coincida con la instrucción correspondiente proporcionada en el lote o módulo identificado por @module_or_batch y @params. Para obtener más información, vea la sección "Comentarios". El tamaño de statement_text solo está limitado por la memoria disponible del servidor.

[@type = ] N'{ OBJECT | SQL | TEMPLATE }'
Es el tipo de entidad en la que aparece statement_text . Especifica el contexto para la coincidencia de statement_text para plan_guide_name.

OBJECT
Indica statement_text aparece en el contexto de un procedimiento almacenado de Transact-SQL, una función escalar, una función con valores de tabla de varios estados o un desencadenador DML de Transact-SQL en la base de datos actual.

SQL
Indica statement_text aparece en el contexto de una instrucción independiente o lote que se puede enviar a SQL Server a través de cualquier mecanismo. Las instrucciones Transact-SQL enviadas por objetos de Common Language Runtime (CLR) o procedimientos almacenados extendidos, o mediante EXEC N'sql_string', se procesan como lotes en el servidor y, por lo tanto, deben identificarse como @type = "SQL". Si se especifica SQL, la sugerencia de consulta PARAMETERIZATION { FORCED | SIMPLE } no se podrá especificar en el parámetro @hints.

TEMPLATE
Indica que la guía de plan se aplica a cualquier consulta que parametrice al formulario indicado en statement_text. Si se especifica TEMPLATE, solo se puede especificar la sugerencia de consulta PARAMETERIZATION { FORCED | SIMPLE } en el parámetro @hints. Para obtener más información sobre las guías de plan TEMPLATE, vea Especificar el comportamiento de parametrización de consulta mediante guías de plan.

[@module_or_batch =] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }
Especifica el nombre del objeto en el que statement_text aparece o el texto por lotes en el que aparece statement_text . El texto por lotes no puede incluir una instrucción USEdatabase .

Para que una guía de plan coincida con un lote enviado desde una aplicación, batch_text debe proporcionarse en el mismo formato, carácter para carácter, que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna. Para obtener más información, vea la sección Comentarios.

[schema_name.] object_name especifica el nombre de un procedimiento almacenado de Transact-SQL, una función escalar, una función con valores de tabla de varios estados o un desencadenador DML de Transact-SQL que contiene statement_text. Si no se especifica schema_name , schema_name usa el esquema del usuario actual. Si se especifica NULL y @type = "SQL", el valor de @module_or_batch se establece en el valor de @stmt. Si @type = 'TEMPLATE**'**, @module_or_batch debe ser NULL.

[ @params = ] { N'@parameter_name data_type [ ,... n ]' | NULL }
Especifica las definiciones de todos los parámetros incrustados en statement_text. @params solo se aplica cuando se da una de las dos condiciones siguientes:

• @type = 'SQL' o 'TEMPLATE'. Si es 'TEMPLATE', @params no debe ser NULL.

• statement_text se envía mediante sp_executesql y se especifica un valor para el parámetro @params o SQL Server envía internamente una instrucción después de parametrizarla. El envío de consultas con parámetros de las API de base de datos (incluidas ODBC, OLE DB y ADO.NET) parece SQL Server como llamadas a sp_executesql o a rutinas de cursor del servidor de API; por lo tanto, también pueden coincidir con las guías del plan SQL o TEMPLATE.

@parameter_name data_type debe proporcionarse exactamente en el mismo formato que se envía a SQL Server mediante sp_executesql o enviado internamente después de la parametrización. Para obtener más información, vea la sección Comentarios. Si el lote no contiene parámetros, debe especificarse NULL. El tamaño de @params solo está limitado por la memoria disponible en el servidor.

[@hints = ] { N'OPTION (query_hint [ ,... n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ ,... n ] )
Especifica una cláusula OPTION que se va a adjuntar a una consulta que coincida con @stmt. @hints debe ser sintácticamente igual que una cláusula OPTION en una instrucción SELECT y puede contener cualquier secuencia válida de sugerencias de consulta.

N'XML_showplan'
Es el plan de consulta en formato XML que se va a aplicar como una sugerencia.

Se recomienda asignar el plan de presentación XML a una variable; de lo contrario, hay que anteponer una comilla sencilla a de cada comilla sencilla presente en el plan de presentación. Vea el ejemplo E.

NULL
Indica que no se aplica a la consulta ninguna sugerencia existente especificada en la cláusula OPTION de la consulta. Para obtener más información, vea Cláusula OPTION (Transact-SQL).

Comentarios

Los argumentos de sp_create_plan_guide deben indicarse en el orden que se muestra. Cuando se incluyen valores para los parámetros de sp_create_plan_guide, deben especificarse todos los nombres de parámetro de forma explícita, o bien no especificarse ninguno. Por ejemplo, si se especifica @name = , también deben especificarse @stmt = , @type =, etc. Del mismo modo, si se omite @name = y solo se indica el valor del parámetro, también deben omitirse los demás nombres de parámetro y solo se indicará su valor. Los nombres de argumento solo se incluyen con fines de descripción, para ayudar a entender la sintaxis. SQL Server no comprueba que el nombre del parámetro especificado coincida con el nombre del parámetro en la posición donde se usa el nombre.

Puede crearse más de una guía de plan OBJECT o SQL para la misma consulta y lote o módulo. Sin embargo, en un momento dado, solo puede estar habilitada una guía de plan.

No se pueden crear guías de plan de tipo OBJECT para un valor @module_or_batch que hace referencia a un procedimiento almacenado, una función o un desencadenador DML que especifica la cláusula WITH ENCRYPTION o que es temporal.

Se producirá un error si se intenta quitar o modificar una función, procedimiento almacenado o desencadenador DML al que una guía de plan, habilitada o deshabilitada, haga referencia. También se producirá un error si se intenta quitar una tabla que tenga definido un desencadenador al que haga referencia una guía de plan.

Requisitos de coincidencia de la Guía de plan

Para las guías de plan que especifican @type = "SQL" o @type = "TEMPLATE" para que coincidan correctamente con una consulta, los valores de batch_text y @parameter_name data_type [,... n ] debe proporcionarse exactamente con el mismo formato que sus homólogos enviados por la solicitud. Esto significa que debe proporcionar el texto por lotes exactamente como el compilador de SQL Server lo recibe. Para capturar el texto real por lotes y parámetros, puede usar SQL Server Profiler. Para obtener más información, vea Usar SQL Server Profiler para crear y probar guías de plan.

Cuando @type = "SQL" y @module_or_batch se establece en NULL, el valor de @module_or_batch se establece en el valor de @stmt. Esto significa que el valor de statement_text debe proporcionarse exactamente en el mismo formato, carácter para carácter, que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.

Cuando SQL Server coincide con el valor de statement_text para batch_text y @parameter_name data_type [,... n ], o si @type = **'**OBJECT', al texto de la consulta correspondiente dentro de object_name, no se tienen en cuenta los siguientes elementos de cadena:

• Los caracteres de espacio en blanco (tabulaciones, espacios, retornos de carro o avances de línea) dentro de una cadena.

• Comentarios (-- o /* */).

• Los signos de punto y coma al final.

Por ejemplo, SQL Server puede coincidir con la cadena N'SELECT * FROM T WHERE a = 10'de statement_text con el siguiente batch_text:

N'SELECT *
FROM T
WHERE a = 10' 

Sin embargo, la misma cadena no coincidiría con esta batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Server omite los caracteres de retorno de carro, avance de línea y espacio dentro de la primera consulta. En la segunda consulta, la secuencia WHERE b = 10 se interpreta de manera diferente que WHERE a = 10. En la comparación se distinguen mayúsculas, minúsculas y acentos (a pesar de que en la intercalación de la base de datos no se hace distinción entre mayúsculas y minúsculas), excepto en el caso de las palabras clave, donde no se hace distinción entre mayúsculas y minúsculas. La coincidencia es sensible a los espacios en blanco. En la comparación no se tienen en cuenta las formas abreviadas de las palabras clave. Por ejemplo, las palabras clave EXECUTE, EXEC y execute se consideran equivalentes.

Efecto de la guía de plan en la memoria caché del plan

Al crear una guía de plan en un módulo, se quita el plan de consulta para dicho módulo de la caché del plan. Al crear una guía de plan de tipo OBJECT o SQL en un lote, se quita el plan de consulta para un lote que tiene el mismo valor hash. Al crear una guía de plan de tipo TEMPLATE, se quitan todos los lotes de instrucción única de la memoria caché del plan dentro de esa base de datos.

Permisos

Para crear una guía de plan de tipo OBJECT, se requiere ALTER permiso en el objeto al que se hace referencia. Para crear una guía de plan de tipo SQL o TEMPLATE, necesita ALTER permiso en la base de datos actual.

Ejemplos

A. Crear una guía de plan de tipo OBJECT para una consulta en un procedimiento almacenado

En el ejemplo siguiente se crea una guía de plan que coincide con una consulta ejecutada en el contexto de un procedimiento almacenado basado en aplicación, y se aplica la sugerencia OPTIMIZE FOR a la consulta.

Éste es el procedimiento almacenado:

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  

Ésta es la guía de plan creada en la consulta del procedimiento almacenado:

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. Crear una guía de plan de tipo SQL para una consulta independiente

En el ejemplo siguiente se crea una guía de plan para que coincida con una consulta en un lote enviado por una aplicación que usa el procedimiento almacenado del sp_executesql sistema.

Este es el lote:

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

Para evitar que se genere un plan de ejecución en paralelo en esta consulta, cree la siguiente guía de plan:

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. Crear una guía de plan de tipo TEMPLATE para una consulta con parámetros

En el ejemplo siguiente se crea una guía de plan que coincide con cualquier consulta que parametriza en un formulario especificado y dirige SQL Server para forzar la parametrización de la consulta. Las dos consultas siguientes son equivalentes desde el punto de vista sintáctico, pero se diferencian solo en los valores literales de las constantes.

SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45639;  
  
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d   
    ON h.SalesOrderID = d.SalesOrderID  
WHERE h.SalesOrderID = 45640;  

Ésta es una guía de plan creada en la consulta con parámetros:

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
              INNER JOIN AdventureWorks2022.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)';  

En el ejemplo anterior, el valor del parámetro @stmt representa la forma de la consulta con parámetros. La única manera confiable de obtener este valor para usarlo en sp_create_plan_guide es recurrir al procedimiento almacenado del sistema sp_get_query_template . El script siguiente se puede utilizar para obtener la consulta con parámetros y, después crear una guía de plan basada en ella.

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h  
      INNER JOIN AdventureWorks2022.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)';  

D. Crear una guía de plan en una consulta enviada mediante una solicitud de cursor API

Las guías de plan pueden coincidir con consultas enviadas desde rutinas de cursor de servidor API. Estas rutinas son sp_cursorprepare, sp_cursorprepexec y sp_cursoropen. Las aplicaciones que usan las API de ADO, OLE DB y ODBC interactúan con frecuencia con SQL Server mediante cursores de servidor de API. Para ver la invocación de rutinas de cursor del servidor de API en SQL Server Profiler seguimientos, vea el evento de seguimiento RPC:Starting profiler.

Supongamos que aparecen los datos siguientes en un evento de seguimiento de analizador RPC:Starting para una consulta que desea optimizar con una guía de plan:

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;  

Como puede observar, el plan de la consulta SELECT en la llamada a sp_cursorprepexec utiliza una combinación de mezcla, pero desea utilizar una combinación hash. La consulta enviada mediante sp_cursorprepexec contiene parámetros, e incluye una cadena de consulta y una cadena de parámetro. Puede crear la siguiente guía de plan para cambiar la opción de plan utilizando las cadenas de consulta y parámetro exactamente como aparecen, carácter por carácter, en la llamada a sp_cursorprepexec.

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)';  

Cuando la aplicación vuelva a ejecutar esta consulta en el futuro, dicha ejecución se verá afectada por esta guía de plan, y se utilizará una combinación hash para procesar la consulta.

E. Crear una guía de plan mediante la obtención del plan de presentación XML a partir de un plan en caché

El ejemplo siguiente crea una guía de plan para una instrucción SQL ad hoc sencilla. El plan de consulta deseado para esta instrucción se proporciona en la guía de plan si se especifica el plan de presentación XML para la consulta directamente en el parámetro @hints . El ejemplo ejecuta primero la instrucción SQL para generar un plan en la memoria caché del plan. Para los fines de este ejemplo, se supone que el plan generado es el plan deseado y que no se requiere ninguna optimización adicional de la consulta. El plan de presentación XML para la consulta se obtiene consultando las vistas de administración dinámica sys.dm_exec_query_stats, sys.dm_exec_sql_texty sys.dm_exec_text_query_plan y está asignado a la variable @xml_showplan . A continuación, la variable @xml_showplan se pasa a la instrucción sp_create_plan_guide en el parámetro @hints . O bien, puede crear una guía de plan a partir de un plan de consulta de la memoria caché del plan con el procedimiento almacenado sp_create_plan_guide_from_handle .

USE AdventureWorks2022;  
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  

Consulte también

Guías de plan
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
Procedimientos almacenados del motor de base de datos (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)
sp_get_query_template (Transact-SQL)