clear out DTA indexes and statistics

Update: There's a saying that when you look at your old code, you should feel you want to improve it? This is one of those. WDA 20180927

Here's a helpful script I keep around in my portable HD toolbox. When the Database Tuning Advisor fails or is aborted for whatever reason, it can leave behind its hypothetical indexes and stats. This script will remove script out dropping these active indexes and stats from your database.

Uses the is_hypothetical flag to find indexes, this is reliable. Uses the _dta prefix to find stats, this is less reliable because it depends on naming convention. This assumes that when you implement DTA-recommended stats, you change their name (always a best practice). If you have implemented DTA-recommended statistics object and not changed their name, this script will remove script out dropping those too.

SELECT 'drop index [' + i.name+ '] on [' + schema_name(o.schema_id) + '].[' + object_name(i.[object_id]) + ']'
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE 1=1
and o.is_ms_shipped = 0
and o.type = 'u'
and i.name is not null
and i.is_hypothetical = 1  --find stats from the DTA

select 'drop statistics [' + schema_name(o.schema_id) + '].[' + object_name(i.[object_id]) + '].['+ i.[name] + ']'
FROM sys.stats i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE 1=1
and o.is_ms_shipped = 0
and o.type = 'u'
and i.[name] LIKE '_dta%' --find stats from the DTA

See also:
I originally posted this on tsqlscripts.com (drink!)
http://www.graytechnology.com/Blog/post.aspx?id=e21cbab0-8ae2-478e-a027-1b3b14e7d0b9
http://weblogs.sqlteam.com/mladenp/archive/2007/07/16/60257.aspx
http://www.sqlservercentral.com/Forums/Topic398549-360-1.aspx


"The great tragedy of science - the slaying of a beautiful hypothesis by an ugly fact." -T.H. Buxley