Database Tuning Advisor bug

I love the ability to profile SQL Server activity and then run the database tuning advisor.

Unfortunately, sometimes I start this DTA advisor, and I let it run for a day, and it doesn’t complete.  Often, when I kill DTA in Task Manager, I have noticed that this leaves orphaned Indexed Views left in my database.

This can cause unintended problems.  Indexed views are extremely powerful, but the bottom line is that having 100 orphaned indexed views is going to slow down your database (it actually might speed it up in some situations, but not likely- 100 indexed views is just ridiculous).

I use a script like this in order to clean up these fragments

select ‘drop view [‘ + name + ‘]’
From sysobjects
where name like ‘%dta_mv_%’

This is just like any other situation where I use Dynamic SQL.. Write a script that generates the DROP VIEW ______ statements, instead of doing this by hand:

drop view [_dta_mv_0]
drop view [_dta_mv_1]
drop view [_dta_mv_2]
drop view [_dta_mv_3]
drop view [_dta_mv_4]
drop view [_dta_mv_5]
drop view [_dta_mv_6]
drop view [_dta_mv_7]
drop view [_dta_mv_8]
drop view [_dta_mv_9]
Now of course, you can copy the results from this script into a new query Window and execute the results in order to drop the views.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.