Diseñar guías de plan para consultas con parámetros
Se puede crear una guía de plan basada en una consulta que tiene parámetros. Una consulta se puede parametrizar por alguna de las siguientes razones:
- La consulta se envía mediante sp_executesql.
- La parametrización forzada está habilitada en la base de datos. Así se parametrizan todas las consultas aptas.
- Se ha creado una guía de plan aparte en una clase de consultas a la que pertenece esta consulta, especificándose que tengan parámetros.
Cuando se crea una guía de plan basada en una consulta con parámetros, en esencia lo que se hace es crear una guía de plan para todas las consultas que se parametrizan de la misma forma, únicamente variando en sus valores literales constantes. Por ejemplo, en una base de datos en la que se ha habilitado la parametrización forzada, las dos consultas siguientes se parametrizan de la misma forma:
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 50
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 100
Para crear una guía de plan basada en consultas con parámetros, cree una guía de plan de tipo SQL y especifique la forma con parámetros de la consulta en el procedimiento almacenado sp_create_plan_guide.
Por ejemplo, para obtener la forma con parámetros de una de las consultas del ejemplo anterior y crear una guía de plan basada en ella para forzar al optimizador a usar una combinación hash, siga estos pasos:
- Obtenga la forma con parámetros de la consulta ejecutando sp_get_query_template.
- Si SQL Server todavía no está parametrizando la consulta mediante sp_executesql o la opción SET de base de datos PARAMETERIZATION FORCED, cree una guía de plan de tipo TEMPLATE para forzar la parametrización.
- Cree una guía de plan de tipo SQL basada en la consulta con parámetros.
El siguiente proceso por lotes ejecuta los tres pasos:
--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 101 GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50',
@stmt OUTPUT,
@params OUTPUT
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)'
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(HASH JOIN)'
La guía de plan no será aplicable a todas las consultas que se parametrizan con la forma especificada pero que contienen valores literales constantes distintos.
Vea también
Conceptos
Optimizar consultas en aplicaciones implementadas mediante guías de plan
Otros recursos
Rendimiento de las consultas
sp_create_plan_guide (Transact-SQL)