Specificare il comportamento di parametrizzazione delle query usando le guide di piano

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Quando si imposta l'opzione PARAMETERIZATION di database su SIMPLE, è possibile che query Optimizer SQL Server scelga di parametrizzare le query. Questa parametrizzazione sostituisce tutti i valori letterali in una query con i parametri. Tale processo viene chiamato parametrizzazione semplice. Quando SIMPLE la parametrizzazione è attiva, non è possibile controllare quali query sono parametrizzate e quali query non sono. Tuttavia, è possibile specificare che tutte le query in un database vengano parametrizzate impostando l'opzione PARAMETERIZATION di database su FORCED. Tale processo viene chiamato parametrizzazione forzata.

È possibile modificare il comportamento di parametrizzazione di un database utilizzando le guide del piano nei modi seguenti:

Si consideri la query seguente sul database 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;

In qualità di amministratore del database, si determina che non si vuole abilitare la parametrizzazione forzata in tutte le query nel database. Tuttavia, si vuole evitare costi di compilazione per tutte le query sintatticamente equivalenti alla query precedente, ma differiscono solo nei valori letterali costanti. In altre parole, desidera che la query venga parametrizzata in modo da riutilizzare un piano di esecuzione per questo tipo di query. In tal caso, è necessario completare i passaggi seguenti:

1. Recuperare il formato parametrizzato della query. L'unico modo sicuro per ottenere questo valore da utilizzare in sp_create_plan_guide consiste nell'usare la stored procedure di sistema sp_get_query_template .

2. Creare la guida del piano nella forma parametrizzata della query, specificando l'hint di query PARAMETERIZATION FORCED.

   Importante

   Nell'ambito della parametrizzazione di una query, SQL Server assegna un tipo di dati ai parametri che sostituiscono i valori letterali, in base al valore e alle dimensioni del valore letterale specifico. Lo stesso processo si verifica per i valori letterali costanti passati al parametro di output @stmt di sp_get_query_template. Poiché il tipo di dati specificato nell'@paramsargomento di sp_create_plan_guide deve corrispondere a quello della query come parametrizzato da SQL Server, potrebbe essere necessario creare più guide di piano per coprire l'intervallo completo dei valori dei parametri possibili per la query.

Lo script seguente può essere utilizzato sia per ottenere la query con parametri che per creare in seguito una guida di piano basata su tale query.

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

Se la parametrizzazione forzata è già abilitata nel database, è possibile eseguirne l'override per query specifiche. Per parametrizzare la query di esempio e le query sintatticamente equivalenti in base alle semplici regole di parametrizzazione, specificare PARAMETERIZATION SIMPLE anziché PARAMETERIZATION FORCED nella clausola OPTION.

Contenuti correlati

• Guida sull'architettura di elaborazione delle query
• sp_create_plan_guide (Transact-SQL)