2

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.

enter image description here

After running the DTA I get the recommendations:

enter image description here 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.

0
8

Check the View Tuning Output:

  1. 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

4

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.

1
  • very useful, great contribution, cheers, it is a very good point to compare the DTA and DMV recommendations as you suggested May 10 '16 at 14:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.