sp_create_plan_guide (Transact-SQL)

Actualizado: 15 de septiembre de 2007

Crea una guía de plan para asociar sugerencias de consulta a las consultas de una base de datos. Para obtener más información acerca de las guías de plan, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan.

Icono de vínculo a temasConvenciones 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 ] )' | NULL }

Argumentos

  • [ @name= ] N'plan_guide_name'
    Especifica el nombre que identifica 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 empezar por el signo de número (#).
  • [ @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.

    statement_text se debe proporcionar de tal forma que SQL Server pueda compararlo con la instrucción correspondiente suministrada dentro del lote o módulo identificado por @module_or_batch y @params. statement_text se convierte en un formato interno estándar antes de que SQL Server intenta realizar esta comparación (no se tienen en cuenta los espacios en blanco, los comentarios ni el uso de mayúsculas o minúsculas en las palabras clave). 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 una instrucción o lote independiente que se puede enviar a SQL Server a través de cualquier mecanismo. Las instrucciones 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 puede especificar en el parámetro @hints.
  • [ @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 USE database.

    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 incrustados en statement_text. @params sólo se aplica cuando se da una de las dos condiciones siguientes:

    • @type='SQL' o 'TEMPLATE'. Si 'TEMPLATE', @params no puede 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 de un proceso de parametrización. Las consultas con parámetros enviadas desde API de base de datos (incluidas de ODBC, OLE DB y ADO.NET) se muestran en SQL Server como si fuesen 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 con el mismo formato con el que se envía a SQL Server, utilizando sp_executesql o enviándolo 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 ] )' | NULL }
    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. NULL indica la ausencia de una cláusula OPTION. Para obtener más información, vea OPTION (cláusula de Transact-SQL).

Notas

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.

Sólo se puede crear una guía de plan para una combinación dada de @module_or_batch y @stmt.

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 sólo se pueden usar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server 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.

Guía de plan que coincide con requisitos

Para que las guías de plan que especifican @type='SQL' o @type='TEMPLATE' coincidan con una consulta correctamente, 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 Analizador de SQL Server. Para obtener más información, vea Usar el Analizador de SQL Server para crear y probar guías de plan.

Si @type = 'SQL' y @module\_or\_batch se establecen en NULL, el valor de @module\_or\_batch se establece en el valor de @stmt. Esto significa que debe suministrarse el valor de statement_text con el mismo formato exactamente, carácter a carácter, en que se envía a SQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.

Cuando SQL Server lleva a cabo la coincidencia del valor de statement_text con batch_text y @parameter_name data_type [,...n ], o de @type='OBJECT' con el texto de la correspondiente consulta 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.
  • 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 caché del plan

Al crear una guía de plan en un módulo, se quita el plan de consultas para ese 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 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 caché del plan dentro de esa base de datos.

Permisos

Para crear una guía de plan de tipo OBJECT (especificando @type='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 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = 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 AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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. La secuencia de comandos 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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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)';
ms179880.note(es-es,SQL.90).gifImportante:
El valor de los literales de constante del parámetro @stmt pasado a sp_get_query_template podría afectar al tipo de datos que se elige 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 las trazas del Analizador de SQL Server, vea el evento de traza de analizador RPC:Starting.

Supongamos que aparecen los datos siguientes en un evento de traza 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 AdventureWorks.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.

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.

Vea también

Referencia

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Procedimientos almacenados del motor de base de datos (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión Historial

5 de diciembre de 2005

Contenido nuevo:
  • Se ha agregado la información de que la sugerencia de consulta PARAMETERIZATION { FORCED | SIMPLE } no se puede especificar en guías de plan SQL.
  • En la sección Notas, se han aclarado las directrices para el orden y la coherencia de la sintaxis.
Contenido modificado:
  • Se ha indicado que las guías de plan OBJECT no pueden hacer referencia a objetos cifrados o temporales.

15 de septiembre de 2007

Contenido actualizado:
  • Se aclaró la guía de plan que coincide con requisitos para statement_text si @type = 'SQL' y @module_or_batch se establecen en NULL.
  • Se agregó información sobre el efecto de crear las guías de plan en la caché del plan.