Condividi tramite

Specificare il comportamento di parametrizzazione delle query tramite guide di piano

  • Articolo

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

Quando l'opzione di database PARAMETERIZATION è impostata su SIMPLE, Query Optimizer di SQL Server può scegliere di parametrizzare le query, ovvero di sostituire con parametri i valori letterali contenuti in una query. Tale processo viene chiamato parametrizzazione semplice. Quando è attiva una parametrizzazione di tipo SIMPLE, non è possibile distinguere le query con parametri da quelle senza parametri. È tuttavia possibile specificare che devono essere parametrizzate tutte le query di un database impostando l'opzione di database PARAMETERIZATION 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:

  • Quando l'opzione di database PARAMETERIZATION è impostata su SIMPLE, è possibile specificare che la parametrizzazione forzata venga tentata su una determinata classe di query. Per fare ciò, devi creare una guida di piano TEMPLATE nella forma parametrizzata della query e specificare l'hint di query PARAMETERIZATION FORCED nella stored procedure sp_create_plan_guide. È possibile considerare questo tipo di guida di piano come uno strumento per abilitare la parametrizzazione forzata solo su una determinata classe di query, anziché su tutte le query. Per altre informazioni sulla parametrizzazione semplice, vedere Guida sull'architettura di elaborazione delle query.

  • Quando l'opzione di database PARAMETERIZATION è impostata su FORCED, è possibile specificare che venga tentata solo la parametrizzazione semplice, anziché forzata, su una determinata classe di query. Per fare ciò, è necessario creare una guida di piano TEMPLATE nella forma di query parametrizzata forzatamente e specificare l'hint di query PARAMETERIZATION SIMPLE in sp_create_plan_guide. Per altre informazioni sulla parametrizzazione forzata, vedere Guida sull'architettura di elaborazione delle query.

Si consideri la query seguente sul database AdventureWorks2022 :

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 di database, hai stabilito che non vuoi abilitare la parametrizzazione forzata su tutte le query del database. Tuttavia, si desidera evitare i costi di compilazione su tutte le query sintatticamente equivalenti alla query precedente, ma che si differenziano solo per i loro 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 metodo affidabile per ottenere questo valore da usare in sp_create_plan_guide consiste nell'uso della stored procedure di sistema sp_get_query_template .

  2. Crea la guida al 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 viene eseguito per i valori letterali costanti passati al parametro di output @stmt della stored procedure sp_get_query_template. Poiché il tipo di dati specificato nell'argomento @params di sp_create_plan_guide deve corrispondere a quello della query con i parametri di SQL Server, potrebbe essere necessario creare più guide di piano per coprire l'intervallo completo di valori di parametro 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 nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC 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;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';

Analogamente, in un database in cui è già abilitata la parametrizzazione forzata è possibile verificare che la query di esempio e le altre sintatticamente equivalenti, tranne per i relativi valori letterali costanti, siano con parametri in conformità alle regole della parametrizzazione semplice. Specificare PARAMETERIZATION SIMPLE invece di PARAMETERIZATION FORCED nella clausola OPTION per conseguire tale scopo.

Nota

Le guide del piano TEMPLATE associano le istruzioni alle query inviate in batch che contengono una sola istruzione. Le istruzioni all'interno di batch costituiti da più istruzioni non sono idonee per la corrispondenza definita dalle guide di piano TEMPLATE.