Guías de plan

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Las guías del plan permiten optimizar el rendimiento de las consultas cuando no se puede o no se quiere cambiar directamente el texto de la consulta real en SQL 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. 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 se ejecuta la consulta, SQL Server hace coincidir la instrucción Transact-SQL con la guía del plan y adjunta la cláusula OPTION a la consulta en tiempo de ejecución o utiliza el plan de consulta especificado. Dado que el Optimizador de consultas de SQL Server suele seleccionar el mejor plan de ejecución para una consulta, recomendamos que los desarrolladores y administradores de bases de datos experimentados solo usen las guías de planes como último recurso.

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.

El plan de ejecución resultante forzado por esta característica será el mismo que el plan que se va a forzar, o similar. Dado que el plan resultante puede no ser idéntico al plan especificado por la guía de plan, el rendimiento de los planes puede variar. En raras ocasiones, la diferencia de rendimiento puede ser significativa y negativa. En ese caso, el administrador debe quitar el plan forzado.

Las guías del plan no se pueden utilizar en todas las ediciones de Microsoft SQL Server. Para obtener una lista de las características admitidas por las ediciones de SQL Server, vea Características compatibles con las ediciones de SQL Server 2016. 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.

Tipos de guías de plan

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

OBJECT [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 AdventureWorks2022:

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). Sin embargo, debido a que hay relativamente pocos pedidos de ventas originados en Australia, el rendimiento disminuye cuando la consulta se ejecuta utilizando valores de parámetros de países/regiones 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 AdventureWorks2022 .

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 coincide con las consultas que se ejecutan en el contexto de instrucciones y lotes independientes de Transact-SQL 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 usando 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)';  

Como ejemplo adicional, considere la siguiente instrucción SQL enviada mediante 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.

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

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.

TEMPLATE, guía de plan

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

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

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 AdventureWorks2022 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 AdventureWorks2022 serán aptas para buscar las coincidencias con esta consulta. No obstante, si la base de datos actual es AdventureWorks2022 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 planes basadas en SQL o TEMPLATE, SQL Server compara los valores de los argumentos @module_or_batch y @params a una consulta comparando los dos valores carácter por carácter. Esto significa que debe proporcionar el texto exactamente como SQL Server lo recibe en el lote real.

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 el mismo formato, carácter por carácter, 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.

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.

Related Tasks

Consulte también

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)