Utilizzo degli hint per le query INDEX e FORCESEEK nelle guide di piano

Procedure consigliate

Si consiglia di ricorrere alle seguenti procedure:

• Utilizzare gli hint INDEX e FORCESEEK come hint per query soltanto nel contesto di una guida di piano o in query ad hoc quando si testano istruzioni della guida di piano. Per tutte le altre query ad hoc specificare tali hint come hint di tabella.

• Prima di utilizzare l’hint FORCESEEK, verificare che le statistiche sul database siano aggiornate ed accurate.

  Le statistiche aggiornate consentono all’ottimizzatore di valutare correttamente il costo di piani di query differenti e di sceglierne uno di qualità elevata. Pertanto si consiglia di impostare AUTO_CREATE_STATISTICS ed AUTO_UPDATE_STATISTICS su ON (impostazione predefinita) per ogni database utente. In alternativa, è possibile aggiornare manualmente le statistiche su una tabella o vista utilizzando l'istruzione UPDATE STATISTICS.

• Non utilizzare l'hint INDEX in combinazione con FORCESEEK se non necessario. Ovvero, se FORCESEEK produce da solo un piano adatto, utilizzare anche l'hint INDEX può limitare eccessivamente le scelte dell'ottimizzatore. Inoltre, un hint INDEX farà sì che la query abbia esito negativo se si modifica lo schema fisico della tabella per eliminare l'indice specificato nell’hint. Finché esiste almeno un indice utilizzabile nella tabella alla quale è applicato l'hint FORCESEEK, la query verrà invece compilata anche se si modificano le strutture dell'indice.

• Non utilizzare l'hint INDEX (0) in combinazione con FORCESEEK. INDEX (0) impone una scansione della tabella di base. Se utilizzato con FORCESEEK, non viene trovato alcun piano e viene restituito l'errore 8622.

• Non utilizzare l’hint per la query USE PLAN in combinazione con FORCESEEK. Se utilizzato, l’hint FORCESEEK viene ignorato.

Utilizzo degli hint INDEX e FORCESEEK con altri hint di tabella

Gli hint INDEX e FORCESEEK possono essere specificati per una query senza hint di tabella esistenti oppure essere utilizzati per sostituire uno o più hint INDEX o FORCESEEK esistenti nella query. Se una query che corrisponde a una guida di piano contiene già una clausola WITH che specifica hint di tabella, gli hint specificati nel parametro @hints della guida di piano sostituiscono quelli contenuti nella query. Se ad esempio la query contiene l'hint di tabella WITH INDEX (PK_Employee_EmployeeID) per la tabella HumanResources.Employee e il parametro @hints nella guida di piano specifica OPTION (TABLE HINT ( HumanResources.Employee, INDEX( IX_Employee_ManagerID ) ), Query Optimizer utilizzerà l'indice IX_Employee_ManagerID.

Gli hint di tabella diversi da INDEX e FORCESEEK non sono consentiti come hint per la query nella guida di piano a meno che la query non disponga già di una clausola WITH che specifica l'hint di tabella. In questo caso è necessario specificare inoltre un hint corrispondente come un hint per la query utilizzando TABLE HINT nella clausola OPTION per mantenere la semantica della query. Se la query contiene ad esempio l'hint di tabella NOLOCK, anche il parametro @hints della guida di piano deve contenere l'hint NOLOCK oltre a un hint di tabella INDEX o FORCESEEK nell'opzione OPTION. Vedere l'esempio C di seguito in questo argomento. Quando un hint di tabella diverso da INDEX o FORCESEEK viene specificato utilizzando TABLE HINT nella clausola OPTION senza un hint per la query corrispondente, o viceversa, viene generato l'errore 8702, che indica che la clausola OPTION può provocare la modifica della query cambi e l'esito negativo della query.

Utilizzo degli hint INDEX e FORCESEEK con altri hint per query

Se una query che corrisponde a una guida di piano contiene già una clausola OPTION che specifica hint per la query, gli hint per la query specificati nel parametro @hints della guida di piano sostituiscono quelli contenuti nella query. Perché una guida di piano possa corrispondere a una query che già contiene una clausola OPTION è tuttavia necessario includere la clausola OPTION della query quando si specifica il testo della query da far corrispondere nell'istruzione sp_create_plan_guide (Transact-SQL). Se si desidera che gli hint specificati nella guida di piano vengano aggiunti agli hint già esistenti nella query, invece di sostituirli è necessario specificare sia gli hint originali che gli hint aggiuntivi nella clausola OPTION della guida di piano.

Esempi

A. 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

B. 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

C. Specifica di hint 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

D. 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

Vedere anche

Riferimento

Concetti