SQL Server 2008 : Performance Tuning - Working with Database Tuning Advisor

2/18/2011 7:44:08 PM
The Database Tuning Advisor (DTA) is a tool provided with SQL Server to help Developers and System Administrators improve query performance by optimizing physical design structures such as indexes, indexed views, and table partitioning. DTA can be launched from the same folder location as SQL Server Profiler. Alternately, DTA can be started from within Management Studio, where it is located on the Tools menu.

In most cases, DTA speeds up the query it’s tuning but adding indexes can have an adverse effect on inserts and deletes. Be sure to review and test the suggestions it offers.

Head of the Class...: Implementing DTA Recommendations

DTA is a useful tool in reviewing workload and making recommendations that can improve performance. However, DTA is no silver bullet. Always review recommendation and consider the impact of maintaining indexes once they’ve been created. For example, with one test workload, DTA could make a 90% performance improvement with 10 new indexes; however an 85% improvement could be gained with a single index—in this case we might elect to ignore the nine additional indexes.

There are two ways to use DTA: either as a workload file or as a tool to tune a specific query. Workload files can either be a SQL Profiler trace or a.sql file containing a workload (queries). To tune a specific query, load the query in SQL Server Management Studio, right-click the query, then select Analyze Query in Database Tuning Advisor from the menu (see Figure 1).

Figure 1. Provide a Specific Query to DTA for Analysis

Capturing a Workload

One of the most useful features of DTA is workload analysis. This gives developers and administrators the ability to capture a trace from a production system and analyze it to identify changes that could improve the performance of the workload. The effort required to capture and use DTA to analyze the workload trace is relatively little compared with the potential performance gain. Additionally, the right-click analyze query - DTA function provides developers with opportunity to evaluate a query prior to changing the query in production – actively looking for missing statistics or indexes.

To achieve the best results, a workload trace should contain activity from the live application where optimization is to be performed. Using the Tuning trace template within SQL Profiler provides sufficient detail to enable analysis with DTA as shown in Figure 2.

Figure 2. SQL Profiler Trace Using Tuning Template

If you prefer to define a trace manually, include the following events:

  • RPC:Completed

  • SP:StmtCompleted

  • SQL:BatchCompleted

Trace files can be provided to DTA as .trc files (the native trace output files from SQL Profiler) or from a table within a database (if you’ve traced from SQL Profiler directly to a table). The performance burden on the live system is minimized by tracing to a flat file.

Performing Analysis with DTA

Once the workload has been captured to a trace file or a specific query has been identified for review, launch DTA and connect to the SQL Server instance where the database to be analyzed resides.

Configuring & Implementing...: Use a Test Server for Tuning

During the tuning process, DTA can make multiple calls to the Query Optimizer within the database engine to determine benefits and impacts of physical design structure changes to query plans. If you run DTA against a production server, performance could be affected during analysis. It’s recommended that you run DTA on a test server from a restored database backup to avoid any performance degradation.

Use the General tab within DTA to locate the workload file (i.e., the file containing tsql workload or the trc file created using SQL Profiler). If there are multiple trace files (created using the file rollover option within SQL Profiler), select the first file, and then subsequent files will be read in sequence automatically. Next, select the checkbox alongside the target database for tuning. If you’re tuning a specific query or .tsql workload file, you can save time during analysis by narrowing the selection of tuning tables within the database. If the workload file is a SQL trace, this may not be possible since there’s no easy way to determine the tables involved in the contents of the trace workload. In this case, run DTA against the entire database (see Figure 3).

Figure 3. Select the Workload File and Target Database for Tuning within DTA

Select the Tuning Options tab to review a number of alternatives. Click the Limit tuning time check box and indicate a stop time of one hour in the future to be certain you receive results within a reasonable period of time (see Figure 4). Additionally, review the three categories that follow in the window to indicate your objectives and ensure any restrictions within your operating environment can be satisfied by the recommendations generated from DTA. To continue, click Start Analysis on the menu bar.

Figure 4. Review Tuning Options within DTA

During analysis, the Progress tab displays each step undertaken by DTA. If there are any errors reading the workload files or missing events in the case of SQL traces, these are displayed with hyperlinks for further information on remediating the problem.

Following completion of your analysis, the Recommendations tab is displayed. Note at the top of the recommendations screen, DTA makes an estimation of the improvements that could be achieved by implementing the recommendations. In Figure 5, there is a combination of indexes and statistics recommended by DTA: the default DTA object naming convention shows new indexes with prefix _dta_index_<objectidentifier> and statistics within prefix _dta_stat_<objectidentifier>.

Figure 5. DTA Displays Recommendations Following Successful Analysis

Finally, select the Reports tab for a summary of the analysis including details of the recommendations (such as information about proposed indexes including sizes, definition, and number of rows). Additionally, there are reports available to summarize events within the workload file such as tables and column access reports.

Implementing DTA Recommendations

Once DTA has run the analysis, its recommendations can either be applied immediately or scripted to be tested and applied at a later date. Script recommendations by selecting Save recommendations from the Actions menu.

  •  SQL Server 2008 : Performance Tuning - Tracing with SQL Profiler
  •  SQL Server 2008 : Performance Tuning - Working with Query Execution Plans
  •  Externalizing BLOB Storage in SharePoint 2010 (part 2) - Installing and Configuring RBS & Migrating and Moving BLOBs Between BLOB Stores
  •  Externalizing BLOB Storage in SharePoint 2010 (part 1)
  •  Sharepoint 2010 : Managing SharePoint Content Databases
  •  Sharepoint 2010 : Maintaining SQL Server in a SharePoint Environment
  •  Sharepoint 2010 : Monitoring SQL Server in a SharePoint Environment
  •  Database Availability Group Replication in Exchange Server 2010 : Load Balancing in Exchange Server 2010
  •  Database Availability Group Replication in Exchange Server 2010 : Comparing and Contrasting DAG Versus CCR/SCR/SCC
  •  SQL Server 2008: Managing Query Performance - Forcing Index Seeks
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us