If there is one complaint business users may have about their production database, it is slow performance. Database professionals, therefore, often try to focus strictly on finding out and resolving the source of the issue in the database.
The Database Engine Tuning Advisor (DTA) is one of the best tools in this regard. It helps DBAs analyze workloads and uncover areas that can be improved. In this blog, we will discuss the working of this tool along with a few additional details.
What is the Database Engine Tuning Advisor?
It is a tool that assists with the optimization of SQL queries and was introduced in SQL Server 2005. Before it, SQL Server had a feature known as the Index Tuning Wizard.
The DTA is designed to assess a workload and provide recommendations to boost query performance. Some of its suggestions include:
- Making partitions
- Incorporating indexes
- Adding statistics (this helps resolve the issue of automatic statistics not getting created despite the auto_create_statistics option being ON)
Apart from locating every type of potential for enhancement, the Oracle Database Engine Tuning Advisor will build a T-SQL script for users to execute in order to carry out the recommendations made by it.
How to Launch the DTA for the Optimization of SQL Queries
You can commence its use through multiple methods:
- Open the Start menu, scroll to the SQL Server application group and look for the Performance Tools folder.
- If you’re using the SQL Server Management Studio application, you can select the tool from the Tools menu.
- The SQL Server Profiler also has this tool in its Tools menu.
- You can find it with the select analyze query in DTA on the SQL Server Management Studio Query menu. This also enables you to pass a T-SQL section to the tool for analysis.
- Open the Command Prompt, type “DTA -?” for a glimpse at the available alternatives.
Normally, you can create a workload by collecting multiple statements in a file or with the help of the SQL Server Profiler. An important point to bear in mind is that the workload being sent to the Advisor for evaluation needs to be representative of the average workload.
What the Database Engine Tuning Advisor Does
The Database Engine Tuning Advisor makes recommendations on the basis of the workload you send for analysis. Therefore, a limited workload will result in inadequate recommendations. The best thing to do is to collect the workload through the Profiler, save the results in a text file, and send the trace to the Advisor.
The SQL Server Profiler plays an important role in the optimization of SQL queries. It can be launched from its location in the SQL Server application folder (in the Start menu) or among the list of tools in the SQL Server Management Studio.
In case you want to know the properties of the new trace, follow these steps –
- Click on the dropdown list in the dropdown menu of the tool window (for the trace template) and select Tuning. The trace template gathers the events considered necessary by DTA.
- Select the ‘Save to file’ and type in the file name you want before passing it to the Database Engine Tuning Advisor once the trace is complete.