Ottimizzazione guidata motore di database

Si applica a: SQL Server

Ottimizzazione guidata motore di database di Microsoft analizza i database e offre raccomandazioni per ottimizzare le prestazioni delle query. È possibile usare Ottimizzazione guidata motore di database per selezionare e creare un set ottimale di indici, viste indicizzate e partizioni di tabella anche senza conoscere in modo approfondito la struttura del database o le caratteristiche interne di SQL Server. Con DTA, è possibile eseguire le attività seguenti:

• Risolvere i problemi relativi alle prestazioni di una query del problema specifico

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

• Eseguire un'analisi di simulazione esplorativa delle possibili modifiche della progettazione fisica

• Gestire lo spazio di archiviazione

Vantaggi di Ottimizzazione guidata motore di database

L'ottimizzazione della prestazione delle query può essere difficile senza una conoscenza approfondita della struttura del database e delle query eseguite nel database. Ottimizzazione guidata motore di database (DTA) può facilitare questa attività analizzando la cache dei piano di query corrente o un carico di lavoro delle query Transact-SQL creato e consigliando una progettazione fisica adatta. Per gli amministratori di database più avanzati, DTA espone un meccanismo potente per eseguire analisi di simulazione esplorativa di diverse alternative di progettazione fisica. DTA può fornire le informazioni seguenti.

• Consigliare la combinazione di indici rowstore e columnstore ottimale 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 degli indici, la distribuzione delle query tra le tabelle e le prestazioni delle query nel carico di lavoro.

• Consigliare i metodi per ottimizzare il database per un set ridotto di query problematiche.

• Consentire all'utente di personalizzare le indicazioni mediante le opzioni avanzate, quali i vincoli di spazio su disco.

• Offrire report in cui sono riassunti gli effetti dell'implementazione delle indicazioni per un determinato carico di lavoro.

• Considerare le alternative in cui vengono indicate possibili scelte di progettazione sotto forma di configurazioni ipotetiche da sottoporre alla valutazione di Ottimizzazione guidata motore di database.

• Ottimizzare i carichi di lavoro da un'ampia gamma di origini, tra cui archivio query di SQL Server, cache dei piani, file o tabella di traccia di SQL Server Profiler o un file .SQL.

Ottimizzazione guidata motore di database consente di gestire i seguenti tipi di carico di lavoro delle query:

• Solo query di elaborazione delle transazioni online (OLTP)

• Solo query di elaborazione analitica online (OLAP)

• Query miste OLTP e OLAP

• Carichi di lavoro elevati in termini di query (più query che modifiche di dati)

• Carichi di lavoro elevati in termini di aggiornamento (più modifiche di dati che query)

Componenti e concetti DTA

Interfaccia utente grafica di Ottimizzazione guidata motore di database
Un'interfaccia di facile utilizzo nella quale è possibile specificare il carico di lavoro e selezionare diverse opzioni di ottimizzazione.

Utilità dta
Versione del prompt dei comandi di Ottimizzazione guidata motore di database. L'utilità dta è stata sviluppata per consentire l'utilizzo della funzionalità Ottimizzazione guidata motore di database in applicazioni e script.

Carico di lavoro
File script Transact-SQL, file di traccia, o tabella di traccia che contiene un carico di lavoro rappresentativo per i database che si desidera ottimizzare. A partire da SQL Server 2012 (11.x), è possibile specificare la cache dei piani come carico di lavoro. A partire da SQL Server 2016 (13.x), è possibile specificare Query Store come carico di lavoro.

File di input XML
File in formato XML che Ottimizzazione guidata motore di database può usare per ottimizzare i carichi di lavoro. Il file di input XML supporta le opzioni di ottimizzazione avanzate che non sono disponibili nella GUI o nell'utilità dta .

Limitazioni e restrizioni

Ottimizzazione guidata motore di database presenta le seguenti limitazioni e restrizioni.

• Non può aggiungere o rilasciare gli indici univoci o gli indici che impongono vincoli PRIMARY KEY o UNIQUE.

• Non può analizzare un database impostato su modalità utente singolo.

• Se si specifica uno spazio massimo su disco per le indicazioni di ottimizzazione e tale spazio supera lo spazio disponibile effettivo, verrà comunque utilizzato il valore specificato. Quando si esegue lo script delle indicazioni, è tuttavia possibile che non venga eseguito se prima non è stato aumentato lo spazio su disco. Per specificare lo spazio massimo su disco è possibile usare l'opzione -B dell'utilità dta oppure specificare un valore nella finestra di dialogo Opzioni di ottimizzazione avanzate .

• Per motivi di sicurezza, Ottimizzazione guidata motore di database non è in grado di ottimizzare un carico di lavoro in una tabella di traccia che si trova su un server remoto. Per risolvere temporaneamente questa limitazione, è possibile utilizzare un file di traccia anziché una tabella di traccia o copiare quest'ultima nel server remoto.

• Se si impongono vincoli, ad esempio si specifica uno spazio massimo su disco per le indicazioni di ottimizzazione (tramite l'opzione -B o la finestra di dialogo Opzioni di ottimizzazione avanzate ), l'Ottimizzazione guidata motore di database potrebbe essere obbligata a eliminare indici esistenti specifici. In questo caso, è possibile che l'indicazione risultante generi un miglioramento previsto negativo.

• Se si specifica un vincolo per limitare il tempo di ottimizzazione tramite l'opzione -A dell'utilità dta o selezionando l'opzione Limita tempo di ottimizzazione della scheda Opzioni di ottimizzazione , è possibile che l'Ottimizzazione guidata motore di database superi tale limite di tempo per creare un miglioramento previsto accurato e che i report di analisi relativi a una parte del carico di lavoro vengano esauriti.

• È possibile che Ottimizzazione guidata motore di database non crei indicazioni nei seguenti casi:

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

  2. Gli indici inseriti nelle indicazioni non offrono un miglioramento delle prestazioni delle query adeguato per la progettazione fisica del database corrente.

  3. L'utente che esegue l'Ottimizzazione guidata motore di database non è un membro del ruolo del 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 Ottimizzazione guidata motore di database. L'utente deve essere un membro del ruolo del database db_owner .

• Ottimizzazione guidata motore di database archivia i dati delle sessioni di ottimizzazione e le altre informazioni nel database msdb. Se vengono apportate modifiche al database msdb esiste il rischio di perdere dati delle sessioni di ottimizzazione. Per eliminare tale rischio, implementare una strategia di backup appropriata per il database msdb.

Considerazioni sulle prestazioni

Ottimizzazione guidata motore di database può impegnare una notevole quantità di risorse dei processori e di memoria durante l'analisi. Per evitare rallentamenti del server di produzione, è possibile applicare una delle strategie seguenti:

• Ottimizzare i database quando il carico di lavoro del server è minimo. Ottimizzazione guidata motore di database può influire sulle prestazioni delle attività di manutenzione.

• Utilizzare la strategia che prevede l'utilizzo combinato di un server di prova e un server di produzione. Per altre informazioni, vedere Riduzione del carico di ottimizzazione del server di produzione.

• Specificare unicamente le strutture di progettazione fisica del database che si desidera vengano analizzate da Ottimizzazione guidata motore di database. La procedura guidata offre numerose opzioni, ma specifica solo quelle necessarie.

Dipendenza dalla stored procedure estesa xp_msver

Per offrire funzionalità complete, Ottimizzazione guidata motore di database dipende dalla stored procedure estesa xp_msver . Questa stored procedure estesa è attiva per impostazione. Questa stored procedure estesa viene utilizzata da Ottimizzazione guidata motore di database per recuperare il numero di processori e la memoria disponibile sul computer che contiene il database da ottimizzare. Se xp_msver non è disponibile, l'Ottimizzazione guidata motore di database prende in considerazione le caratteristiche hardware del computer in cui è in esecuzione . Se le caratteristiche hardware del computer in cui è in esecuzione Ottimizzazione guidata motore di database non sono disponibili, vengono considerati un processore e 1024 megabyte (MB) di memoria.

La relazione di dipendenza influisce sulle indicazioni relative al partizionamento, in quanto il numero di partizioni consigliate dipende da questi due valori (numero di processori e memoria disponibile). La dipendenza influisce inoltre sui risultati dell'ottimizzazione quando si utilizza un server di prova per ottimizzare il server di produzione. In questo scenario Ottimizzazione guidata motore di database usa xp_msver per recuperare le proprietà hardware del server di produzione. Dopo avere ottimizzato il carico di lavoro nel server di prova, Ottimizzazione guidata motore di database utilizza queste proprietà hardware per generare un'indicazione. Per altre informazioni, vedere xp_msver (Transact-SQL).

Attività di Ottimizzazione guidata motore di database

Nella tabella seguente vengono elencate attività comuni di Ottimizzazione guidata motore di database e articoli che illustrano come eseguirle.