Editor’s Note: In the first of this four part series, I gave an overview of the tools and techniques of query optimization in SQL Server. In the second part, I talked about the process of displaying and interpreting query execution plans. I also showed how to generate query execution plans using SQL Server Management Studio, SHOWPLAN_TEXT and SHOWPLAN_ALL. In the third part, I talked about the index-related dynamic management functions (DMFs) and views (DMVs). In this fourth and final part, I will talk about how to create a workload and use the workload and Database Engine Tuning Advisor to tune a database indexes.
In my previous article, I talked about the index-related dynamic management functions (DMFs) and views (DMVs) that are helpful to find potential missing indexes for your database queries. However, the missing indexes recommendations provided by index-related dynamic management functions (DMFs) and views (DMVs) is recommended as a general guideline only. You shouldn’t use the information for fine-tuning a database. This is because the identified missing indexes are the ones that the SQL Server optimizer identified during query compilation, and are targeting a specific query. Therefore, to create an optimal set of indexes, indexed views, or table partitions, Microsoft recommends submitting your workload for query optimisation to the Database Tuning Advisor for further evaluation.
What is Database Engine Tuning Advisor?
Database Engine Tuning Advisor is a tool that uses a SQL Profiler trace file to make recommendations about optimizing a database.
Query performance optimization can be difficult without a full understanding of the database structure and the queries running against the database. The DTA can make this task easier by analysing the current query cache or by analysing a workload of Transact-SQL statements to make recommendations about database physical design to fine tune your database.
Database tuning
You use the Database Engine Tuning Advisor to analyse database activity based on a workload file or table. The Database Engine Tuning Advisor returns suggested index changes. The better (more representative) the workload, the better the index tuning suggestions.
General procedure
The general procedure for using the Database Engine Tuning Advisor to fine-tune a database is:
- Generate a workload.
- Run the Database Engine Tuning Advisor or dta (command-line tuning utility).
- View the tuning output.
- If desired, run an exploratory analysis.
- Implement the recommendations.
Because both your database environment and business requirements can change over time, you should periodically run through the tuning process. General procedures are covered in this article. For more detailed information, refer to the “ Start and Use the Database Engine Tuning Advisor and Tutorial: Database Engine Tuning Advisor articles in SQL Server Books Online.
Workload
Your first step is generating a workload. You can use a trace file, trace table, or embed the workload in an XML input file. The advantage of using an XML input file is that you can assign weights to events. Otherwise, they are all treated the same. Detailed information about using an XML input fileis beyond the scope of this article. To create a workload:
- Launch SQL Server Profiler.
- Create a new trace using the tuning template.
- Configure the trace to save to a file or database table.
- Capture system activity.
- Save the workload.
This gives you a workload file or table you can import into the Tuning Advisor to fine tune your database.
Database tuning with the Database Engine Tuning Advisor
You use the Database Engine Tuning Advisor or the dta command to tune the database. To use Database Engine Tuning Advisor:
- Launch Database Engine Tuning Advisor.
- Select the database(s) and (optionally) table(s) to tune.
- Open the workload file or table.
- Set tuning options.
- Run the analysis.
You use the Tuning Options tab to set tuning options. These include:
- Physical data structures (indexes, indexed views, etc.) to use.
- Partition options.
- Existing physical data structures to keep after tuning.
- The Tuning Advisor reports its tuning recommendations when finished.
Database tuning with the dta command
The dta command is an operating system command-line command. It performs the same function as the Database Engine Tuning Advisor, analysing a workload and returning tuning recommendations. You can specify the target database(s) and table(s). As workload input, you can specify a:
- Workload file
- Workload table
- XML input file
You can have the command report tuning recommendations or apply the recommendations automatically without prompting you.
Viewing tuning output
The Database Engine Tuning Advisor reports recommendations automatically. You can also view the current recommendations at any time after running the analysis by activating the Recommendations tab. For a previous session, you select the session in the Session Monitor pane and activate Recommendations. You activate the Reports table to view a tuning summary report.
You can save the recommendations for later review. Before starting the analysis, in the General page of the Tuning Advisor, check “Save tuning log.” You can export the session results to an XML file after running the analysis. Choose File, Export Session Results, then select the destination folder and enter the XML file name. The Tuning Advisor saves the results as a well-formed XML document.
Exploratory analysis
Exploratory analysis is ‘What if’ analysis, trying different options and parameters and comparing the results. In essence, it is running multiple analysis sessions based on the same workload. To evaluate any tuning session:
- Activate the Recommendations tab and remove any recommendations you don’t want considered.
- Choose Actions, Evaluate Recommendations to launch a new tuning session.
- Name the session and set any tuning options.
- Click Start Analysis.
You can run another analysis using the same data, but with different analysis parameters.
Applying recommendations
The usual goal of running the tuning advisor is generating a set of tuning recommendations you can apply to your database(s). The dta utility can apply recommendations automatically. When using the Database Engine Tuning Advisor, you must apply the recommendations manually. To apply the recommendations:
- Select the session results you want to apply.
- Choose Actions, Apply Recommendations.
- Select either ‘Apply now’ or ‘Schedule for later’
- Click OK.
If you choose “Schedule for later,” you must set the schedule (the date and time) for applying the recommendations. It might take multiple attempts before you have an appropriate set of recommendations. Don’t apply the changes until you have the tuned the database to your specific requirements.
What Next?
Verifying optimization
The final step in the optimization process, whether optimizing the hardware platform, database server, database, or application, is testing and verifying that your changes have the desired result. You run the same tests with the same parameters after optimization as your ran before.
Further Reading:
- For additional information about views and functions that return index information, see the following SQL Server Books Online article “ Index Related Dynamic Management Views and Functions .”
- For more information about Database Tuning Advisor, see Start and Use the Database Engine Tuning Advisor and Tutorial: Database Engine Tuning Advisor .