Condividi tramite

  • Articolo

Utilizzo dell'hint di tabella FORCESEEK

L'hint FORCESEEK forza l'Optimizer ad utilizzare soltanto un'operazione Index Seek per accedere ai dati contenuti nella tabella o nella vista a cui si fa riferimento nella query. È possibile utilizzare tale hint di tabella per eseguire l'override del piano predefinito scelto dal Query Optimizer per evitare problemi di prestazioni causati da un piano di query inefficiente. Ad esempio, se un piano contiene operatori Table o Index Scan, e le tabelle corrispondenti provocano un numero elevato di letture durante l'esecuzione della query, come osservato nell’output STATISTICS IO, forzare un'operazione di Index Seek può produrre migliori prestazioni di esecuzione della query. Ciò è vero soprattutto quando cardinalità imprecisa o stime del costo fanno sì che l'ottimizzatore favorisca operazioni Scan in fase di compilazione del piano.

FORCESEEK si applica alle operazioni Clustered Index Seek e Nonclustered Index Seek. Può essere specificato per qualsiasi tabella o vista nella clausola FROM di un'istruzione SELECT e nella clausola FROM <table_source> di un'istruzione UPDATE o DELETE.

Nota di attenzioneAttenzione

Poiché il Query Optimizer di SQL Server in genere seleziona il piano di esecuzione migliore per una query, è consigliabile utilizzare hint solo se strettamente necessario e sempre da parte di sviluppatori e amministratori esperti di database.

Valutazione di piani di query per l'applicabilità di FORCESEEK

L’hint di tabella FORCESEEK potrebbe essere utile quando il piano di query utilizza un operatore Table o Index Scan su una tabella o vista ma un operatore Index Seek potrebbe essere più efficiente. Considerare la query seguente e il piano di esecuzione successivo.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

Nel piano di esecuzione seguente si osserva che il Query Optimizer ha scelto un operatore Clustered Index Seek per accedere ai dati contenuti in entrambi tabelle.

Piano di esecuzione con operatori Clustered Index Scan

È possibile forzare il Query Optimizer ad eseguire un'operazione di ricerca sulla tabella Sales.SalesOrderDetail specificando l’hint FORCESEEK come mostrato nella query seguente.

USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

Nel piano di esecuzione seguente vengono illustrati i risultati dell'utilizzo dell’hint FORCESEEK nella query. Per accedere ai dati contenuti nella tabella Sales.SalesOrderDetail viene utilizzata un’operazione Clustered Index Seek.

Piano di esecuzione con operatori Clustered Index Seek

Supporto unione e intersezione di indici

L’hint FORCESEEK supporta unioni e intersezioni di indici. Con tale hint è più probabile che il Query Optimizer utilizzi queste tecniche. Per evitare di rallentare la fase di compilazione di semplici query, le unioni e le intersezioni di indici di norma sono scelte solo secondo regole che tengono in considerazione la cardinalità e la selettività delle colonne. Ad ogni modo, quando viene specificato l’hint FORCESEEK tali regole vengono ignorate e queste tecniche vengono sempre considerate. Si consideri ad esempio la query seguente:

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 AND T.b = 2;

Se vi sono indici separati, non cluster, sulle colonne a e b della tabella T, può essere scelto un piano di intersezione degli indici. Ciò vuol dire che il piano contiene un'operazione Nonclustered Index Seek sulla colonna a ed un'operazione Nonclustered Index Seek sulla colonna b ed interseca i set di chiavi di indice risultanti prima di eseguire un'operazione di ricerca nella tabella di base.

Nell'esempio seguente viene scelto un piano di unione degli indici. Ciò vuol dire che il piano contiene un'operazione Seek sulla colonna a ed un'operazione Seek sulla colonna b e unisce i set di chiavi di indice risultanti prima di eseguire un'operazione di ricerca nella tabella di base.

SELECT * FROM T WITH(FORCESEEK) WHERE T.a = 1 OR T.b = 2;

Utilizzo di FORCESEEK in query che utilizzano LIKE o IN

Regole del Query Optimizer e stima della cardinalità insufficiente possono fare sì anche che l'Optimizer esegua un'operazione Table o Index Scan piuttosto che un Index Seek quando una query utilizza IN o LIKE come predicati di ricerca.

L'esempio seguente dimostra come l'hint FORCESEEK può forzare il Query Optimizer ad eseguire un'operazione Index Seek piuttosto che un Table Scan quando vengono utilizzati LIKE o IN come predicati di ricerca. Per visualizzare i piani di esecuzione delle query, fare clic sul pulsante della barra degli strumenti Include Actual Execution Plan prima di eseguire l'esempio.

USE tempdb;
GO
DROP TABLE t;
GO
CREATE TABLE t(i int UNIQUE, j int, vc varchar(100));
CREATE INDEX t_vc ON t(vc);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
DECLARE @p1 int, @p2 int, @p3 int, @p4 int, @p5 int;
SELECT * FROM t WITH (FORCESEEK) WHERE i IN (@p1, @p2, @p3, @p4, @p5);
GO
SELECT * FROM t WHERE vc LIKE 'Test%';
GO
SELECT * FROM t WITH (FORCESEEK) WHERE vc LIKE 'Test%';
GO
DECLARE @vc varchar(100);
SELECT * FROM t WHERE vc LIKE @vc;
GO
DECLARE @vc varchar(100);
SELECT * FROM t WITH (FORCESEEK) where vc like @vc;
GO

Utilizzo di FORCESEEK su Viste

FORCESEEK può essere specificato con o senza un hint di indice. Quando si applica un hint di tabella di FORCESEEK a una vista o a una vista indicizzata, l’hint FORCESEEK viene propagato in modo ricorsivo su tutte le tabelle nella versione espansa della vista. L’hint di indice, se specificato, viene ignorato. Se le tabelle sottostanti non hanno almeno un indice ciascuna, non viene trovato alcun piano e viene restituito l'errore 8622.

Quando si utilizzano insieme gli hint FORCESEEK e NOEXPAND su un riferimento ad una vista indicizzata, la vista indicizzata viene utilizzata senza che prima venga espansa. L’hint FORCESEEK viene applicato direttamente alla vista indicizzata, trattata semplicemente come una tabella.

Se si applica un hint FORCESEEK ad un riferimento della tabella, il riferimento della tabella non partecipare alla corrispondenza della vista indicizzata. Ad ogni modo, le altre parti della query che non sono influenzate dall’hint FORCESEEK possono partecipare alla corrispondenza della vista indicizzata. Ciò è paragonabile al comportamento della corrispondenza della vista indicizzata quando utilizzato con gli hint INDEX.

Considerazioni sulle procedure consigliate

Si consiglia di ricorrere alle seguenti procedure:

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

    Le statistiche aggiornate consentono all'utilità di ottimizzazione di valutare correttamente il costo di vari piani di query e di sceglierne uno di elevata qualità. 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.

  • Analizzare la query per individuare elementi che possono provocare stime di cardinalità o costo insufficienti e rimuovere tali elementi, se possibile. Ad esempio, sostituire le variabili locali con parametri o valori letterali e limitare l'utilizzo di funzioni valutate a livello di tabella con istruzioni multiple e di variabili di tabella nella query. Per ulteriori informazioni su altri elementi da cercare vedere Statistiche utilizzate da Query Optimizer in Microsoft SQL Server 2005.

  • Non utilizzare l’hint INDEX in combinazione con FORCESEEK se non necessario. Ovvero, se FORCESEEK produce da solo un piano sufficiente, utilizzare anche l’hint INDEX può limitare eccessivamente le scelte dell'Optimizer. 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.