Guías de plan

Las guías de plan permiten optimizar el rendimiento de las consultas cuando no pueda o no desee cambiar directamente el texto de la consulta real en SQL Server 2012. Las guías de plan influyen en la optimización de las consultas adjuntando sugerencias de consulta o un plan de consulta fijo para ellas. Las guías de plan pueden ser de gran utilidad cuando el rendimiento de un pequeño subconjunto de consultas de una aplicación de base de datos proporcionado por otro proveedor no es el esperado. En la guía de plan, se especifica la instrucción Transact-SQL que se desea optimizar y además una cláusula OPTION que incluye las sugerencias de consulta que se desean usar o un plan de consulta específico con el que desea optimizar la consulta. Cuando la consulta se ejecuta, el SQL Server hace coincidir la instrucción Transact-SQL con la guía de plan y además adjunta en tiempo de ejecución la cláusula OPTION a la consulta o usa el plan de consulta especificado.

El número total de guías de plan que se pueden crear solo está limitado por los recursos de los que disponga el sistema. No obstante, las guías de plan deberían limitarse a aquellas consultas de gran importancia cuyo rendimiento se desea mejorar o estabilizar. No se deben usar las guías de plan para influenciar la mayor parte de la carga de la consulta de una aplicación implementada.

[!NOTA]

Las guías de plan no se pueden usar en todas las ediciones de Microsoft SQL Server. Para obtener una lista de características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2012. 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 de plan permanecen intactas cuando se restaura o adjunta una base de datos a una versión actualizada de SQL Server.

Tipos de guías de plan

Se pueden crear los siguientes tipos de guías de plan.

• OBJETO [guía de plan]
  Una guía de plan OBJECT compara las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQL, funciones escalares definidas por el usuario, funciones definidas por el usuario con valores de tabla de múltiples instrucciones y desencadenadores DML.

  Suponga que el siguiente procedimiento almacenado, que usa el parámetro @Country\_region, está en una aplicación de base de datos que se implementa con la base de datos AdventureWorks2012 :

  CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
  AS
  BEGIN
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
          Sales.SalesTerritory AS t
      WHERE h.CustomerID = c.CustomerID
          AND c.TerritoryID = t.TerritoryID
          AND CountryRegionCode = @Country_region
  END;
  

  Asuma que este procedimiento almacenado ha sido compilado y optimizado para @Country\_region = N'AU' (Australia). Sin embargo, dado hay relativamente pocos pedidos de ventas que se originen en Australia, el rendimiento se reduce cuando la consulta ejecuta usando valores para los parámetros que se corresponden con países con más pedidos de ventas. Dado que el mayor número de pedidos de ventas se origina en Estados Unidos, el rendimiento de un plan de consulta generado para @Country\_region = N'US' será probablemente mejor para todos los valores posibles del parámetro @Country\_region.

  Puede solucionar este problema modificando el procedimiento almacenado y agregando la sugerencia de consulta OPTIMIZE FOR a la consulta. No obstante, puesto que el procedimiento almacenado se encuentra en una aplicación implementada, no puede modificar directamente el código de la aplicación. En su lugar, puede crear la guía de plan siguiente en la base de datos AdventureWorks2012 .

  sp_create_plan_guide 
  @name = N'Guide1',
  @stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
          Sales.Customer AS c,
          Sales.SalesTerritory AS t
          WHERE h.CustomerID = c.CustomerID 
              AND c.TerritoryID = t.TerritoryID
              AND CountryRegionCode = @Country_region',
  @type = N'OBJECT',
  @module_or_batch = N'Sales.GetSalesOrderByCountry',
  @params = NULL,
  @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
  

  Cuando se ejecute la consulta especificada en la instrucción sp_create_plan_guide, se modificará la consulta antes de la optimización para incluir la cláusula OPTIMIZE FOR (@Country = N''US'').

• Guía de plan SQL
  Una guía de plan de SQL compara las consultas que se ejecutan en el contexto de instrucciones independientes de Transact-SQL y lotes que no forman parte de un objeto de base de datos. Las guías de plan basadas en SQL también se pueden usar para comparar consultas que se parametrizan en un formulario especificado. Las guías de plan de SQL se aplican a las instrucciones y lotes independientes de Transact-SQL. Con frecuencia, las aplicaciones envían esas instrucciones utilizando el procedimiento almacenado del sistema sp_executesql. Considere, por ejemplo, el siguiente lote independiente:

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

  Para evitar que se genere un plan de ejecución paralelo en esta consulta, cree la siguiente guía de plan y establezca la sugerencia de consulta MAXDOP en 1 en el parámetro @hints.

  sp_create_plan_guide 
  @name = N'Guide2', 
  @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)';
  

  Importante
  Los valores que se proporcionan para los argumentos @params y @module_or_batch de la instrucción sp_create_plan guide deben coincidir con el texto correspondiente enviado en la consulta real. Para obtener más información, vea sp_create_plan_guide (Transact-SQL) y Usar SQL Server Profiler para crear y probar guías de plan.

  También se pueden crear guías de plan SQL en consultas que se parametrizan en el mismo formulario cuando se establece el valor de la opción SET de base de datos PARAMETERIZATION en FORCED, o cuando se crea una guía de plan TEMPLATE en la que se especifica que debe parametrizarse una clase de consultas.

• Guía de plan TEMPLATE
  Una guía de plan TEMPLATE compara consultas independientes que se parametrizan en un formulario especificado. Estas guías de plan se usan para reemplazar la opción PARAMETERIZATION actual de una base de datos para una clase de consultas por medio de SET.

  Puede crear una guía de plan TEMPLATE en cualquiera de las situaciones siguientes:

  • Se ha establecido el valor de la opción PARAMETRIZATION de la base de datos en FORCED mediante el mandato SET, pero hay consultas que desea compilar según las reglas parametrización simple.

  • Se ha establecido el valor de la opción PARAMETERIZATION de la base de datos en SIMPLE (el valor predeterminado), pero desea que intente la parametrización forzada en una clase de consultas.

Requisitos de coincidencia de la guía de plan

Las guías de plan tienen como ámbito la base de datos en la que se crean. Por tanto, solo se pueden buscar las coincidencias con la consulta de las guías de plan que existen en la base de datos actual cuando se ejecuta una consulta. Por ejemplo, si AdventureWorks2012 es la base de datos actual y se ejecuta la consulta siguiente:

SELECT FirstName, LastName FROM Person.Person;

Solo las guías de plan de la base de datos AdventureWorks2012 serán aptas para buscar las coincidencias con esta consulta. No obstante, si la base de datos actual es AdventureWorks2012 y se ejecutan las instrucciones siguientes:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

Solo las guías de plan de DB1 serán aptas para buscar las coincidencias con la consulta, puesto que la consulta se ejecuta en el contexto de DB1.

En el caso de las guías de plan basadas en SQL o TEMPLATE, SQL Server examina los valores para los argumentos @module\_or\_batch y @params con una consulta, comparando ambos valores carácter a carácter. Esto significa que se debe proporcionar el texto exactamente como lo recibe SQL Server en el lote real.

Si @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 en formato idéntico, 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 una guía de plan normal (SQL u OBJECT) y una guía de plan TEMPLATE se pueden aplicar a una instrucción, solo se utilizará la guía de plan normal.

[!NOTA]

El lote que contiene la instrucción en la que desea crear una guía de plan no puede contener una instrucción USE database.

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

Tareas relacionadas

Vea también

Referencia

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)