Compartir vía

sp_create_plan_guide (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure 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'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ ,... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

Argumentos

[ @name = ] N'name'

Nombre de la guía de plan. @name es sysname, sin valor predeterminado y una longitud máxima de 124 caracteres. 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 (#).

[ @stmt = ] N'stmt'

Instrucción Transact-SQL en la que se va a crear una guía de plan. @stmt es nvarchar(max), con un valor predeterminado de NULL. Cuando el optimizador de consultas de SQL Server reconoce una consulta que coincide con @stmt, @name surte efecto. Para que la creación de una guía de plan se realice correctamente, @stmt debe aparecer en el contexto especificado por los parámetros @type, @module_or_batch y @params .

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

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

Tipo de entidad en la que aparece @stmt . Esto especifica el contexto para la coincidencia de @stmt para @name. @type es nvarchar(60) y puede ser uno de estos valores:

  • OBJECT

    Indica @stmt 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 @stmt aparece en el contexto de una instrucción independiente o un 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 de SQL. Si SQL se especifica , la sugerencia PARAMETERIZATION { FORCED | SIMPLE } de consulta no se puede especificar en el parámetro @hints .

  • TEMPLATE

    Indica que la guía de plan se aplica a cualquier consulta que parametrice con el formulario indicado en @stmt. Si TEMPLATE se especifica , solo se puede especificar la PARAMETERIZATION { FORCED | SIMPLE } sugerencia de consulta en el parámetro @hints . Para obtener más información sobre TEMPLATE las guías de plan, 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' }

Especifica el nombre del objeto en el que aparece @stmt o el texto por lotes en el que aparece @stmt . @module_or_batch es nvarchar(max), con un valor predeterminado de NULL. El texto por lotes no puede incluir una USE <database> instrucción .

Para que una guía de plan coincida con un lote enviado desde una aplicación, @module_or_batch 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 Notas.

[ <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 @stmt. Si <schema_name> no se especifica, <schema_name> usa el esquema del usuario actual. Si NULL se especifica y @type es SQL, el valor de @module_or_batch se establece en el valor de @stmt. Si @type es TEMPLATE, @module_or_batch debe ser NULL.

[ @params = ] N'@parameter_name data_type [ ,... n ]'

Especifica las definiciones de todos los parámetros incrustados en @stmt. @params es nvarchar(max), con un valor predeterminado de NULL. @params solo se aplica cuando se cumple alguna de las siguientes opciones:

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

  • @stmt 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 parametrizarlo. El envío de consultas parametrizadas desde las API de base de datos (incluido ODBC, OLE DB y ADO.NET) aparece en SQL Server como llamadas a sp_executesql o a rutinas de cursor del servidor de API; por lo tanto, también pueden coincidir con SQL guías de plan o TEMPLATE .

@params debe proporcionarse 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 Notas. Si el lote no contiene parámetros, NULL debe especificarse. El tamaño de @params solo está limitado por la memoria del servidor disponible.

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }

@hints es nvarchar(max), con un valor predeterminado de NULL.

  • OPTION ( <query_hint> [ , ...n ] )

    Especifica una OPTION cláusula que se va a asociar a una consulta que coincida con @stmt. @hints debe ser sintácticamente igual que una OPTION cláusula en una SELECT instrucción y puede contener cualquier secuencia válida de sugerencias de consulta.

  • <XML_showplan>'

    Plan de consulta en formato XML que se va a aplicar como sugerencia.

    Se recomienda asignar el plan de presentación XML a una variable. De lo contrario, debe escapar las comillas simples en el plan de presentación antes de ellas con otra comilla simple. Vea el ejemplo E.

  • NULL

    Indica que cualquier sugerencia existente especificada en la OPTION cláusula de la consulta no se aplica a la consulta. Para obtener más información, vea cláusula OPTION.

Comentarios

Los argumentos que sp_create_plan_guide se deben proporcionar 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 de parámetro especificado coincida con el nombre del parámetro en la posición donde se usa el nombre.

Puede crear más de una OBJECT guía de plan o SQL consulta 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 de @module_or_batch que haga referencia a un desencadenador DML, función o procedimiento almacenado que especifique la WITH ENCRYPTION cláusula o que sea 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. Al intentar quitar una tabla que es un desencadenador definido en ella al que hace referencia una guía de plan, también se produce un error.

Las guías de plan no se pueden usar en todas las ediciones de SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, consulte Ediciones y características admitidas de SQL Server 2022. Las guías de plan son visibles en todas las ediciones. También se pueden adjuntar bases de datos que incluyen guías de plan a cualquier versión. Las guías del plan permanecen intactas al restaurar o adjuntar una base de datos a una versión actualizada de SQL Server. Después de realizar una actualización de servidor, debe comprobar la idoneidad de las guías de plan en cada base de datos.

Requisitos de coincidencia de la guía de plan

Para las guías de plan que especifican @type de SQL o TEMPLATE para que coincidan correctamente con una consulta, los valores de @module_or_batch y @params [, ... n ] debe proporcionarse exactamente con el mismo formato que sus homólogos presentados 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, consulte Uso de SQL Server Profiler para crear y probar guías de plan.

Cuando @type es y @module_or_batch se establece NULLen , el valor de @module_or_batch se establece en el valor de @stmt. SQL Esto significa que el valor de @stmt debe proporcionarse exactamente con 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 @stmt para @module_or_batch y @params [, ... n ], o si @type es OBJECT, en el texto de la consulta correspondiente dentro <object_name>de , no se tienen en cuenta los siguientes elementos de cadena:

  • Caracteres de espacio en blanco (pestañas, espacios, retornos de carro o fuentes de línea) dentro de la cadena
  • Comentarios (-- o /* */)
  • Los signos de punto y coma al final.

Por ejemplo, SQL Server puede coincidir con la cadena de @stmt con el siguiente @module_or_batchN'SELECT * FROM T WHERE a = 10':

 N'SELECT *
 FROM T
 WHERE a = 10'

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

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. La coincidencia distingue mayúsculas de minúsculas y distingue acentos (incluso cuando la intercalación de la base de datos no distingue mayúsculas de minúsculas), excepto si hay palabras clave que no distinguen mayúsculas de 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 del 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 de un lote que es el mismo valor hash. Al crear una guía de plan de tipo TEMPLATE , se quitan todos los lotes de instrucciones únicas de la memoria caché del plan dentro de esa base de datos.

Permisos

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

Ejemplos

A Creación de 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.

Este 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

Esta es la guía de plan creada en la consulta en el 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. Creación de 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. Creación de una guía de plan de tipo TEMPLATE para el formulario con parámetros de una consulta

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;

Esta es la guía de plan en la forma parametrizada de la consulta:

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 su uso es sp_create_plan_guide usar el procedimiento almacenado del sistema sp_get_query_template . El siguiente script obtiene la consulta parametrizada y, a continuación, crea una guía de plan en él.

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

Importante

El valor de los literales de constante del parámetro @stmt pasado a sp_get_query_template podría afectar al tipo de datos elegido para el parámetro que reemplaza al valor literal. Esto afectaría a la correspondencia de la guía de plan. Es posible que tenga que crear más de una guía de plan para controlar distintos intervalos de valores de parámetros.

D. Creación de una guía de plan en una consulta enviada mediante una solicitud de cursor de API

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

Supongamos que los siguientes datos aparecen en un RPC:Starting evento de seguimiento del generador de perfiles 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)';

Las ejecuciones posteriores de esta consulta por parte de la aplicación se ven afectadas por esta guía de plan y se usa una combinación hash para procesar la consulta.

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

En el ejemplo siguiente se crea una guía de plan para una instrucción ad hoc SQL simple. El plan de consulta deseado para esta instrucción se proporciona en la guía de plan especificando el plan de presentación XML para la consulta directamente en el @hints parámetro . En primer lugar, el ejemplo ejecuta la SQL instrucción 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 más optimización de consultas. El plan de presentación XML de la consulta se obtiene consultando las sys.dm_exec_query_statsvistas de administración dinámica , sys.dm_exec_sql_texty sys.dm_exec_text_query_plan y se asigna a la @xml_showplan variable . 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

Contenido relacionado