Database Engine Tuning Advisor (DTA, _dta) errors out leaving hypothetical stats/indexes

by Cliff 31. July 2007

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. 

Related posts

Comments are closed

Cliff Gray's Info

Cliff Gray
Developer/Founder GrayTechnology.com.

E-mail me Send mail

Authors

Calendar

<<  September 2019  >>
MoTuWeThFrSaSu
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

View posts in large calendar

Blogroll

Download BlogEngine.NET

Download at CodePlex

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2019

Subscribe