Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

Question

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases.

I have then passed this .trc trace file as a parameter to the database engine tuning advisor.

After running the DTA I get the recommendations:

How do I script out the recommendations?

I am using SQL Server 2005, and I don't seem to find any other way than scripting them individually which is overly time consuming.

Answer 1

Check the View Tuning Output:

6. If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

As always review and test the recommendations before blindly applying them to your PROD environment.

I would highly suggest to look at more sane tools like sp_BlitzIndex from folks at BrentOzar.com

Answer 2

Please note that a computer generating a list of "missing indexes" should not be swallowed whole. You will still need to decide which indexes to create, which recommended indexes are near duplicates of existing indexes, and how you should want to handle those issues.

It still requires you making a decision since the generated recommendations need some serious review before using them.

Bart Duncan produced a script that will list the "missing indexes" using the Dynamic Management Views (DMV) for indexes. This is not exactly the same as the Database Engine Tuning Advisor (DTA), but it is valuable.

I suggest that you compare the DTA and DMV recommendations on "missing indexes" for a sanity check.

Bart Duncan's query does generate the code to be used if you create the recommended indexes. (Slightly reformatted for readability.)

SELECT 
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * 
  (migs.user_seeks + migs.user_scans) AS improvement_measure, 
  'CREATE INDEX [missing_index_' + 
   CONVERT (varchar, mig.index_group_handle) + '_' + 
   CONVERT (varchar, mid.index_handle) +
   '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND 
      mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs 
      ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid 
      ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
     (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * 
         (migs.user_seeks + migs.user_scans) DESC

Likely many of the DTA recommendations are included in the missing indexes query. Whenever that works for you, then you have the scripts you need. Anything from the DTA that is not included in the DMV recommendation could be hand coded.