The Database Engine Tuning Advisor (DTA) is a handy little tool included with SQL Server Management Studio that can be used to help identify performance issues with ad hoc queries and stored procedures. Specifically, this tool looks to see if adding indexes, indexed views, statistics or partitioning can improve the run time of a particular query or batch of queries. The beauty of the DTA tool is that after it analyzes a particular query or batch it will not only recommend the best course of action, it also provides the necessary scripts to create the recommended indexes or statistics. Though this tool is not a catch all, it does provide an excellent first step when it comes to performance tuning and identifying bottlenecks within SQL Server query performance.

The example outlined below will walk you through the basic steps of using the Database Engine Tuning Advisor to analyze and provide recommendations for improvement against a particular query.

The first step is to launch the Database Engine Tuning Advisor in SQL Server Management Studio. There are a couple different ways to do this. One option is to select it from the Tools menu (Tools>>Database Engine Tuning Advisor).

The other option, and my personal preference, is to launch it directly from the query window containing the query or execute statement that you are wanting to analyze.

This can be done by simply right-clicking anywhere in the query window and selecting Analyze Query in Database Engine Tuning Advisor. I prefer this method because it requires less configuration as the DTA already knows what you are wanting to analyze based on the query or stored procedure being called in the query window.

Once the DTA window is displayed you’ll notice a few things. First, you can provide a Session name. Each time you launch DTA a new session is created and saved to the server. By default the name will correspond to your login and the date the session was created. So, for this example, you’ll see JWorthen 1/26/208 9:26:53. If you would prefer to provide a more specific name for the session, you can do that here.

The next thing you may notice is the list of databases. Each database on the server will be displayed but only the database to which you were connected in the original query window will be selected.

The last thing you’ll want to review before kicking off the analysis are the available tuning options. To do this, click the Turning Options tab at the top of the window.

These options allow you to specify the type of recommendations that the DTA will provide and the method by which it will analyze the current environment. In most cases you can just leave the default setting configured. If you would like to know more about these options, Microsoft provides a good overview here.

Once you’re ready to kick off the analysis click Start Analysis.

The amount of time it takes for the Database Engine Tuning Advisor to run is dependent on the query being analyzed. As it is running you will see the progress displayed.

Upon completion you will see a list of recommendations on the Recommendations tab. The screenshot below shows a list containing two recommended Indexes and five recommended statistics with an estimated improvement of 95%.

It is important to note that just because the DTA recommends adding multiple indexes does not necessarily mean you should deploy them all. Before publishing any new indexes to your SQL Server database you should review each index and gauge for yourself the potential benefit.

Fortunately DTA makes this relatively easy. If you scroll over to the right on the Recommendations tab to the Definition column you will see a link to the recommended object. By clicking the object the necessary CREATE script will be provided and you can simply copy and paste this to a new query window for review and eventual publication.

The steps outlined above are a basic overview of analyzing query performance using the Database Engine Tuning Advisor. As mentioned previously, you need to be careful when implementing recommendations provided by the analysis. Simply adding a new index each time the DTA recommends it is not a good idea. It’s vital to understand the potential benefits and pitfalls of each recommendation. In some cases simple trial and error can be used to determine which recommendations to implement and which to ignore. Just make sure to keep track of any objects you create so that they can be removed later if needed.