Microsoft Database Engine Tuning Advisor (DTA) is a database performance tuning utility designed to analyze your SQL Server database and suggest actions to take your query performance to the next level. You can use this tool to make decisions for your database that have an effect on its overall performance. The Database Engine Tuning Advisor built into SQL Server 2012 empowers you to perform the following tasks:

• Analyze the performance of a particular query

• Control and tune a group of queries across multiple databases

• Know when your database requires design changes and what the benefits are in implementing them

• Optimize storage space allocation

Initializing SQL Server Tuning Advisor

Before starting to use the Database Engine Tuning Advisor, you need to initialize this performance tuning software. To do so, you must be a member of the 'sysadmin' fixed server role. Initialization results in creation of system tables in your msdb database that will be used to carry out several tuning operations. The process also facilitates tuning workloads on tables by the owners of the database that have membership privileges of the 'db_owner' fixed database role.

A user with administrative rights should perform any of the following tasks:

• Connect to an instance of SQL Server 2012 through the GUI of Database Engine Tuning Advisor.

• Tune the very first workload via the dta utility (the command prompt version of the Database Engine Tuning Advisor).

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.