Guías de planPlan Guides

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL DatabaseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

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 ServerSQL Server.Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL ServerSQL Server. 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.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. 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.Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. 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.In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. Cuando la consulta se ejecuta, el SQL ServerSQL 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.When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

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.The total number of plan guides you can create is limited only by available system resources. No obstante, las guías de plan deberían limitarse a aquellas consultas de gran importancia cuyo rendimiento se desea mejorar o estabilizar.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. 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.Plan guides should not be used to influence most of the query load of a deployed application.

Nota

Las guías de plan no se pueden usar en todas las ediciones de MicrosoftMicrosoftSQL ServerSQL Server.Plan guides cannot be used in every edition of MicrosoftMicrosoftSQL ServerSQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL ServerSQL Server, vea Características compatibles con las ediciones de SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. Las guías de plan son visibles en todas las ediciones.Plan guides are visible in any edition. También se pueden adjuntar bases de datos que incluyen guías de plan a cualquier versión.You can also attach a database that contains plan guides to any edition. Las guías de plan permanecen intactas cuando se restaura o adjunta una base de datos a una versión actualizada de SQL ServerSQL Server.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

Tipos de guías de planTypes of Plan Guides

Se pueden crear los siguientes tipos de guías de plan.The following types of plan guides can be created.

OBJECT [guía de plan]OBJECT plan guide

Una guía de plan OBJECT compara las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQLTransact-SQL , funciones escalares definidas por el usuario, funciones definidas por el usuario con valores de tabla de múltiples instrucciones y desencadenadores DML.An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

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 AdventureWorks2012AdventureWorks2012:Suppose the following stored procedure, which takes the @Country_region parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

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 se ha compilado y optimizado para @Country_region = N'AU' (Australia).Assume that this stored procedure has been compiled and optimized for @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.However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. 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.Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

Puede solucionar este problema modificando el procedimiento almacenado y agregando la sugerencia de consulta OPTIMIZE FOR a la consulta.You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. 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.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. En su lugar, puede crear la guía de plan siguiente en la base de datos AdventureWorks2012AdventureWorks2012 .Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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'') .When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

Guía de plan SQLSQL plan guide

Una guía de plan de SQL compara las consultas que se ejecutan en el contexto de instrucciones independientes de Transact-SQLTransact-SQL y lotes que no forman parte de un objeto de base de datos.An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. Las guías de plan basadas en SQL también se pueden usar para comparar consultas que se parametrizan en un formulario especificado.SQL-based plan guides can also be used to match queries that parameterize to a specified form. Las guías de plan de SQL se aplican a las instrucciones y lotes independientes de Transact-SQLTransact-SQL .SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. Con frecuencia, las aplicaciones envían esas instrucciones usando el procedimiento almacenado del sistema sp_executesql .Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. Considere, por ejemplo, el siguiente lote independiente:For example, consider the following stand-alone batch:

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 .To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

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

Como ejemplo adicional, considere la siguiente instrucción SQL enviada mediante sp_executesql.As another example, consider the following SQL statement submitted using sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;  

Para crear un plan único para cada ejecución de esta consulta, cree la guía de plan siguiente y use la sugerencia de consulta OPTION (RECOMPILE) en el parámetro @hints.To create a unique plan for every execution of this query, create the following plan guide and use the OPTION (RECOMPILE) query hint in the @hints parameter.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Importante

Los valores que se proporcionan para los argumentos @module_or_batch y @params de la instrucción sp_create_plan guide deben coincidir con el texto correspondiente enviado en la consulta real.The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. 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.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

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.SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

TEMPLATE, guía de planTEMPLATE plan guide

Una guía de plan TEMPLATE compara consultas independientes que se parametrizan en un formulario especificado.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. 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.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

Puede crear una guía de plan TEMPLATE en cualquiera de las situaciones siguientes:You can create a TEMPLATE plan guide in either of the following situations:

  • Se ha establecido el valor de la opción PARAMETERIZATION de la base de datos en FORCED mediante el comando SET, pero hay consultas que quiere compilar según las reglas de la parametrización SIMPLE.The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of Simple Parameterization.

  • Se ha establecido el valor de la opción PARAMETERIZATION de la base de datos en SIMPLE (el valor predeterminado), pero quiere probar la parametrización FORCED en una clase de consultas.The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want Forced Parameterization to be tried on a class of queries.

Requisitos de coincidencia de la guía de planPlan Guide Matching Requirements

Las guías de plan tienen como ámbito la base de datos en la que se crean.Plan guides are scoped to the database in which they are created. 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.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. Por ejemplo, si AdventureWorks2012AdventureWorks2012 es la base de datos actual y se ejecuta la consulta siguiente:For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

Solo las guías de plan de la base de datos AdventureWorks2012AdventureWorks2012 serán aptas para buscar las coincidencias con esta consulta.Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. No obstante, si la base de datos actual es AdventureWorks2012AdventureWorks2012 y se ejecutan las instrucciones siguientes:However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

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.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

En el caso de las guías de plan basadas en SQL o TEMPLATE, SQL ServerSQL Server examina los valores para los argumentos @module_or_batch y @params con una consulta, comparando ambos valores carácter a carácter.For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. Esto significa que se debe proporcionar el texto exactamente como lo recibe SQL ServerSQL Server en el lote real.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

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 en formato idéntico, carácter a carácter, en que se envía a SQL ServerSQL Server.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt. This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. Para facilitar esta concordancia no se realiza ninguna conversión interna.No internal conversion is performed to facilitate this match.

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.When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

Nota

El lote que contiene la instrucción en la que quiere crear una guía de plan no puede contener una instrucción USE database .The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

Efecto de la guía de plan en la caché del planPlan Guide Effect on the Plan Cache

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.Creating a plan guide on a module removes the query plan for that module from the plan cache. 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.Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. 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.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

TareaTask TemaTopic
Describe cómo crear una guía de plan.Describes how to create a plan guide. Crear una nueva guía de planCreate a New Plan Guide
Describe cómo crear una guía de plan para consultas con parámetros.Describes how to create a plan guide for parameterized queries. Crear una guía de plan para consultas con parámetrosCreate a Plan Guide for Parameterized Queries
Describe cómo controlar el comportamiento de parametrización de consultas mediante guías de plan.Describes how to control query parameterization behavior by using plan guides. Especificar el comportamiento de parametrización de consultas por medio de guías de planSpecify Query Parameterization Behavior by Using Plan Guides
Describe cómo incluir un plan de consulta fijo en una guía de plan.Describes how to include a fixed query plan in a plan guide. Aplicar un plan de consulta fijo a una guía de planApply a Fixed Query Plan to a Plan Guide
Describe cómo especificar sugerencias de consulta en una guía de plan.Describes how to specify query hints in a plan guide. Asociar sugerencias de consulta a una guía de planAttach Query Hints to a Plan Guide
Describe cómo ver las propiedades de la guía de plan.Describes how to view plan guide properties. Ver propiedades de la guía de planView Plan Guide Properties
Describe cómo usar SQL Server Profiler para crear y probar guías de plan.Describes how to use SQL Server Profiler to create and test plan guides. Usar SQL Server Profiler para crear y probar guías de planUse SQL Server Profiler to Create and Test Plan Guides
Describe cómo validar las guías de plan.Describes how to validate plan guides. Validar guías de planes tras una actualizaciónValidate Plan Guides After Upgrade

Consulte tambiénSee Also

sp_create_plan_guide (Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL) sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)sys.fn_validate_plan_guide (Transact-SQL)