Consigliere di ottimizzazione del motore di database

Il Database Engine Tuning Advisor di Microsoft analizza i database e fornisce consigli che è possibile utilizzare per ottimizzare le prestazioni delle query. È possibile usare il Consulente per l'ottimizzazione del motore di database per selezionare e creare un set ottimale di indici, viste indicizzate o partizioni di tabella senza avere una conoscenza approfondita della struttura del database o dei meccanismi interni di SQL Server. Utilizzando il DTA, è possibile eseguire i seguenti compiti.

• Risolvere i problemi relativi alle prestazioni di una query specifica problematica

• Ottimizzare un ampio set di query in uno o più database

• Effettuare un'analisi ipotetica esplorativa di potenziali modifiche nel design fisico

• Gestire lo spazio di archiviazione

Vantaggi del Consulente di ottimizzazione per il motore di database

L'ottimizzazione delle prestazioni delle query può essere difficile senza una conoscenza completa della struttura del database e delle query eseguite sul database. Advisor di ottimizzazione del motore di database può semplificare questa attività analizzando la cache del piano di query corrente o analizzando un carico di lavoro di Transact-SQL query che crei e consigliando una progettazione fisica appropriata. Per gli amministratori di database più avanzati, DTA espone un meccanismo potente per eseguire analisi esplorative ipotetiche di diverse alternative di progettazione fisica. Il DTA può fornire le seguenti informazioni.

• Consigliare la combinazione ottimale di indici per i database usando Query Optimizer per analizzare le query in un carico di lavoro.

• Consigliare partizioni allineate o non allineate per i database a cui si fa riferimento in un carico di lavoro.

• Consigliare viste indicizzate per i database a cui si fa riferimento in un carico di lavoro.

• Analizzare gli effetti delle modifiche proposte, tra cui l'utilizzo dell'indice, la distribuzione delle query tra le tabelle e le prestazioni delle query nel carico di lavoro.

• Consigliare i modi per ottimizzare il database per un piccolo set di query problematiche.

• Consente di personalizzare la raccomandazione specificando opzioni avanzate, ad esempio vincoli di spazio su disco.

• Fornire report che riepilogano gli effetti dell'implementazione delle raccomandazioni per un determinato carico di lavoro.

L'Advisor di Ottimizzazione Motore di Database è progettato per gestire i seguenti tipi di carichi di lavoro di query.

• Solo query di elaborazione delle transazioni online (OLTP)

• Solo query OLAP (elaborazione analitica online)

• Query OLTP e OLAP combinati

• Carichi di lavoro con un numero elevato di query (più query rispetto alle modifiche ai dati)

• Carichi di lavoro con un numero elevato di aggiornamenti (più modifiche dei dati rispetto alle query)

Componenti e concetti di DTA

Interfaccia grafica utente dell'ottimizzazione guidata del motore di database
Interfaccia facile da usare in cui è possibile specificare il carico di lavoro e selezionare varie opzioni di ottimizzazione.

dta Utilità
Versione prompt dei comandi di Ottimizzatore motore di database. L'utilità dta è stata sviluppata per consentire l'utilizzo della funzionalità Guida dell'ottimizzazione per il motore di database in applicazioni e script.

Carico di lavoro
Un file di script Transact-SQL, un file di traccia o una tabella di traccia che contiene un carico di lavoro rappresentativo per i database che si desidera ottimizzare. A partire da SQL Server 2012, è possibile specificare la cache dei piani come carico di lavoro.

File di input XML
File in formato XML che l'Ottimizzatore motore di database può utilizzare per sintonizzare i carichi di lavoro. Il file di input XML supporta opzioni di ottimizzazione avanzate che non sono disponibili nell'utilità GUI o dta .

Limitazioni e restrizioni

L'Ottimizzazione Guidata Motore di Database presenta le limitazioni e le restrizioni seguenti.

• Non può aggiungere o eliminare indici o indici univoci che applicano vincoli PRIMARY KEY o UNIQUE.

• Non è possibile analizzare un database impostato sulla modalità utente singolo.

• Se specifichi uno spazio su disco massimo per le raccomandazioni di ottimizzazione che superano lo spazio disponibile effettivamente, l’Advisor di ottimizzazione del motore di database usa il valore specificato. Tuttavia, quando si esegue lo script di raccomandazione per implementarlo, lo script potrebbe non riuscire se non viene aggiunto prima più spazio su disco. È possibile specificare spazio massimo su disco con l'opzione -B dell'utilità dta oppure immettendo un valore nella finestra di dialogo Opzioni di ottimizzazione avanzata .

• Per motivi di sicurezza, l'Advisor di ottimizzazione del motore di database non può ottimizzare un carico di lavoro presente in una tabella di monitoraggio su un server remoto. Per ovviare a questa limitazione, è possibile usare un file di traccia anziché una tabella di traccia o copiare la tabella di traccia nel server remoto.

• Quando si impongono vincoli, come quando si specifica uno spazio su disco massimo per le raccomandazioni di ottimizzazione (utilizzando l'opzione -B o la finestra di dialogo Opzioni di ottimizzazione avanzate), Ottimizzazione Guidata Motore di Database potrebbe essere costretta a eliminare determinati indici esistenti. In questo caso, la raccomandazione del Consulente per l'ottimizzazione del motore di database risultante può produrre un miglioramento atteso negativo.

• Quando si specifica un vincolo per limitare il tempo di ottimizzazione (usando l'opzione -A con l'utilità dta o selezionando Limita tempo di ottimizzazione nella scheda Opzioni di ottimizzazione), il Consulente di ottimizzazione del motore di database può superare tale limite di tempo per produrre un miglioramento previsto accurato e i report di analisi per qualsiasi parte del carico di lavoro che è stata elaborata finora.

• Il Consulente per l'ottimizzazione del motore di database potrebbe non fornire raccomandazioni nelle seguenti circostanze:

  1. La tabella da ottimizzare contiene meno di 10 pagine di dati.

  2. Gli indici consigliati non offrono un miglioramento sufficiente delle prestazioni delle query rispetto alla progettazione corrente del database fisico.

  3. L'utente che esegue Consulente per l'ottimizzazione del motore di database non è membro del ruolo di database db_owner o del ruolo predefinito del server sysadmin. Le query nel carico di lavoro vengono analizzate nel contesto di sicurezza dell'utente che esegue l'Advisor per l'ottimizzazione del motore di database. L'utente deve essere membro del ruolo del db_owner database.

• Advisor Ottimizzazione Motore di Database archivia i dati della sessione di ottimizzazione e altre informazioni nel database msdb. Se vengono apportate modifiche al msdb database, è possibile rischiare di perdere i dati della sessione di ottimizzazione. Per eliminare questo rischio, implementare una strategia di backup appropriata per il msdb database.

Considerazioni sulle prestazioni

L'Advisor per l'ottimizzazione del motore di database può consumare risorse significative di processore e memoria durante l'analisi. Per evitare di rallentare il server di produzione, seguire una delle strategie seguenti:

• Ottimizzare i database quando il server è gratuito. Database Engine Tuning Advisor può influire sulle prestazioni delle operazioni di manutenzione.

• Usare la funzionalità server di test/server di produzione. Per altre informazioni, vedere Ridurre il carico di ottimizzazione del server di produzione.

• Specificare solo le strutture di progettazione fisica del database che si desidera analizzare da parte del Consulente di Ottimizzazione del Motore di Database. Lo Strumento di Ottimizzazione del Motore di Database offre molte opzioni, ma specifica solo quelle necessarie.

Dipendenza da xp_msver Stored Procedure Estesa

Il Consulente di Ottimizzazione del Motore di Database dipende dalla procedura memorizzata estesa xp_msver per offrire funzionalità complete. La procedura memorizzata estesa è attivata per impostazione predefinita. Il Consulente per l'ottimizzazione del motore di database utilizza questa stored procedure estesa per ottenere il numero di processori e la memoria disponibile sul computer dove risiede il database che si sta ottimizzando. Se xp_msver non è disponibile, Ottimizzazione guidata motore di database presuppone le caratteristiche hardware del computer in cui è in esecuzione Ottimizzazione guidata motore di database. Se le caratteristiche hardware del computer in cui è in esecuzione l'Advisor per l'ottimizzazione del motore di database non sono disponibili, si assume un processore e 1024 megabyte (MB) di memoria.

Questa dipendenza influisce sulle raccomandazioni sul partizionamento perché il numero di partizioni consigliate dipende da questi due valori (numero di processori e memoria disponibile). La dipendenza influisce anche sui risultati di ottimizzazione quando si usa un server di test per ottimizzare il server di produzione. In questo scenario, Ottimizzazione guidata del motore di database usa xp_msver per ottenere le proprietà hardware dal server di produzione. Dopo aver ottimizzato il carico di lavoro sul server di test, l'Advisor di Ottimizzazione del Motore di Database utilizza queste proprietà hardware per generare un consiglio. Per altre informazioni, vedere xp_msver (Transact-SQL).

Attività di Consigliere per l'Ottimizzazione del Motore di Database

Nella tabella seguente sono elencate le attività comuni dell'Advisor per l'ottimizzazione del motore di database e gli argomenti che spiegano come eseguirle.

Attività del Consulente per l'Ottimizzazione del Motore di Database	Argomento
Inizializzare e avviare il Consulente di Ottimizzazione del Motore di Database. Creare un carico di lavoro specificando la cache dei piani, creando uno script o generando un file di traccia o una tabella di traccia. Ottimizzare un database usando lo strumento Consigliere di Ottimizzazione del Motore di Database. Creare file di input XML per ottimizzare i carichi di lavoro. Visualizzare le descrizioni delle opzioni dell'interfaccia utente dell'Advisor di Ottimizzazione del Motore di Database.	Avvia e utilizza l'Advisor di Tuning del Motore di Database
Visualizzare i risultati dell'operazione di ottimizzazione del database. Selezionare e implementare le raccomandazioni di ottimizzazione. Eseguire l'analisi esplorativa "what-if" sul carico di lavoro. Esaminare le sessioni di ottimizzazione esistenti, clonare le sessioni in base a quelle esistenti o modificare le raccomandazioni di ottimizzazione esistenti per un'ulteriore valutazione o implementazione. Visualizza le descrizioni delle opzioni dell'interfaccia utente dell'Advisor di Ottimizzazione Motore di Database.	Visualizzare e usare l'output di Ottimizzazione guidata motore di database