Consigli per l'ottimizzazione delle query
Alcune query consumano più risorse di altre. Le query che restituiscono set di risultati estesi e quelle che contengono clausole WHERE non univoche utilizzano sempre molte risorse. Il costo di tali costrutti in termini di risorse non può essere ridotto in alcun modo dai meccanismi interni di Query Optimizer, in particolare se paragonato a quello ottenibile utilizzando una query meno complessa. SQL Server utilizza il piano di accesso ottimale, tuttavia le possibilità concrete di intervento di Query Optimizer sono limitate.
Per ottimizzare le prestazioni delle query, è comunque possibile adottare le misure seguenti:
Aggiungere memoria. Questa soluzione si rivela particolarmente utile se il server esegue molte query complesse e l'esecuzione di diverse query risulta lenta.
Utilizzare più processori. Più processori consentono l'esecuzione di query parallele in Motore di database. Per ulteriori informazioni, vedere Elaborazione parallela di query.
Riformulare la query. In tal caso, tenere in considerazione i problemi seguenti:
Se la query utilizza cursori, determinare se la query può essere scritta tramite un tipo di cursore più efficiente, ad esempio un cursore fast forward-only oppure tramite una query singola. In genere le query singole offrono prestazioni superiori a quelle di operazioni basate su cursori. Poiché un set di istruzioni di cursore è in genere un'operazione di ciclo esterno, in cui ogni riga del ciclo esterno viene elaborata una sola volta tramite un'istruzione interna, è consigliabile utilizzare un'istruzione GROUP BY o CASE o una sottoquery anziché il set di istruzioni di cursore. Per ulteriori informazioni, vedere Tipi di cursore (Motore di database) e Nozioni fondamentali sulle query.
Se un'applicazione utilizza un ciclo, può risultare utile inserirlo nella query. È frequente che un'applicazione includa un ciclo contenente a sua volta una query con parametri. Tale query viene eseguita più volte e richiede un round trip in rete tra il computer che esegue l'applicazione e SQL Server. È pertanto consigliabile sostituire tale struttura con una query unica, più complessa, che utilizza una tabella temporanea. In questo modo sarà sufficiente un unico round trip in rete e la query unica sarà più facile da ottimizzare. Per ulteriori informazioni, vedere Transact-SQL procedurale e Variabili Transact-SQL.
Non utilizzare più alias per un'unica tabella nella stessa query per simulare l'intersezione degli indici. Questa operazione non è più necessaria in quanto l'intersezione degli indici viene considerata automaticamente in SQL Server ed è possibile utilizzare più indici nella stessa tabella della stessa query. Si consideri la query di esempio:
SELECT * FROM lineitem WHERE partkey BETWEEN 17000 AND 17100 AND shipdate BETWEEN '1/1/1994' AND '1/31/1994'SQL Server può utilizzare gli indici sia sulla colonna partkey che sulla colonna shipdate, quindi eseguire una corrispondenza hash tra i due subset per ottenere l'intersezione degli indici.
Utilizzare la parametrizzazione della query per consentire il riutilizzo dei piani di esecuzione di query memorizzati nella cache. Se a un set di query è associato lo stesso valore hash della query e del piano di query, per migliorare le prestazioni è possibile creare una query con parametri. La chiamata di una query con parametri anziché di più query con valori letterali consente di riutilizzare il piano di esecuzione di query memorizzato nella cache. Per ulteriori informazioni, vedere Ricerca e ottimizzazione di query analoghe tramite valori hash della query e del piano di query e Memorizzazione nella cache e riutilizzo del piano di esecuzione.
Se non è possibile modificare l'applicazione, utilizzare guide di piano del modello con parametrizzazione forzata per ottenere un risultato analogo. Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.
Utilizzare gli hint per la query soltanto in caso di necessità. È consigliabile verificare il funzionamento senza gli hint specificati per le query che utilizzano hint in versioni precedenti di SQL Server. Gli hint possono impedire a Query Optimizer la scelta di un piano di esecuzione ottimale. Per ulteriori informazioni, vedere SELECT (Transact-SQL).
Utilizzare il valore query_plan_hash per acquisire, archiviare e confrontare i piani di esecuzione di query nel tempo. Dopo avere modificato la configurazione di sistema, è possibile confrontare valori hash del piano di query per le query di importanza critica con i valori hash originali. Le differenze nei valori hash del piano di query possono indicare se la modifica della configurazione di sistema ha provocato l'aggiornamento di piani di esecuzione per query importanti. È inoltre possibile decidere di interrompere l'esecuzione di una query corrente con esecuzione prolungata se il valore hash del piano di query in sys.dm_exec_requests differisce da quello del piano di base, nel caso in cui sia noto che le prestazioni di quest'ultimo sono elevate. Per ulteriori informazioni, vedere Ricerca e ottimizzazione di query analoghe tramite valori hash della query e del piano di query.
Utilizzare l'opzione di configurazione query governor. per evitare che query con esecuzione prolungata consumino le risorse di sistema. Per impostazione predefinita, questa opzione consente l'esecuzione di tutte le query, indipendentemente dalla durata dell'esecuzione. È tuttavia possibile impostare Query Governor in modo da limitare il numero massimo di secondi consentito per l'esecuzione di tutte le query per tutte le connessioni o delle sole query relative a una connessione specifica. Poiché Query Governor si basa sul costo stimato delle query anziché sul tempo trascorso effettivo, non comporta overhead in fase di esecuzione. Le query con esecuzione prolungata vengono inoltre interrotte prima dell'inizio anziché essere eseguite fino al limite prestabilito. Per ulteriori informazioni, vedere Opzione query governor cost limit e SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).
Ottimizzare il riutilizzo di piani di query dalla cache del piano. Il Motore di database memorizza nella cache i piani di query per un eventuale riutilizzo. Sarà possibile riutilizzare solo piani di query memorizzati nella cache. I piani di query non memorizzati nella cache dovranno invece essere compilati a ogni esecuzione, con conseguenze negative in termini di prestazioni. Le opzioni dell'istruzione SET Transact-SQL riportate di seguito consentono di impedire il riutilizzo dei piani di query memorizzati nella cache. Un batch Transact-SQL per il quale queste opzioni SET sono attivate non può condividere i relativi piani di query con lo stesso batch compilato con le stesse opzioni SET disattivate:
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET DATEFIRST
SET DATEFORMAT
SET FORCEPLAN
SET LANGUAGE
SET NO_BROWSETABLE
SET NUMERIC_ROUNDABORT
SET QUOTED_IDENTIFIER
SET TEXTSIZE
L'opzione SET ANSI_DEFAULTS influisce inoltre sul riutilizzo dei piani di query memorizzati nella cache in quanto può essere utilizzata per modificare le opzioni SET per ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS e QUOTED_IDENTIFIER. Si noti che la maggior parte delle opzioni SET modificabili con SET ANSI_DEFAULTS è indicata tra le opzioni SET che possono influire sul riutilizzo di piani di query.
È possibile modificare alcune di queste opzioni SET con i metodi seguenti:
Utilizzare la stored procedure sp_configure per modifiche per l'intero server. Per ulteriori informazioni, vedere sp_configure (Transact-SQL).
Utilizzare la clausola SET dell'istruzione ALTER DATABASE. Per ulteriori informazioni, vedere ALTER DATABASE (Transact-SQL)
Modificare le impostazioni di connessione di OLE DB e ODBC. Per ulteriori informazioni, vedere Configurazione di rete dei client.
Nota
Per evitare che i piani di query vengano ricompilati in seguito all'utilizzo di opzioni SET, definire le opzioni SET in fase di connessione e accertarsi che non vengano modificati per la durata della connessione. Per utilizzare viste indicizzate o indici su colonne calcolate, è necessario impostare alcune opzioni SET su valori specifici. Per ulteriori informazioni, vedere Opzioni SET che hanno effetto sui risultati.