Especificar el comportamiento de parametrización de consultas mediante guías de plan

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Al establecer la opción de base de datos PARAMETERIZATION en SIMPLE, el optimizador de consultas de SQL Server podría optar por parametrizar las consultas. Esta parametrización sustituye los valores literales de una consulta con parámetros. Este proceso se conoce como parametrización simple. Cuando SIMPLE la parametrización está en vigor, no puede controlar qué consultas se parametrizan y qué consultas no. Sin embargo, puede especificar que todas las consultas de una base de datos se parametrizan estableciendo la opción de base de datos PARAMETERIZATION en FORCED. Este proceso se conoce como parametrización forzada.

Puede cambiar el comportamiento de parametrización de una base de datos por medio de guías de plan tal y como se indica a continuación:

Fíjese en la consulta siguiente en la base de datos AdventureWorks2025 :

SELECT pi.ProductID,
       SUM(pi.Quantity) AS Total
FROM Production.ProductModel AS pm
     INNER JOIN Production.ProductInventory AS pi
         ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING SUM(pi.Quantity) > 50;

Como administrador de bases de datos, determina que no desea habilitar la parametrización forzada en todas las consultas de la base de datos. Sin embargo, quiere evitar costos de compilación en todas las consultas que son sintácticamente equivalentes a la consulta anterior, pero que solo difieren en sus valores literales constantes. En otras palabras, desea que la consulta se parametrice para que se reutilice un plan de consulta para este tipo de consulta. En este caso, siga los pasos siguientes:

1. Recupere la forma parametrizada de la consulta. La única manera segura de obtener este valor para su uso en sp_create_plan_guide es mediante el procedimiento almacenado del sistema sp_get_query_template .

2. Cree la guía de plan de ejecución a partir de la forma parametrizada de la consulta y especifique la sugerencia de consulta PARAMETERIZATION FORCED.

   Importante

   Como parte de la parametrización de una consulta, SQL Server asigna un tipo de datos a los parámetros que sustituyen a los valores literales, en función del valor y el tamaño del literal. El mismo proceso se aplica al valor de los literales constantes que se pasan al parámetro de salida @stmt de sp_get_query_template. Dado que el tipo de datos especificado en el @params argumento de sp_create_plan_guide debe coincidir con el de la consulta tal como está parametrizado por SQL Server, es posible que tenga que crear más de una guía de plan para cubrir el intervalo completo de posibles valores de parámetro para la consulta.

El siguiente script puede utilizarse para obtener la consulta con parámetros y, a continuación, crear una guía de plan en ella.

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE sp_get_query_template
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total
      FROM Production.ProductModel AS pm
      INNER JOIN Production.ProductInventory AS 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;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
@stmt, N'TEMPLATE', NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)';

Si la parametrización forzada ya está habilitada en la base de datos, puede invalidarla para consultas específicas. Para parametrizar la consulta de ejemplo y las consultas sintácticasmente equivalentes según las reglas de parametrización simples, especifique PARAMETERIZATION SIMPLE en lugar de PARAMETERIZATION FORCED en la OPTION cláusula .

Contenido relacionado

• Guía de arquitectura de procesamiento de consultas
• sp_create_plan_guide (Transact-SQL)