Definizione delle funzionalità di parametrizzazione delle query tramite guide di pianoSpecify Query Parameterization Behavior by Using Plan Guides

Quando l'opzione di database PARAMETERIZATION è impostata su SIMPLE, Query Optimizer di SQL ServerSQL Server può scegliere di parametrizzare le query,When the PARAMETERIZATION database option is set to SIMPLE, the SQL ServerSQL Server query optimizer may choose to parameterize the queries. ovvero di sostituire con parametri i valori letterali contenuti in una query.This means that any literal values that are contained in a query are substituted with parameters. Tale processo viene chiamato parametrizzazione semplice.This process is referred to as simple parameterization. Quando è attiva una parametrizzazione di tipo SIMPLE, non è possibile distinguere le query con parametri da quelle senza parametri.When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not. È tuttavia possibile specificare che devono essere parametrizzate tutte le query di un database impostando l'opzione di database PARAMETERIZATION su FORCED.However, you can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. Tale processo viene chiamato parametrizzazione forzata.This process is referred to as forced parameterization.

È possibile sostituire le funzionalità di parametrizzazione di un database nei modi seguenti:You can override the parameterization behavior of a database by using plan guides in the following ways:

  • Quando l'opzione di database PARAMETERIZATION è impostata su SIMPLE, è possibile specificare che la parametrizzazione forzata venga tentata su una determinata classe di query.When the PARAMETERIZATION database option is set to SIMPLE, you can specify that forced parameterization is attempted on a certain class of queries. A tale scopo, è necessario creare una guida di piano TEMPLATE nel formato con parametri della query e specificare l'hint per la query PARAMETERIZATION FORCED nella stored procedure sp_create_plan_guide .You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. È 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.You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries.

  • 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.When the PARAMETERIZATION database option is set to FORCED, you can specify that for a certain class of queries, only simple parameterization is attempted, not forced parameterization. A tale scopo, è necessario creare una guida di piano TEMPLATE nel formato di query force-parametrized e specificare l'hint per la query PARAMETERIZATION SIMPLE nella stored procedure sp_create_plan_guide.You do this by creating a TEMPLATE plan guide on the force-parameterized form of the query, and specifying the PARAMETERIZATION SIMPLE query hint in sp_create_plan_guide.

    Si consideri la query seguente sul database AdventureWorks2012AdventureWorks2012 :Consider the following query on the AdventureWorks2012AdventureWorks2012 database:

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;  

L'amministratore di database ha deciso di non abilitare la parametrizzazione forzata su tutte le query del database.As a database administrator, you have determined that you do not want to enable forced parameterization on all queries in the database. Tuttavia, l'amministratore desidera evitare i costi di compilazione relativi a tutte le query sintatticamente equivalenti alla query precedente ma che si differenziano solo per i relativi valori letterali costanti.However, you do want to avoid compilation costs on all queries that are syntactically equivalent to the previous query, but differ only in their constant literal values. In altre parole, desidera che la query venga parametrizzata in modo da riutilizzare un piano per questo tipo di query.In other words, you want the query to be parameterized so that a query plan for this kind of query is reused. In tal caso, è necessario completare i passaggi seguenti:In this case, complete the following steps:

  1. Recuperare il formato con parametri della query.Retrieve the parameterized form of the 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 .The only safe way to obtain this value for use in sp_create_plan_guide is by using the sp_get_query_template system stored procedure.

  2. Creare la guida di piano nel formato con parametri della query, specificando l'hint per la query PARAMETERIZATION FORCED.Create the plan guide on the parameterized form of the query, specifying the PARAMETERIZATION FORCED query hint.

    Importante

    Nell'ambito della parametrizzazione di una query, SQL ServerSQL Server assegna un tipo di dati ai parametri che sostituiscono i valori letterali, in base al valore e alle dimensioni del valore letterale specifico.As part of parameterizing a query, SQL ServerSQL Server assigns a data type to the parameters that replace the literal values, depending on the value and size of the literal. Lo stesso processo viene eseguito per i valori letterali costanti passati al parametro di output @stmt della stored procedure sp_get_query_template.The same process occurs to the value of the constant literals passed to the @stmt output parameter of 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 ServerSQL Server, potrebbe essere necessario creare più guide di piano per coprire l'intervallo completo di valori di parametro possibili per la query.Because the data type specified in the @params argument of sp_create_plan_guide must match that of the query as it is parameterized by SQL ServerSQL Server, you may have to create more than one plan guide to cover the complete range of possible parameter values for the 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.The following script can be used both to obtain the parameterized query and then create a plan guide on it:

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.Similarly, in a database in which forced parameterization is already enabled, you can make sure that the sample query, and others that are syntactically equivalent, except for their constant literal values, are parameterized according to the rules of simple parameterization. A tale scopo, nella clausola OPTION è necessario specificare PARAMETERIZATION SIMPLE anziché PARAMETERIZATION FORCED.To do this, specify PARAMETERIZATION SIMPLE instead of PARAMETERIZATION FORCED in the OPTION clause.

Nota

Le guide di piano TEMPLATE definiscono la corrispondenza tra le istruzioni e le query inviate in batch costituite da una singola istruzione.TEMPLATE plan guides match statements to queries submitted in batches that consist of a single statement only. Le istruzioni all'interno di batch costituiti da più istruzioni non sono idonee per la corrispondenza definita dalle guide di piano TEMPLATE.Statements inside multistatement batches are not eligible to be matched by TEMPLATE plan guides.