Hint per la query (Transact-SQL)

Sintassi

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'| TABLE HINT ( exposed_object_name  [ , <table_hint> [ [, ]...n ] ] )<table_hint> ::=
[ NOEXPAND ] { 
    INDEX (index_value [ ,...n ] ) | INDEX = (index_value)
  | FASTFIRSTROW 
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Argomenti

• { HASH | ORDER } GROUP
  Specifica che nelle funzioni di aggregazione elencate nella clausola GROUP BY, DISTINCT o COMPUTE della query devono essere utilizzate operazioni di hashing o di ordinamento.

• { MERGE | HASH | CONCAT } UNION
  Specifica che per tutte le operazioni UNION vengono eseguite operazioni di unione, hashing o concatenazione dei set UNION. Se viene specificato più di un hint UNION, Query Optimizer seleziona la strategia meno onerosa tra gli hint specificati.

• { LOOP | MERGE | HASH } JOIN
  Specifica che tutte le operazioni JOIN vengono eseguite da LOOP JOIN, MERGE JOIN o HASH JOIN nell'intera query. Se vengono specificati più hint di join, Query Optimizer seleziona la strategia di join meno onerosa tra i join consentiti.

  Se nella stessa query un hint di join viene specificato nella clausola FROM anche per una particolare coppia di tabelle, tale hint risulta prioritario rispetto all'unione in join delle due tabelle. Gli hint per la query devono essere comunque rispettati. L'hint di join della coppia di tabelle pertanto consente di limitare solo la selezione dei metodi di join consentiti nell'hint per la query. Per ulteriori informazioni, vedere Hint di join (Transact-SQL).

• FAST number_rows
  Specifica che la query è ottimizzata per il recupero rapido delle prime righe specificate in number_rows. Si tratta di un numero intero non negativo. Dopo la restituzione del numero di righe definito da number_rows, l'esecuzione della query continua e viene generato il set di risultati completo.

• FORCE ORDER
  Specifica che l'ordine di join indicato dalla sintassi della query viene conservato durante l'ottimizzazione della query. L'utilizzo dell'hint FORCE ORDER non ha alcun effetto sulla possibile inversione dei ruoli in Query Optimizer. Per ulteriori informazioni, vedere Informazioni sugli hash join.

  [!NOTA]

  In un'istruzione MERGE, l'accesso alla tabella di origine viene eseguito prima della tabella di destinazione come ordine di join predefinito, a meno che non sia specificata la clausola WHEN SOURCE NOT MATCHED. Specificando FORCE ORDER viene conservato questo comportamento predefinito.

  Per informazioni su come Query Optimizer di SQL Server consente di applicare l'hint FORCE ORDER quando in una query è inclusa una vista, vedere Risoluzione delle viste.

• MAXDOP number
  Viene eseguito l'override dell'opzione di configurazione max degree of parallelism di sp_configure e di Resource Governor per la query che consente di specificare tale opzione. L'hint per la query MAXDOP può superare il valore configurato con sp_configure. Se MAXDOP supera il valore configurato con Resource Governor, nel Motore di database viene utilizzato il valore MAXDOP di Resource Governor, descritto in ALTER WORKLOAD GROUP (Transact-SQL). Quando si utilizza l'hint per la query MAXDOP sono valide tutte le regole semantiche utilizzate con l'opzione di configurazione max degree of parallelism. Per ulteriori informazioni, vedere Opzione max degree of parallelism.

  Attenzione
  L'impostazione di MAXDOP su zero consente al server di scegliere il massimo grado di parallelismo, ovvero internamente il valore DOP di 64.

• OPTIMIZE FOR ( @variable\_name { UNKNOWN | = literal_constant } [ , ...n ] )
  Consente a Query Optimizer di utilizzare un valore specifico per una variabile locale quando la query viene compilata e ottimizzata. Il valore viene utilizzato durante l'ottimizzazione della query e non durante la sua esecuzione.

  • @variable\_name
    Nome di una variabile locale utilizzata in una query alla quale è possibile assegnare un valore da utilizzare con l'hint per la query OPTIMIZE FOR.

  • UNKNOWN
    Specifica che Query Optimizer utilizza dati statistici anziché il valore iniziale per determinare il valore per una variabile locale durante l'ottimizzazione della query.

  • literal_constant
    Valore letterale costante da assegnare a @variable\_name per l'utilizzo con l'hint per la query OPTIMIZE FOR. literal_constant viene utilizzato solo durante l'ottimizzazione della query e non come valore di @variable\_name durante l'esecuzione della query. literal_constant può essere di un tipo di dati di sistema qualsiasi di SQL Server che sia possibile esprimere come costante letterale. Il tipo di dati di literal_constant deve supportare la conversione implicita nel tipo di dati a cui @variable\_name fa riferimento nella query.

  OPTIMIZE FOR può annullare la funzionalità di rilevamento predefinita dei parametri di Query Optimizer oppure può essere utilizzato durante la creazione delle guide di piano. Per ulteriori informazioni, vedere Ricompilazione di stored procedure e Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano.

• OPTIMIZE FOR UNKNOWN
  Indica a Query Optimizer di utilizzare dati statistici anziché i valori iniziali per tutte le variabili locali quando la query viene compilata e ottimizzata, includendo parametri creati con parametrizzazione forzata. Per ulteriori informazioni sulla parametrizzazione forzata, vedere Parametrizzazione forzata.

  Se OPTIMIZE FOR @variable\_name = literal_constant e OPTIMIZE FOR UNKNOWN sono utilizzati nello stesso hint per la query, Query Optimizer utilizzerà il valore literal_constant indicato per un valore specifico e UNKNOWN per i valori di variabile rimanenti. I valori vengono utilizzati durante l'ottimizzazione della query e non durante la sua esecuzione.

• PARAMETERIZATION { SIMPLE | FORCED }
  Specifica le regole di parametrizzazione applicate da Query Optimizer di SQL Server alla query durante la compilazione di questa.

  Importante
  L'hint per la query PARAMETERIZATION può essere specificata solo all'interno di una guida di piano e non direttamente all'interno di una query.

  SIMPLE indica a Query Optimizer di tentare la parametrizzazione semplice. FORCED indica a Query Optimizer di tentare la parametrizzazione forzata. L'hint per la query PARAMETERIZATION viene utilizzato per sostituire l'impostazione corrente dell'opzione SET dell'hint di database PARAMETERIZATION all'interno di una guida di piano. Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.

• RECOMPILE
  Indica a Motore di database di SQL Server di eliminare il piano generato per la query dopo che questa è stata eseguita e forza Query Optimizer a ricompilare un piano di query alla successiva esecuzione della stessa query. Se RECOMPILE non viene specificato, Motore di database inserisce i piani di query nella cache e li riutilizza. Durante la compilazione dei piani di query, l'hint per la query RECOMPILE utilizza i valori correnti delle variabili locali incluse nella query e, se la query è contenuta in una stored procedure, i valori correnti vengono passati ai parametri.

  RECOMPILE rappresenta una valida alternativa alla creazione di una stored procedure che utilizza la clausola WITH RECOMPILE quando è necessario ricompilare solo un subset di query all'interno della stored procedure, anziché l'intera stored procedure. Per ulteriori informazioni, vedere Ricompilazione di stored procedure. RECOMPILE risulta utile anche durante la creazione delle guide di piano. Per ulteriori informazioni, vedere Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano.

• ROBUST PLAN
  Impone in Query Optimizer l'applicazione di un piano che funziona anche con dimensioni di riga massime, eventualmente a scapito delle prestazioni. Quando la query viene elaborata, è possibile che tabelle e operatori intermedi debbano archiviare ed elaborare righe con dimensioni maggiori rispetto a qualsiasi riga di input. Talvolta le righe possono essere talmente estese che l'operatore specificato non è in grado di elaborarle. In questi casi Motore di database genera un errore durante l'esecuzione della query. Grazie alla clausola ROBUST PLAN i piani di query in cui potrebbe verificarsi questo problema vengono ignorati da Query Optimizer.

  Se non è possibile implementare tale piano, viene restituito un errore anziché posticipare il rilevamento dell'errore fino all'esecuzione della query. Le righe possono includere colonne di lunghezza variabile. In Motore di database è consentito definire righe con dimensioni massime superiori alla capacità di elaborazione di Motore di database. In un'applicazione tuttavia vengono in genere archiviate righe le cui dimensioni effettive rientrano nei limiti delle capacità di elaborazione di Motore di database. Se in Motore di database viene rilevata una riga di lunghezza eccessiva, viene restituito un errore di esecuzione.

• KEEP PLAN
  Impone in Query Optimizer l'impostazione di una soglia di ricompilazione stimata meno restrittiva per una query. La soglia di ricompilazione stimata è il punto in corrispondenza del quale una query viene automaticamente ricompilata quando in una tabella è stato apportato il numero stabilito di modifiche a livello di colonne indicizzate mediante l'esecuzione delle istruzioni UPDATE, DELETE, MERGE o INSERT. Specificando KEEP PLAN è possibile assicurarsi che una query non venga ricompilata troppo frequentemente in caso di aggiornamenti multipli di una tabella.

• KEEPFIXED PLAN
  Impedisce a Query Optimizer di ricompilare una query in seguito a modifiche alle statistiche. Se si specifica KEEPFIXED PLAN, una query viene ricompilata solo se lo schema delle tabelle sottostanti viene modificato oppure se in tali tabelle si esegue sp_recompile.

• EXPAND VIEWS
  Specifica che le viste indicizzate vengono espanse e che in Query Optimizer non viene presa in considerazione alcuna vista indicizzata per la sostituzione di una parte della query. Una vista viene espansa quando nel testo della query il nome della vista viene sostituito dalla definizione della vista stessa.

  Con questo hint per la query viene praticamente disabilitato l'utilizzo diretto di viste indicizzate e di relativi indici nel piano di query.

  La vista indicizzata non viene espansa solo se nella sezione SELECT della query viene fatto riferimento diretto alla vista e se viene specificato l'hint WITH (NOEXPAND) o WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ). Per ulteriori informazioni sull'hint per la query WITH (NOEXPAND), vedere FROM.

  L'hint influisce solo sulle viste nella sezione SELECT delle istruzioni, comprese le sezioni delle istruzioni INSERT, UPDATE, MERGE e DELETE.

• MAXRECURSION number
  Specifica il numero massimo di ricorsioni consentite per la query corrente. number è un numero intero non negativo compreso tra 0 e 32767. Se è specificato 0, non viene applicato alcun limite. Se questa opzione non viene specificata, il limite predefinito per il server è 100.

  Se durante l'esecuzione della query viene raggiunto il valore specificato o predefinito per il limite MAXRECURSION, la query viene terminata e viene restituito un errore.

  A causa di questo errore, verrà eseguito il rollback di tutti gli effetti dell'istruzione. Se l'istruzione è un'istruzione SELECT, è possibile che vengano restituiti risultati parziali oppure nessun risultato. È possibile che eventuali risultati parziali non includano tutte le righe nei livelli di ricorsione che superano il livello di ricorsione massimo specificato.

  Per ulteriori informazioni, vedere WITH common_table_expression (Transact-SQL).

• USE PLAN N**'xml_plan'**
  Impone in Query Optimizer l'utilizzo di un piano di query esistente per una query specificata da 'xml_plan'. Per ulteriori informazioni, vedere Definizione dei piani di query tramite l'utilizzo forzato. USE PLAN non può essere specificato nelle istruzioni INSERT, UPDATE, MERGE o DELETE.

• TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
  Applica l'hint di tabella specificato alla tabella o alla vista che corrisponde a exposed_object_name. È consigliabile utilizzare un hint di tabella come hint per la query solo nel contesto di una guida di piano.

  exposed_object_name può essere uno dei seguenti riferimenti:

  • Quando viene utilizzato un alias per la tabella o la vista nella clausola FROM della query, exposed_object_name è l'alias.

  • Quando non viene utilizzato un alias, exposed_object_name è la corrispondenza esatta della tabella o della vista cui si fa riferimento nella clausola FROM. Se, ad esempio, si fa riferimento alla tabella o alla vista utilizzando un nome in due parti, exposed_object_name è lo stesso nome in due parti.

  Quando viene specificato exposed_object_name senza specificare anche un hint di tabella, qualsiasi indice specificato nella query come parte di un hint di tabella per l'oggetto viene ignorato e l'utilizzo di indici è determinato da Query Optimizer. È possibile utilizzare questa tecnica per eliminare l'effetto di un hint di tabella INDEX quando non è possibile modificare la query originale. Vedere l'esempio J.

• <table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FASTFIRSTROW | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
  Hint di tabella da applicare alla tabella o alla vista corrispondente a exposed_object_name come hint per la query. Per una descrizione di questi hint, vedere Hint di tabella (Transact-SQL).

  Gli hint di tabella diversi da INDEX e FORCESEEK non sono consentiti come hint per la query a meno che la query non disponga già di una clausola WITH che specifica l'hint di tabella. Per ulteriori informazioni, vedere la sezione Osservazioni.

Osservazioni

Gli hint per la query non possono essere specificati in un'istruzione INSERT, eccetto quando una clausola SELECT viene utilizzata all'interno dell'istruzione.

È possibile specificare gli hint per la query solo nella query di livello principale e non nelle sottoquery. Quando un hint di tabella viene specificato come hint per la query, l'hint può essere specificato nella query di livello superiore o in una sottoquery, tuttavia il valore specificato per exposed_object_name nella clausola TABLE HINT deve corrispondere esattamente al nome esposto nella query o nella sottoquery.

Specifica di hint di tabella come hint per la query

Esempi

A. Utilizzo di MERGE JOIN

USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

B. Utilizzo di OPTIMIZE FOR

USE AdventureWorks;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

C. Utilizzo di MAXRECURSION

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

D. Utilizzo di MERGE UNION

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

E. Utilizzo di HASH GROUP e FAST

USE AdventureWorks;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

F. Utilizzo di MAXDOP

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

G. Utilizzo di INDEX

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

H. Utilizzo di FORCESEEK

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

I. Utilizzo di più hint di tabella

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

J. Utilizzo di TABLE HINT per eseguire l'override di un hint di tabella esistente

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

K. Specifica di hint di tabella che influiscono sulla semantica

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

Vedere anche

Riferimento

Concetti