Tuning SQL Server



By:

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


Last Update: 5/27/2011






Comments For This Article





download














get free sql tips
agree to terms