TSQLScripts.com: Script Details

TSQLScripts.com.

skip to the main content area of this page

Transact SQL scripts written by
DBAs and Developers for
DBAs and Developers.

To submit a comment about this script click here here or to submit your own script click here.

clear out DTA indexes and statistics
Description:
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. If you have implemented DTA-recommneded stats and not changed their name, this script will remove those too.
Submitted By: w

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