I have encountered an issue when running the database tuning advisor against SQL Server 2005 that if the advisor errors out while performing its analysis, it will leave the hypothetical stats/indexes in your database. These should be removed as they may result in additional processing for SQL Server.
The below statement will generate the scripts to drop these stats/indexes.
SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] +
']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE
'_dta%'
ORDER BY i.name
If you receive the below error change the script to drop index:
Msg 3739, Level 11, State 1, Line 1
Cannot DROP the index because it is not a statistics collection.