Tuning SQL Server

By: Tibor Nagy

Overview

The SQL Server databases require fine tuning of queries from time to time to optimize performance. There are several tools that can be used for this, but one built-in tool which makes it easier is the Database Engine Tuning Advisor.

Explanation

There are two different ways to use the Database Engine Tuning Advisor: you can enter your script in the SQL Server Management Studio Query Editor window and fine tune it or you can create a workload file using the SQL Server Profiler. Also you can use the dta utility from a command prompt instead of the graphical user interface.

The result set can contain the following recommendations:

• new or modified indexes
• indexes that should be dropped
• new or modified indexed views
• partitioning

The report will contain an analysis of implementing the recommendations such as table and column access statistics, disk usage of new indexes and expected performance improvements.

The tool fully supports XML input files.

Additional Information

• Introduction to the SQL Server Database Engine Tuning Advisor (DTA) for Performance Tuning article on MSSQLTips
• Other tools that are helpful are:
• Query Plans
• Server Side Traces
• Profiler
• Read these tips for more about performance tuning