mssqltips logo

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools