Search the site...
Starting the Database Engine Tuning Advisor
You have various options to start the Database Engine Tuning Advisor graphical user interface. However, you should never start this tool when running database engine in single-user mode.
From Windows Start Menu
Navigate to 'Start' and select 'All Programs'. On the 'All Programs' submenu, click 'Microsoft SQL Server' → 'Performance Tools' → 'Database Engine Tuning Advisor'.
From SQL Server Management Studio
While you are in SQL Server Management Studio, click 'Tools' and select ' Database Engine Tuning Advisor'.
From the SQL Server Management Studio Query Editor
Launch SQL Server Management Studio and open any Transact-SQL script file. Highlight a query, right-click this query, and select 'Analyze Query in Database Engine Tuning Advisor'. This will open the GUI of the Database Engine Tuning Advisor.
Using SQL Server Profiler
Start SQL Server Profiler. On the 'Tools' menu, select 'Database Engine Tuning Advisor'.
Creating a Workload
A workload is referred to as a group of T-SQL statements that runs against a specific database or a set of databases you need to tune. The Database Engine Tuning Advisor works by examining these workloads and suggesting the most appropriate indexing or partitioning strategies for your database in order to best optimize the query performance. You may create workloads using any of the below mentioned methods:
If you want to refrain from creating a workload manually, you can choose to use the plan cache as your workload.
You can create Transact-SQL script workloads using the following procedure:
Open SQL Server Management Studio and start Query Editor.
Type a set of Transact-SQL statements to generate a Transact-SQL script.
Save this script and give it a '.SQL' extension. This file can be used as a workload with the Database Engine Tuning Advisor GUI and the command-line dta utility.
You can create a trace file or trace table workload through SQL Server Profiler as follows:
Start SQL Server Management Studio. On the ‘Tools’ menu, select SQL Server Profiler.
Create a Trace with the help of SQL Server Profiler.
Save the Trace results to a file or table to create a trace file or a trace table that uses the SQL Server Profiler Tuning template.
Tuning a Database
For tuning a database, you need the Database Engine Tuning Advisor GUI or the dta utility. When using the Database Engine Tuning Advisor GUI, this can be done either through the plan cache or a workload file or table.
Using Plan Cache for Database Tuning:
Follow the given procedure to tune a SQL Server database using plan cache as a workload:
Start Database Engine Tuning Advisor and connect to an instance of SQL Server.
Navigate to the ‘General’ tab and provide a name to the session you are starting in ‘Session Name’. You should specify all options on this tab to start a tuning session. You need not change anything on the ‘Tuning Options’ tab before starting a new tuning session.
Choose ‘Plan Cache’ to be used as the workload. Database Engine Tuning Advisor picks the top 1000 events in the plan cache to be used for analysis.
Choose a database or a set of databases you need to tune. You may also select tables in each database from ‘Selected Tables’. If you selected multiple databases, you need to include events from the plan caches for all databases. To do this, select ‘Advanced Options’ in ‘Tuning Options’ and select ‘Include plan cache events from all databases’.
If you want to save a copy of the tuning log, select ‘Save tuning log’.
Select the ‘Tuning Options’ tab and configure the fields provided.
Click ‘Start Analysis’ to start the session.
You may stop the tuning session using either ‘Stop Analysis (With Recommendations)’ or ‘Stop Analysis’. The former will stop the session and generate recommendations, the latter will end the session without providing any recommendations.
Using Workload File or Table for Tuning a Database:
The following is the procedure to tune a SQL Server database using a workload file or table:
Identify the database features (such as indexes, index views, or partitioning) you want Database Engine Tuning Advisor to take into account when performing its analysis.
Create a workload using any of the methods mentioned earlier.
Start Database Engine Tuning Advisor and connect to an instance of SQL Server.
On the ‘General’ tab, specify a name for the new tuning session in ‘Session name’.
Select ‘Workload File’ or ‘Table’. You need to specify the exact location of the file or the name of the table in the text box.
When specifying the table name, use the following format:
‘database_name.schema_name.table_name’
You need to make sure that the table should be present on the server that the Database Engine Tuning Advisor is tuning.
Select the database or the set of databases you wish to tune. You can select tables using the ‘Selected Tables’ arrow.
To save a copy of the tuning log, select ‘Save tuning log’.
Specify the options on the ‘Tuning Options’ tab.
Click ‘Start Analysis’ button to start the tuning session. You may choose from ‘Stop Analysis (With Recommendations)’ or ‘Stop Analysis’ to stop the session anytime.
You may also use the dta utility to performance tune your database queries. This utility does the same job performed by Database Engine Tuning Advisor, but uses batch files and scripts. You can use plan cache, trace files, trace tables, or T-SQL scripts as your workload option.
Microsoft Sql Database Engine
-->APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
Database Engine Tuning Advisor provides a graphical user interface (GUI) based way to view tuning sessions and tuning recommendation reports. This lesson shows you how to start the tool and how to configure the display. At the end of this lesson, you will know the different ways you can start the tool and how to configure its display to support the tuning tasks that you regularly perform.
Prerequisites
To complete this tutorial, you need SQL Server Management Studio, access to a server that's running SQL Server, and an AdventureWorks database.
- Install SQL Server Management Studio.
- Install SQL Server 2017 Developer Edition.
- Download AdventureWorks2017 sample databases.
Instructions for restoring databases in SSMS are here: Restore a database.
Note
This tutorial is meant for a user familiar with using SQL Server Management Studio and basic database administration tasks.
Launch Database Tuning Advisor
To begin, open the Database Engine Tuning Advisor (DTA) graphical user interface (GUI). On first use, a member of the sysadmin fixed server role must launch Database Engine Tuning Advisor to initialize the application. After initialization, members of the db_owner fixed database role can use Database Engine Tuning Advisor to tune databases that they own. For more information about initializing Database Engine Tuning Advisor, see Start and Use the Database Engine Tuning Advisor.
- Start SQL Server Management Studio (SSMS). On the Windows Start Menu, point to All Programs and locate SQL Server Management Studio.
- Once SSMS is open, select the Tools menu and select Database Tuning Advisor.
- Database Tuning Advisor launches, and opens the Connect to Server dialog box. Verify the default settings, and then select Connect to connect to your SQL Server.
By default, Database Engine Tuning Advisor opens to the configuration in the following illustration:
Note
The Session Monitor tab displays the session name, which is the name of the connected user and current data.
Two main panes are displayed in the Database Engine Tuning Advisor GUI when it is first opened.
The left pane contains the Session Monitor, which lists all tuning sessions that have been performed on this Microsoft SQL Server instance. When you open Database Engine Tuning Advisor, it displays a new session at the top of the pane. You can name this session in the adjacent pane. Initially, only a default session is listed. This is the default session that Database Engine Tuning Advisor automatically creates for you. After you have tuned databases, all tuning sessions for the SQL Server instance to which you are connected are listed below the new session. You can right-click a tuning session to rename it, close it, delete it, or clone it. If you right-click in the list you can sort the sessions by name, status, or creation time, or create a new session. In the bottom section of this pane, details of the selected tuning session are displayed. You can choose to display the details organized into categories with the Categorized button, or you can display them in an alphabetized list by using the Alphabetical button. You can also hide Session Monitor by dragging the right pane border to the left side of the window. To view it again, drag the pane border back to the right. Session Monitor enables you to view previous tuning sessions, or to use them to create new sessions with similar definitions. You can also use Session Monitor to evaluate tuning recommendations. For more information, see View and Work with the Output from the Database Engine Tuning Advisor. Use the Back button on your browser to return to this tutorial.
The right pane contains the General and the Tuning Options tabs. This is where you can define your Database Engine Tuning session. In the General tab, you type the name for your tuning session, specify the workload file or table to use, and select the databases and tables you want to tune in this session. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases. On the Tuning Options tab, you can select the physical database design structures (indexes or indexed views) and the partitioning strategy that you want Database Engine Tuning Advisor to consider during its analysis. On this tab, you also can specify the maximum time that Database Engine Tuning Advisor takes to tune a workload. By default, Database Engine Tuning Advisor will tune a workload for one hour.
Note
Database Engine Tuning Advisor can take XML files as input when a Transact-SQL script is imported from MicrosoftSQL Server Management Studio Query Editor. For more information, see the section on launching Database Engine Tuning Advisor from the SQL Server Management Studio Query Editor in Start and Use the Database Engine Tuning Advisor.
Configure tool options and layout
- On the Tools menu, click Options.
In the Options dialog box, view the following options:
Expand the On startup list to view what Database Engine Tuning Advisor can display when it is started. By default, Show a new session is selected.
Click Change Font to see what fonts you can choose for the lists of databases and tables on the General tab. The fonts you choose for this option also are used in Database Engine Tuning Advisor recommendation grids and reports after you have performed tuning. By default, Database Engine Tuning Advisor uses system fonts.
The Number of items in most recently used lists can be set between 1 and 10. This sets the maximum number of items in the lists displayed by clicking Recent Sessions or Recent Files on the File menu. By default, this option is set to 4.
When Remember my last tuning options is checked, by default Database Engine Tuning Advisor uses the tuning options you specified for your last tuning session for the next tuning session. Clear this check box to use Database Engine Tuning Advisor tuning option defaults. By default, this option is selected.
By default, Ask before permanently deleting sessions is checked to avoid accidentally deleting tuning sessions.
By default, Ask before stopping session analysis is checked to avoid accidentally stopping a tuning session before Database Engine Tuning Advisor has finished analyzing a workload.
Next Lesson
