sp_create_plan_guide (Transact-SQL)

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 Descripción de las guías de plan.

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 las guías de plan pertenecen al ámbito de la base de datos actual. plan_guide_name debe cumplir las reglas de los identificadores y no puede comenzar por almohadilla (#). La longitud máxima de plan_guide_name es de 124 caracteres.

• [ @stmt = ] N'statement_text'
  Es una instrucción Transact-SQL que se va a utilizar en la creación de 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 sea correcta, statement_text debe aparecer en el contexto especificado por los parámetros @type, @module_or_batch y @params.

  Se debe proporcionar statement_text de tal forma que permita al optimizador de consultas hacerlo coincidir con la instrucción correspondiente suministrada 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 statement_text sólo está limitado por la memoria disponible en el servidor.

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

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

  • SQL
    Indica que statement_text aparece en el contexto de un lote o instrucción independiente que se puede enviar a SQL Server a través de cualquier mecanismo. Las instrucciones de Transact-SQL enviadas mediante objetos Common Language Runtime (CLR) o procedimientos almacenados extendidos, o mediante el uso de EXEC N'sql_string', se procesan como lotes en el servidor y, por tanto, deberían 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 se parametrice como se indica en statement_text. Si se especifica TEMPLATE, sólo se puede especificar la sugerencia de consulta PARAMETERIZATION { FORCED | SIMPLE } en el parámetro @hints. Para obtener más información acerca de las guías de plan TEMPLATE, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

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

  Para que una guía de plan coincida con un lote enviado desde una aplicación, batch_tex debe suministrarse en el mismo formato, carácter a carácter, en 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, función escalar, función con valores de tabla con varias instrucciones o desencadenador DML de Transact-SQL que contenga statement_text. Si no se especifica schema_name , schema_name utiliza 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 insertados en statement_text. @params sólo 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 bien SQL Server envía internamente una instrucción después del proceso de parametrización. Las consultas con parámetros enviadas desde las API de base de datos (incluidas ODBC, OLE DB y ADO.NET) se muestran en SQL Server como llamadas a sp_executesql o a rutinas de cursor de servidor API; por tanto, se pueden comparar con guías de plan de tipo SQL o TEMPLATE. Para obtener más información acerca de la parametrización y las guías de plan, vea Cómo asocia SQL Server las guías de plan a consultas.

  @parameter_name data_type debe suministrarse exactamente en el mismo formato en el que se envía a SQL Server mediante sp_executesql o internamente después de un proceso de parametrización. Para obtener más información, vea la sección Notas. Si el lote no contiene parámetros, debe especificarse NULL. El tamaño de @params sólo 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 adjunta a una consulta que coincida con @stmt. Desde el punto de vista sintáctico, @hints debe ser 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 consultas 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 OPTION (cláusula de 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 sólo se indica el valor del parámetro, también deben omitirse los demás nombres de parámetro y sólo se indicará su valor. Los nombres de argumento sólo se incluyen con fines de descripción, para ayudar a entender la sintaxis. SQL Server no comprueba si el nombre del parámetro especificado coincide con el nombre del parámetro en la posición donde se utiliza.

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.

Nota

Aunque las guías de plan solo se pueden utilizar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server sí se pueden ver en todas las versiones. También se pueden adjuntar bases de datos que incluyen guías de plan a cualquier versión. Las guías de plan permanecen intactas cuando se restaura o adjunta una base de datos a una versión actualizada de SQL Server 2008. 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 que las guías de plan que especifican @type = 'SQL' o @type = 'TEMPLATE' coincidan correctamente con una consulta, los valores de batch_text y @parameter_name data_type [,...n ] deben suministrarse exactamente en el mismo formato que sus equivalentes enviados por la aplicación. Esto significa que es necesario suministrar el texto del lote exactamente como lo recibe el compilador de SQL Server. Para capturar el texto real del lote y del parámetro, se puede utilizar el SQL Server Profiler. Para obtener más información, vea Usar SQL Server Profiler para crear y probar guías de plan.

Si se especifica @type = 'SQL' y @module_or_batch es NULL, se establecerá el valor de @module_or_batch en el valor de @stmt. Esto significa que se debe proporcionar el valor de statement_text debe suministrarse en el mismo formato, carácter a carácter, en que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.

Si SQL Server realiza la coincidencia del valor de statement_text con batch_text y @parameter_name data_type [,...n ], o si @type = **'**OBJECT', con el texto de la correspondiente consulta dentro de object_name, no se tendrán 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.

• Los comentarios (-- o /* */).

• Los signos de punto y coma al final.

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

N'SELECT *

FROM T

WHERE a=10'

Sin embargo, esa misma cadena no se correspondería con esta cadena de 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. 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.

Para obtener más información sobre cómo se establece la concordancia entre las guías de plan y las consultas, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan.

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 consultas para dicho módulo de la memoria caché del plan. Al crear una guía de plan de tipo OBJECT o SQL en un lote, se quita el plan de consultas 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 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.

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 que coincide con una consulta de un lote enviado por una aplicación que utiliza el procedimiento almacenado del sistema sp_executesql.

Éste 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 coincida con cualquier consulta que se parametrice de una forma específica, e indica a SQL Server que fuerce la parametrización de la consulta. Las dos consultas siguientes son equivalentes desde el punto de vista sintáctico, pero se diferencian sólo en los valores literales de las constantes.

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

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.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 utilizarlo en sp_create_plan_guide es utilizar el 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 AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.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. Puede que tenga que crear más de una guía de plan para abarcar los distintos intervalos de valores de parámetros.

Para obtener más información sobre cómo obtener la forma con parámetros de una consulta para utilizarla en una guía de plan basada en TEMPLATE, vea Diseñar guías de plan para consultas con parámetros.

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 utilizan las API de ADO, OLE DB y ODBC interactúan frecuentemente con SQL Server mediante cursores de servidor API. Para obtener más información, vea Cursores de servidor de la API. Para ver la invocación de las rutinas de cursor de servidor API en los seguimientos del SQL Server Profiler, vea el evento de seguimiento de analizador RPC:Starting.

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 h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @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 >= @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.

Para obtener información sobre cómo utilizar la sugerencia de consulta USE PLAN en una guía de plan para una consulta enviada con un cursor, vea Usar la sugerencia de consulta USE PLAN en consultas con cursores.

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 consultas 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_text y 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 mediante el procedimiento almacenado sp_create_plan_guide_from_handle.

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

Vea también

Referencia

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)