Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor

Ottimizzazione guidata motore di database (DTA) di MicrosoftMicrosoft analizza i database e fornisce consigli da utilizzare per ottimizzare le prestazioni di query.The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. È possibile utilizzare Ottimizzazione guidata motore di database per selezionare e creare un set ottimale di indici, viste indicizzate e partizioni di tabella senza che sia necessario conoscere in modo approfondito la struttura del database o le caratteristiche interne di SQL ServerSQL Server.You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. Con DTA, è possibile eseguire le attività seguenti.Using the DTA, you can perform the following tasks.

  • Risolvere i problemi relativi alle prestazioni di una query del problema specificoTroubleshoot the performance of a specific problem query

  • Ottimizzare un grande set di query in uno o più databaseTune a large set of queries across one or more databases

  • Eseguire un'analisi di simulazione esplorativa delle possibili modifiche della progettazione fisicaPerform an exploratory what-if analysis of potential physical design changes

  • Gestire lo spazio di archiviazioneManage storage space

Vantaggi di Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor Benefits

L'ottimizzazione della prestazione delle query può essere difficile senza una conoscenza approfondita della struttura del database e delle query eseguite nel database.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. Ottimizzazione guidata motore di database può rendere più facile questa attività grazie all'analisi della cache dei piano di query corrente o di un carico di lavoro delle query Transact-SQLTransact-SQL creato e consigliando una progettazione fisica adatta.The Database Engine Tuning Advisor can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. Per gli amministratori di database più avanzati, DTA espone un meccanismo potente per eseguire analisi di simulazione esplorativa di diverse alternative di progettazione fisica.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. DTA può fornire le informazioni seguenti.The DTA can provide the following information.

  • Consigliare la combinazione di indici rowstore e columnstore ottimale per i database usando Query Optimizer per analizzare le query in un carico di lavoro.Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • Consigliare partizioni allineate o non allineate per i database a cui si fa riferimento in un carico di lavoro.Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • Consigliare viste indicizzate per i database a cui si fa riferimento in un carico di lavoro.Recommend indexed views for databases referenced in a workload.

  • 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.Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • Consigliare i metodi per ottimizzare il database per un set ridotto di query problematiche.Recommend ways to tune the database for a small set of problem queries.

  • Consentire all'utente di personalizzare le indicazioni mediante le opzioni avanzate, quali i vincoli di spazio su disco.Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • Offrire report in cui sono riassunti gli effetti dell'implementazione delle indicazioni per un determinato carico di lavoro.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • 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.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • 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.Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

Ottimizzazione guidata motore di database consente di gestire i seguenti tipi di carico di lavoro delle query.The Database Engine Tuning Advisor is designed to handle the following types of query workloads.

  • Solo query di elaborazione delle transazioni online (OLTP)Online transaction processing (OLTP) queries only

  • Solo query di elaborazione analitica online (OLAP)Online analytical processing (OLAP) queries only

  • Query miste OLTP e OLAPMixed OLTP and OLAP queries

  • Carichi di lavoro elevati in termini di query (più query che modifiche di dati)Query-heavy workloads (more queries than data modifications)

  • Carichi di lavoro elevati in termini di aggiornamento (più modifiche di dati che query)Update-heavy workloads (more data modifications than queries)

Componenti e concetti DTADTA Components and Concepts

Interfaccia utente grafica di Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor Graphical User Interface
Un'interfaccia di facile utilizzo nella quale è possibile specificare il carico di lavoro e selezionare diverse opzioni di ottimizzazione.An easy-to-use interface in which you can specify the workload and select various tuning options.

Utilitàdta dta Utility
Versione del prompt dei comandi di Ottimizzazione guidata motore di database.The command prompt version of Database Engine Tuning Advisor. L'utilità dta è stata sviluppata per consentire l'utilizzo della funzionalità Ottimizzazione guidata motore di database in applicazioni e script.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

carico di lavoroworkload
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 Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. A partire da SQL Server 2012SQL Server 2012, è possibile specificare la cache dei piani come carico di lavoro.Beginning with SQL Server 2012SQL Server 2012, you can specify the plan cache as the workload. A partire da SQL Server 2016SQL Server 2016, è possibile specificare l'archivio query come carico di lavoro.Beginning with with SQL Server 2016SQL Server 2016, you can specify the Query Store as the workload.

file di input XMLXML input file
File in formato XML che Ottimizzazione guidata motore di database può utilizzare per ottimizzare i carichi di lavoro.An XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. Il file di input XML supporta le opzioni di ottimizzazione avanzate che non sono disponibili nella GUI o nell'utilità dta .The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

Limitazioni e restrizioniLimitations and Restrictions

Ottimizzazione guidata motore di database presenta le seguenti limitazioni e restrizioni.The Database Engine Tuning Advisor has the following limitations and restrictions.

  • Non può aggiungere o rilasciare gli indici univoci o gli indici che impongono vincoli PRIMARY KEY o UNIQUE.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • Non può analizzare un database impostato su modalità utente singolo.It cannot analyze a database that is set to single-user mode.

  • 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.If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. Quando si esegue lo script delle indicazioni, è tuttavia possibile che non venga eseguito se prima non è stato aumentato lo spazio su disco.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. 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 .Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • 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.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. Per risolvere temporaneamente questa limitazione, è possibile utilizzare un file di traccia anziché una tabella di traccia o copiare quest'ultima nel server remoto.To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • 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.When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. In questo caso, è possibile che l'indicazione risultante generi un miglioramento previsto negativo.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • 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.When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • È possibile che Ottimizzazione guidata motore di database non crei indicazioni nei seguenti casi:Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. La tabella da ottimizzare contiene meno di 10 pagine di dati.The table being tuned contains less than 10 data pages.

    2. Gli indici inseriti nelle indicazioni non offrono un miglioramento delle prestazioni delle query adeguato per la progettazione fisica del database corrente.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    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 .The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. Le query nel carico di lavoro vengono analizzate nel contesto di sicurezza dell'utente che esegue Ottimizzazione guidata motore di database.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. L'utente deve essere un membro del ruolo del database db_owner .The user must be a member of the db_owner database role.

  • L'Ottimizzazione guidata motore di database memorizza i dati delle sessioni di ottimizzazione e le altre informazioni nel database msdb .Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Se vengono apportate modifiche al database msdb esiste il rischio di perdere dati delle sessioni di ottimizzazione.If changes are made to the msdb database you may risk losing tuning session data. Per eliminare tale rischio, implementare una strategia di backup appropriata per il database msdb .To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Considerazioni sulle prestazioniPerformance Considerations

Ottimizzazione guidata motore di database può impegnare una notevole quantità di risorse dei processori e di memoria durante l'analisi.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. Per evitare rallentamenti del server di produzione, è possibile applicare una delle strategie seguenti:To avoid slowing down your production server, follow one of these strategies:

  • Ottimizzare i database quando il carico di lavoro del server è minimo.Tune your databases when your server is free. Ottimizzazione guidata motore di database può influire sulle prestazioni delle attività di manutenzione.Database Engine Tuning Advisor can affect maintenance task performance.

  • Utilizzare la strategia che prevede l'utilizzo combinato di un server di prova e un server di produzione.Use the test server/production server feature. Per altre informazioni, vedere Riduzione del carico di ottimizzazione del server di produzione.For more information, see Reduce the Production Server Tuning Load.

  • Specificare unicamente le strutture di progettazione fisica del database che si desidera vengano analizzate da Ottimizzazione guidata motore di database.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. La procedura guidata offre numerose opzioni, ma specifica solo quelle necessarie.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Dipendenza dalla stored procedure estesa xp_msverDependency on xp_msver Extended Stored Procedure

Per offrire funzionalità complete, Ottimizzazione guidata motore di database dipende dalla stored procedure estesa xp_msver .Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. Questa stored procedure estesa è attiva per impostazione.This extended stored procedure is turned on by default. 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.Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. Se xp_msver non è disponibile, l'Ottimizzazione guidata motore di database prende in considerazione le caratteristiche hardware del computer in cui è in esecuzione .If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. 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.If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

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).This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). La dipendenza influisce inoltre sui risultati dell'ottimizzazione quando si utilizza un server di prova per ottimizzare il server di produzione.The dependency also affects your tuning results when you use a test server to tune your production server. In questo scenario Ottimizzazione guidata motore di database usa xp_msver per recuperare le proprietà hardware del server di produzione.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. Dopo avere ottimizzato il carico di lavoro nel server di prova, Ottimizzazione guidata motore di database utilizza queste proprietà hardware per generare un'indicazione.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. Per altre informazioni, vedere xp_msver ( Transact-SQL ).For more information, see xp_msver (Transact-SQL).

Attività di Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor Tasks

Nella tabella seguente vengono elencate attività di Ottimizzazione guidata motore di database comuni e argomenti che illustrano come eseguirle.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

Attività di Ottimizzazione guidata motore di databaseDatabase Engine Tuning Advisor Task ArgomentoTopic
Inizializzare e avviare Ottimizzazione guidata motore di database.Initialize and start the Database Engine Tuning Advisor.

Creare un carico di lavoro specificando la cache dei piani, creando uno script o generando un file di traccia o una tabella di traccia.Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

Ottimizzare un database tramite lo strumento dell'interfaccia utente grafica Ottimizzazione guidata motore di database.Tune a database by using the Database Engine Tuning Advisor graphical user interface tool.

Creare file input XML per l'ottimizzazione di carichi di lavoro.Create XML input files to tune workloads.

Visualizzare le descrizioni delle opzioni dell'interfaccia utente di Ottimizzazione guidata motore di database.View descriptions of the Database Engine Tuning Advisor user interface options.
Avvio e utilizzo di Ottimizzazione guidata motore di databaseStart and Use the Database Engine Tuning Advisor
Visualizzare i risultati dell'operazione di ottimizzazione del database.View the results of the database tuning operation.

Selezionare e implementare le indicazioni relative all'ottimizzazione.Select and implement tuning recommendations.

Eseguire l'analisi di simulazione esplorativa nel carico di lavoro.Perform what-if exploratory analysis against the workload.

Rivedere le sessioni di ottimizzazione esistenti, clonare le sessioni in base a quelle esistentiReview existing tuning sessions, clone sessions based on existing ones
o modificare le indicazioni di ottimizzazione per un'ulteriore valutazione o implementazione.or edit existing tuning recommendations for further evaluation or implementation.

Visualizzare le descrizioni delle opzioni dell'interfaccia utente di Ottimizzazione guidata motore di database.View descriptions of the Database Engine Tuning Advisor user interface options.
Visualizzare e utilizzare l'output di Ottimizzazione guidata motore di databaseView and Work with the Output from the Database Engine Tuning Advisor