Skip to content

MS SQL

An Introduction to Database Engine Tuning Advisor (DETA) in SQL Server 2012

Introduction

Database Engine Tuning Advisor (DETA or DTA) is a performance analysis tool that helps in the following scenario:

  1. Tuning of a query or a group of queries that are executing slower than expected;
  2. Identify the changes that can be made in the database so that the query performance is enhanced for a specific batch;
  3. It can even be helpful in identifying plans to carry out in case a query batch includes operations from multiple databases.

P.S. This tool is not available in any of the Express editions of SQL Server.

Options to start DETA

DETA can be started using any one of the following 4 possible options:

1. From Start Menu; go to “All Programs” -> “Microsoft SQL Server 2012” -> “Performance Tools”   -> “Database Engine Tuning Advisor” (shown in Pic 1)

Open option from Start Menu

Pic 1 Opening option from Start Menu

2. We can also start it from SQL Server Management Studio from menu “Tools” -> “Database Engine Tuning Advisor” (shown in Pic. 2).

Pic2. Open option from SSMS Tools Menu

Pic 2. Open option from SSMS Tools Menu

3. We can start DETA by right-clicking on an SQL query written in SQL Server Management Studio and selecting “Analyze query in Database Engine Tuning Advisor” (shown in Pic 3).

Pic 3 Open option from SSMS Query context menu

Pic 3 Open option from SSMS Query context menu

 4. We can even initiate a DETA session from SQL Server Profiler by selecting menu option “Tools” -> “Database Engine Tuning Advisor” (shown in Pic. 4).

Pic 4 Starting from SQL Server Profiler

Pic 4 Starting from SQL Server Profiler

Screen Elements

The following Pic 5 shows Main screen elements in DETA in General tab

Pic 5. Screen elements - General tab

Pic 5. Screen elements – General tab

Session name

To begin with; whenever a DETA session is started, it is assigned a unique “Session name” (by default; it takes SQL Server user name and date time information; but we have changed it to “Analysis Test Tuning” as shown highlighted in top left part in Pic 5) which user can change to have a more convenient name for recalling later.

Workload

Next important thing is the workload selection. Workload denotes the actual area (a query or a batch of query or a table) that needs to be analyzed by DETA. There are three options available here:

  1. File: This option is used when we have identified a group of queries to be fine-tuned and saved those queries in a .sql file. We can also create a trace file by running SQL Server profiler during a specific set of operations if we are not fully sure which queries are being executed. In that case we can also use the .trc (trace file) as an input to the workload.
  2. Table: This option is used when we use the output of a trace to a trace table. After the tracing operation is complete we can use the output trace table as an input to the workload.
  3. Plan Cache: This option is generally used when we do not have a clear idea of what we need to tune and tend to depend on SQL Server for determining this. If we choose this option then SQL Server uses last executed 1000 events from its cache for analysis. But we may not be interested in all the events fired from every database in the server. So in that case, we can specify for which database(s) and optionally which tables we are interested to check in the area called “Select databases and tables to tune” (highlighted in Pic 5).
  4. Query: This option is only available if we initiate a DETA session by right-clicking a selected query in SQL Server Management Studio (as discussed in option 3 for opening DETA session in previous section).

Pic 5 is the output, when we used Option 3 [DETA startup options, mentioned above] to start a DETA session by selecting a specific query from Management Studio. This is the reasons options other than “Query” are disabled in pic 5.

So it is to remember that DETA can perform analysis on multiple tables from multiple databases if the workload is designed accordingly and the options are chosen accordingly.

Tuning Options tab

Limit tuning time

This is an option by which we can restrict the time limit for execution of the analysis to be performed by DETA. Ideally this should not be checked so that DETA can analyze the workload and recommend the appropriate actions. But this is a handy option when we are going to execute the analysis in production environment with a huge workload and we have a time-limit to perform the operation.

Pic 6 Screen elements - Tuning Options tab

Pic 6 Screen elements – Tuning Options tab

Physical Design Structures (PDS) to use in database

Under this section; we can restrict DETA regarding the recommendations that it is going to suggest after the analysis. DETA mainly recommends three types of physical design structures to be created – index (both clustered and non-clustered), indexed views and statistics. If we select “Indexes and Indexed views”; DETA will consider all kind of structures during analysis. But if we specify “Indexed views” then it will not consider any kind of indexes and it will consider only indexed views. Similarly if we choose “Indexes” (as shown in the screen shot in Pic 6); DETA will restrict its recommendations to creating indexes only (will not consider indexed views). Similarly we can further restrict which type of index DETA will consider using “Nonclustered Indexes”. The check box option “Include filtered indexes” is available only if we choose any of the options – “Indexes and Index views”, “Indexes” or “Nonclustered indexes”.

Partitioning strategy to employ

This option directs DETA whether it will consider partitioning while analyzing the workload. If we select “No Partitioning” then it will not consider any type of partitioning. If we select “Full Partitioning” then it will consider any type of partitioning. Similarly if we choose “Aligned partitioning” then recommendation may include aligned partitioning only. Choosing “Aligned partitioning” option enables DETA to recommend the partitions in such a manner so that the recommended indexes are aligned properly with the partitions resulting in best possible performance benefit.

Physical Design Structures (PDS) to keep in database

This option tells DETA whether it will consider dropping of existing database structures (e.g. indexes, indexed views or partitions) if it finds those to be of no use in present context. If we choose “Keep all existing PDS” (like what we did in the screen shot in Pic 6); DETA will not consider dropping any of the existing structures. Options “Keep indexes only”, “Keep clustered indexes only” and “Keep aligned partitions only” instructs DETA not to consider dropping of the respective structures but it can consider dropping others. “Do not keep any existing PDS” is the most liberal option where DETA is free to consider dropping of any of the physical structures during analysis. But DETA will never make recommendation of dropping a structure if it is found to be useful during the workload analysis. But it is to remember that DETA will never drop any database structure proactively during analysis; it will only make recommendations for dropping them.

We can go to the “Advanced Tuning Options” by clicking on “Advanced Options” button on the top right portion in Tuning Options tab (highlighted in Pic 6). The main points in “Advanced Tuning Options” (shown in Pic 7) are

  1. Define max. Space for recommendations

This option instructs DETA to restrict its recommended physical design structures maximum up to the number of MB’s mentioned here. If this option is omitted DETA uses its own method to calculate the maximum space for recommendations.

  1. columns per index

Default value for this is 1023; but user has the option to change this as well.

  1. Online index recommendations

This has three possible sub-options available

  1. All recommendations are offline (chosen in our case in Pic 7): it gives the best possible recommendations;
  2. Generate online recommendations where possible: this option instructs DETA to give precedence to the recommendations that allows the server to be online while generating the recommended T-SQL script;
  3. Generate only online recommendations: this is used to generate only the recommendations that allow the server to be online.
Pic 7. Advanced Tuning options

Pic 7. Advanced Tuning options

This was a brief introduction of DETA (DTA) for beginners. In our next article we will soon see DETA in action.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)