Views created by SQL server Database Tuning advisor

Question

Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy.

I implemented the suggestions, and it created several views with names like _dta_mv_xxx

I have never written a query against those views, and I wonder if they are used in any way by SQL-Server. I can't see any indexes on the views either.

Is it safe to delete those views or will it impact performance?

The database is around 400 gb so working with creating and recreating indexes is slow.

The database is SQL Server 2016 Enterprise.

Answer 1

If the views were created by the Database Tuning Advisor, then they are probably indexed views. The Tuning Advisor "recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning. " An indexed view can be used even if you don't explicitly query it because of Indexed View Query Rewrite: "The view does not have to be referenced in the query for the optimizer to consider that view for a substitution. " -docs

So you should include them in a (periodic) evaluation of your indexing strategy, looking for both missing and unused indexes. You can find lots of DMV queries for both, based on sys.dm_db_index_usage_stats sys.dm_db_missing_index_group_stats

Answer 2

The short answer is that it's impossible to tell, but it's not terribly likely.

Views without indexes are just obfuscated queries. Unless you've written queries directly against those views, there's generally no advantage for the optimizer to decide to use them.

The safest thing to do would be to

• Script their definitions
• Script the DROP commands you plan to use
• Check them into change management or source control

That way, if you drop any, and something odd happens (like queries failing), you can add them back. If you have a dev/QA environment, make the changes there first and let them burn in for a week.

I'm not sure why you brought up index creation speed. If you're under the impression that creating a view that isn't indexed requires some physical structure gets built, it doesn't.

On the flip side, the views created by DTA are often created with the SCHEMABINDING option, which makes altering the underlying tables and columns that they touch throw errors that are confusing to track down.

Hope this helps!