What is the best way to tune the indexes on my production database? I normally use the following query :
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.user_seeks,
migs.avg_total_user_cost,
migs.avg_user_impact
FROM
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?
