Guide di piano

Le guide di piano consentono di ottimizzare le prestazioni delle query quando non è possibile o non si desidera modificare direttamente il testo della query corrente in SQL Server 2012. Le guide di piano influiscono sull'ottimizzazione delle query mediante l'aggiunta di hint per la query o di un piano di query fisso. Le guide di piano risultano utili quando le prestazioni di un piccolo subset di query eseguite su un database di terze parti sono inferiori a quelle previste. In una guida di piano, viene specificata l'istruzione Transact-SQL da ottimizzare e la clausola OPTION che contiene gli hint per la query da utilizzare o un piano di query specifico da utilizzare per ottimizzare la query. Quando viene eseguita la query, SQL Server associa l'istruzione Transact-SQL alla guida di piano e, in fase di esecuzione, associa la clausola OPTION alla query oppure utilizza il piano di query specificato.

Il numero totale di guide di piano che è possibile creare è limitato solo dalle risorse di sistema disponibili. È comunque consigliabile utilizzare le guide di piano per le sole query critiche di cui si desidera migliorare o stabilizzare le prestazioni. Le guide di piano non vanno utilizzate per modificare la maggior parte del carico di query di un'applicazione distribuita.

[!NOTA]

Non è possibile utilizzare le guide di piano in ogni edizione di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2012. Le guide di piano sono visibili in qualsiasi edizione. È inoltre possibile collegare un database che contiene guide di piano a qualsiasi edizione. Quando si ripristina o si collega un database a una versione aggiornata di SQL Server, le guide di piano non vengono modificate.

Tipi di guide di piano

È possibile creare i seguenti tipi di guide di piano:

• Guida di piano di tipo OBJECT
  Una guida di piano di tipo OBJECT corrisponde alle query eseguite nel contesto di stored procedure Transact-SQL, funzioni scalari definite dall'utente, funzioni con valori di tabella definite dall'utente con istruzioni multiple e trigger DML.

  Si supponga che la seguente stored procedure, che accetta il parametro @Country\_region, si trovi in un'applicazione di database distribuita sul database AdventureWorks2012 :

  CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
  AS
  BEGIN
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, 
          Sales.SalesTerritory AS t
      WHERE h.CustomerID = c.CustomerID
          AND c.TerritoryID = t.TerritoryID
          AND CountryRegionCode = @Country_region
  END;
  

  Questa stored procedure è stata compilata e ottimizzata per @Country\_region = N'AU' (Australia). Tuttavia, poiché sono presenti relativamente pochi ordini di vendita con origine in Australia, le prestazioni diminuiscono quando viene eseguita la query utilizzando i valori del parametro dei paesi con più ordini di vendita. Poiché il paese da cui proviene la maggior parte degli ordini di vendita sono gli Stati Uniti, un piano di query generato per @Country\_region = N'US' offrirebbe probabilmente prestazioni migliori per tutti i possibili valori del parametro @Country\_region.

  Per risolvere il problema è possibile modificare la stored procedure aggiungendo alla query l'hint OPTIMIZE FOR. Poiché la stored procedure si trova in un'applicazione distribuita, non è possibile modificare direttamente il codice dell'applicazione. È invece possibile creare la guida di piano seguente nel database AdventureWorks2012 .

  sp_create_plan_guide 
  @name = N'Guide1',
  @stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
          Sales.Customer AS c,
          Sales.SalesTerritory AS t
          WHERE h.CustomerID = c.CustomerID 
              AND c.TerritoryID = t.TerritoryID
              AND CountryRegionCode = @Country_region',
  @type = N'OBJECT',
  @module_or_batch = N'Sales.GetSalesOrderByCountry',
  @params = NULL,
  @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
  

  Al momento dell'esecuzione della query specificata nell'istruzione sp_create_plan_guide, la query viene modificata prima dell'ottimizzazione per includere la clausola OPTIMIZE FOR (@Country = N''US'').

• Guida di piano di tipo SQL
  Una guida di piano di tipo SQL corrisponde alle query eseguite nel contesto di batch e istruzioni Transact-SQL autonome che non fanno parte di un oggetto di database. Le guide di piano basate su SQL possono inoltre essere utilizzate per query con parametrizzazioni specifiche. Le guide di piano di tipo SQL vengono applicate a istruzioni Transact-SQL autonome e batch. Spesso tali istruzioni vengono inoltrate da un'applicazione utilizzando la stored procedure di sistema sp_executesql. Ad esempio, si consideri il batch autonomo seguente:

  SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;
  

  Per impedire la generazione di un piano di esecuzione parallelo su questa query, creare la seguente guida di piano e impostare l'hint per la query MAXDOP su 1 nel parametro @hints.

  sp_create_plan_guide 
  @name = N'Guide2', 
  @stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',
  @type = N'SQL',
  @module_or_batch = NULL, 
  @params = NULL, 
  @hints = N'OPTION (MAXDOP 1)';
  

  Importante
  I valori specificati per gli argomenti @module_or_batch e @params dell'istruzione sp_create_plan guide devono corrispondere al testo specificato nella query effettiva. Per ulteriori informazioni, vedere sp_create_plan_guide (Transact-SQL) e Utilizzo di SQL Server Profiler per creare e testare guide di piano.

  È possibile creare guide di piano SQL anche per le query con parametrizzazione forzata quando l'opzione di database PARAMETERIZATION è impostata su FORCED, oppure quando si crea una guida di piano di tipo TEMPLATE per specificare la parametrizzazione di una classe di query.

• TEMPLATE - guida di piano
  Una guida di piano di tipo TEMPLATE corrisponde alle query autonome con parametrizzazioni specifiche. Tali guide di piano vengono utilizzate per sostituire l'opzione SET di database PARAMETERIZATION di un database per una classe di query.

  È possibile creare una guida di piano di tipo TEMPLATE nelle seguenti situazioni:

  • L'opzione di database PARAMETERIZATION è impostata su FORCED, ma si desidera compilare alcune query in base alle regole della parametrizzazione semplice.

  • L'opzione di database PARAMETERIZATION è impostata su SIMPLE (impostazione predefinita), ma si desidera che una classe di query venga sottoposta a parametrizzazione forzata.

Requisiti di corrispondenza per la guida di piano

Le guide di piano sono definite a livello di ambito del database in cui vengono create. Pertanto, è possibile far corrispondere alla query solo le guide di piano presenti nel database corrente al momento dell'esecuzione della query. Ad esempio, se AdventureWorks2012 è il database corrente e viene eseguita la query seguente:

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano nel database AdventureWorks2012 . Se tuttavia il database corrente è AdventureWorks2012 e vengono eseguite le istruzioni seguenti:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

È possibile far corrispondere alla query solo le guide di piano in DB1, poiché la query è in esecuzione nel contesto di DB1.

Per guide di piano basate su SQL o TEMPLATE, SQL Server esegue la corrispondenza tra i valori per gli argomenti @module\_or\_batch e @params e una query, confrontando i due valori carattere per carattere. Per questo motivo è necessario immettere il testo esattamente come SQL Server lo riceve nel batch.

Se @type = 'SQL' e @module\_or\_batch sono impostate su NULL, il valore di @module\_or\_batch è impostato sul valore di @stmt. Di conseguenza, il valore per statement_text deve essere specificato nello stesso formato, carattere per carattere, così come viene inviato a SQL Server. Per semplificare questa corrispondenza, non viene eseguita alcuna conversione interna.

Quando è possibile applicare sia una guida di piano normale (SQL o OBJECT) sia una guida di piano TEMPLATE a un'istruzione, verrà utilizzata solo la guida di piano normale.

[!NOTA]

Il batch contenente l'istruzione sulla quale si desidera creare una guida di piano non può contenere un'istruzione USE database.

Effetto delle guide di piano sulla cache dei piani

La creazione di una guida di piano su un modulo rimuove il piano di query per il dato modulo dalla cache dei piani. La creazione di una guida di piano di tipo OBJECT o SQL su un batch rimuove il piano di query per un batch con lo stesso valore hash. La creazione di una guida di piano di tipo TEMPLATE rimuove tutti i batch a istruzione singola dalla cache dei piani all'interno del database.

Attività correlate

Vedere anche

Riferimento

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)