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.
Best Answer
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