Undo changes made by SQL Server Database Tuning

I am not sure if this question can be asked here or serverfault. I开发者_如何学Python was wondering...Is there is a way to quickly undo the changes made by SQL Server 2005 Tuning Advsor other than recovering from a backup?

There is a way as long as you haven't renamed them usually Database Tuning Advisor generated objects are prefixed with _dta

so you can view them by running this query

FOR indexes

SELECT        
*
FROM            
sys.indexes where name like '_dta%'

FROM Stats

SELECT
*
FROM 
sys.stats where name like '_dta%'

And from there I guess you would know what items to drop

There is no official reference to UNDO this operation, just as there won't be an official recommendation for recovering from DROP DATABASE. A responsible DBA just doesn't do these things.

If you've just done it and were trigger happy, you most likely left the names as the defaults - that helps a lot. Raymund's answer is almost there. But if you've done it over time, then you would not want to UNDO all the good stats and indexes. The 2nd piece of the puzzle is to check when the indexes were last updated. If you have just done it a few minutes ago, which is a good timeframe to UNDO within, check this query to get indexes and statistics that have just been rebuilt.

  SELECT object_name(object_id) tablename,
         name indexname,
         nullif(name,name) statsname,
         STATS_DATE(object_id, index_id) lastupdated
    from sys.indexes
   where STATS_DATE(object_id, index_id) >= dateadd(hh,-1,getdate())
  -- and name like '_dta%'
   union all
  SELECT object_name(object_id) tablename,
         nullif(name,name) indexname,
         name statsname,
         STATS_DATE(object_id, stats_id) lastupdated
    from sys.stats
   where STATS_DATE(object_id, stats_id) >= dateadd(hh,-1,getdate())
  -- and name like '_dta%'
order by lastupdated desc

This filters the list down to only the indexes that have been UPDATED in the last hour. However, auto-stats is normally on by default, and also rebuilds statistics occasionally, so you wouldn't want to drop all the indexes shown. Uncommenting and name like '_dta%' would be a start, unless you renamed them - in which case surely you can recall what you named them to?

I frequently use psuedo-dynamic SQL in order to generate the SQL Statements that I need. this will drop all indexes that start with the prefix _dta.

SELECT
'drop index ' + OBJECT_NAME(object_id) + '.[' + name + ']' FROM
sys.indexes where name like '_dta%'

paste the results from this into a new window, and then I'd double-check each index manually before dropping.

likewise, you can do the same thing for dropping statistics

SELECT
'drop index ' + OBJECT_NAME(object_id) + '.[' + name + ']' FROM
sys.stats where name like '_dta%'