What is the best way to tune the indexes on my production database? I normally use the following query :

    user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
    mid.[statement] AS [Database.Schema.Table],
    sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
        ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

This gives me a list of missing indexes and I used to add them and re-run the query a few hours later to see the result. Will it be better for me to capture a workload table using SQL Profiler and then use the Database Tuning Advisor?

Also if I use the tuning advisor does it re-run all the queries in the workload file e.g. if there was an insert statement captured will it re-insert the record on the database I run the analysis against? Or does it just analyze the insert statement? I could not find any information on how that data is treated and if it would be safe to run the analysis to against my production database after hours? (my worry is that the analyzer changes the data in my production database).

An alternative would be to backup my production database in the morning, capture the workload file during the day then restore the production database on my test environment together with the workload file and execute the DTA overnight on my test environment. Is this a good approach?

  • 1
    I highly recommend trying out Qure Analyzer - capture a server-side trace (not using Profiler) and then analyze it with this tool. The Qure Optimizer product does a lot more like what you're looking for, but it's not free. I tend to ignore the DTA / missing index group recommendations unless I've really given a fully representative workload, otherwise you create indexes that benefit the wrong part of your workload. You also have to pay very close attention to suggestions of duplicate indexes. – Aaron Bertrand Oct 3 '12 at 14:10
  • Thanks for your response Aaron, I will take a look at Qure. If any database needs tuning it is this one I have started working on. I am excited that Qure does a lot more than just the indexes we have numerous stored procs that have problems too. – Robert Brown Oct 8 '12 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.