1

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.

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

  • FWIW, I've seen databases with hundreds of views created by DTA that didn't have indexes. Your guess is as good as mine as to why. – Erik Darling May 5 '18 at 20:36
  • These views dont have indexes on the views. I ’ve also queryd execution plans to see for any references to these views but can’t find any. – Addeladde May 6 '18 at 11:08
  • Then you can drop the views. You probably want to retain a script for them in case you discover they are used in some infrequent operation. – David Browne - Microsoft May 6 '18 at 14:16
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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.