Posted by Tommy Hayes on 09/27/07 15:58

Hello all,

I want to use the SQL Server 2005 Tuning Advisor on our database, and
I'm hoping someone here can just confirm the steps for me. We have a
10GB database that has a number of applications hitting it constantly,
all performing many SELECTs, INSERTs and UPDATEs. Are the following
the steps I need to take?

1. Stop all the applications hitting the database.
2. Make a backup of the database.
3. Start the profiler on the original database.
4. Turn on all the applications again.
5. Wait for a time before turning off the profiler and saving the
profile.
6. Run the tuning wizard against the backup database using the saved
profile.

Do I have to do this every time or am I missing something obvious? I'm
under the impression I need the backup because our database has a high
volume of INSERTs and UPDATEs so I have to make sure the state of the
database matches the state of the profile.

As a matter of interest, why is it not possible for the tuning wizard
to analyse the database using the *current* activity? Why does it have
to be a 'pre-recorded' workload?

Many thanks,
Tommy.

[Back to original message]