pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Thursday, May 21, 2009

clear out DTA indexes and statistics

Here's a helpful script I keep around in my portable HD. 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 these active indexes and stats from your database. Uses the is_hypothetical flag to find indexes. Uses the _dta prefix to find stats. This assumes that when you implement DTA-recommended stats, you change their name (always a best practice). If you have implemented DTA-recommneded stats and not changed their name, this script will remove those too.


WHILE EXISTS (SELECT * FROM sys.indexes WHERE is_hypothetical = 1)
BEGIN
DECLARE @sql varchar(max)
SELECT @sql = 'drop index ' + indexname + ' on [' + tablename + ']'
FROM (
SELECT TOP 1 indexname = i.name, tablename = o.name
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE i.is_hypothetical = 1 and o.type = 'u'
) x
SELECT @sql
EXEC (@sql)
END
GO

WHILE EXISTS (SELECT * FROM sys.stats i WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE '_dta%' and user_created = 0)
BEGIN
DECLARE @sql varchar(max)
SELECT @sql = 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] + ']'
FROM sys.stats i
WHERE
OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND
i.[name] LIKE '_dta%' and user_created = 0
SELECT @sql
EXEC (@sql)
END


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

No comments:

Post a Comment

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.