What is a Database Engine Tuning Advisor (DTA)?
Changing database design to a proper one makes it to perform better. One of the performance-critical areas of databases is proper table indexing and their usage.
Database Engine Tuning Advisor helps to decide if existing indexes are correct and if new indexes are required.
This tool does not require an expert level on SQL Server.
However, blindly doing what is suggested by this tool can also negatively impact SQL Server’s performance.
How to start using it?
There are several ways how to open DTA. You can open it:
- Via Windows Search, by typing “tuning advisor” and choosing from suggested options.
- By running from a command prompt (exe).
- From SQL Profiler (clicking Tools and then Database Engine Tuning Advisor).
- From Query in SQL Server Management Studio (highlight required Query and select Query and then Analyze Query in the Database Tuning Advisor).
- On SQL Server Management Studio (Choosing Tools and Database Tuning Advisor afterward), like it is shown in Figure 1 below.
When DTA is opened for the first time, it will require sysadmin privileges.
Later on, database owners can do this. It is because new system databases will be created during the very first start of this tool.
Creating the first session
When the tool is opened, and a connection to a server is established, you should see windows like in Figure 2.
Now you can start to choose a workload and objects that need to be tuned. It is also essential to use a meaningful session name – for easier finding it later.
The workload can be chosen out of different options – from a trace file or a table, plan cache, or even from the Query Store. You can create a query having a SELECT statement that needs to be improved, save it as .sql file and choose by using the File option.
Workload view also depends on how DTA was launched – if it was started from Query window in SSMS (SQL Server Management Studio), Query radio button will be shown, and Table and File buttons cannot be used.
You also have to choose a database Workload Analysis setting and databases that need to be tuned.
When the database for tuning is selected, specific tables may be selected too, like in Figure 3.
When workload is defined on General Tab, choose the Tuning Options tab like in Figure 4.
In this tab, firstly, limiting tuning time can be checked, and the start date and time can be chosen to define length.
Longer Database Tuning Advisor runtime can provide more accurate results.
Then Physical Design Structures and Partition strategy can be chosen, so it is included in DTA’s consideration. Tick boxes if you think that DTA may recommend columnstore and filtered indexes. However, be careful choosing table partitioning because it may fit one database design structure and may not benefit another.
There is also an area for Physical Design Structures to be kept in the database; using this will change the number of choices from improvements provided by the DTA review.
If it is necessary, the Advanced Options button can be used. There you can dedicate maximum space for recommendations and amount of columns that can be included in an index. However, limiting space for recommendations might restrict accurate analysis results. Also, plan cache events can be included as an option or not.
Online index recommendations can be set to be done as offline, only online or moderate operations. If you do not know which one to choose – take the third option; it is the safest one (Generate only online recommendations).
When all options are in place, a session can be started by using the Start Analysis button.
Analysis Results
During analysis, data is being stored in the msdb database, and progress is shown on the Tuning Progress window, like in Figure 5.
When a session ends, a list of recommendations will be provided. An example of index recommendations is in Figure 6.
Even if a set of scripts will be suggested, that might improve performance, and some reports will be generated as well.
These will include data about existing database design and also recommendations.
Possible reports and where to find them are shown in Figure 7.
This topic might look very straightforward and simple.
However, always test any changes that are suggested by the DTA tool.
As told at the beginning of this article, some recommendations might negatively impact if they are applied blindly and without appropriate testing first.
