SQL Database Engine Tuning Advisor

One of the most underestimated tool that I have seen in SQL today is the database engine tuning advisor. When the tool is put in the right hands, it can decrease database select and statement times by up to 80%. Recently, I ran it against a larger project involving real estate. The database blew up as soon as images and properties came flooding in. The application we had written for the company started at loading 1000 properties in about a second to loading 20000 in about 10-15 seconds. This obviously was unacceptable, so we turned to this tool.

Here are some instructions and/or keys about using the tuning wizard.

First, you will need to create a trace file which demonstrates average database usage. Typically, this file should be between 1-5 minutes of database usage. But it can be as long or as short as you want, depending on your database capacity.

This link will help you create a workload/trace file for use in the tuning wizard:

Finally, load in the trace file into the wizard and execute the report. This will crunch a bunch of numbers, and finally pop out recommended tuning steps. In the top there is an action for applying all recommended tuning.

For a full walk through of this information, please visit Microsoft’s tutorial:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>