Condividi tramite

  • Articolo

Progettazione di guide di piano per le query parametrizzate

È possibile creare una guida di piano su una query parametrizzata. Una query può essere parametrizzata per le ragioni seguenti:

  • La query viene inviata tramite sp_executesql.

  • La parametrizzazione forzata è attivata nel database. In questo caso vengono parametrizzate tutte le query idonee.

  • La parametrizzazione è specificata in una guida di piano creata su una classe di query a cui appartiene la query.

Quando si crea una guida di piano su una query parametrizzata, si crea essenzialmente una guida di piano per tutte le query che vengono parametrizzate nello stesso modo, ma differiscono solo per i rispettivi valori letterali costanti. In un database in cui è attivata l'opzione per la parametrizzazione forzata, ad esempio, le due query seguenti avranno la stessa forma parametrizzata:

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;

Per creare una guida di piano su query parametrizzate, creare una guida di piano di tipo SQL e specificare la forma parametrizzata della query nella stored procedure sp_create_plan_guide.

Per ottenere ad esempio la forma parametrizzata di una delle query dell'esempio precedente e creare su di essa una guida di piano, in modo da imporre a Query Optimizer l'utilizzo di un hash join, eseguire la procedura seguente:

  1. Ottenere la forma parametrizzata della query eseguendo sp_get_query_template.

  2. Se la query non è già stata parametrizzata da SQL Server tramite sp_executesql o mediante l'impostazione dell'opzione di database PARAMETERIZATION su FORCED, creare una guida di piano di tipo TEMPLATE per imporre la parametrizzazione.

  3. Creare una guida di piano di tipo SQL sulla query parametrizzata.

Il batch seguente esegue tutte e tre le operazioni:

--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 guida di piano verrà ora applicata a tutte le query che hanno la stessa forma parametrizzata, ma contengono valori letterali costanti diversi.