Database Tuning Advisor

Microsoft SQL Server 2008 (and 2005) has a tool that analyzes the performance of your databases. The tool is called the Database Tuning Advisor (DTA). To use DTA you identify the set of SQL statements – queries, stored procedures, views, etc. and include them into a workload. DTA analyzes the workload and makes recommendations to improve the performance. Some of the recommendations may include creating, modifying, or deleting clustered indexes, non-clustered indexes, indexed views, and partitioning.

DTA has two interfaces – a command line interface – dta.exe and a GUI tool. To use the command line interface scripts with the different commands have to be run and interpreted by the DTA. To run DTA the user has to be logged in as a member of the sysadmin group. In the recommendations DTA will NOT recommend dropping referential integrity indexes – the primary key and foreign-key relationships.

If your company is using Microsoft SQL Server 2008 on databases with lots of transaction activity, it would be a good idea to have your database administrator take some time and use the Database Tuning Advisor. Microsoft SQL Server 2008 databases are a key component to your IT infrastructure and you want to ensure you have the highest performance possible.

Visit our site at DAPCO Software Engineering, Inc. for more information on this or other related topics.