We have a concept which assumes that we will change original table name with a view for some other purposes. This very large table (ca. 80mln rows) is lying in OLTP system but will not be changed straight forward but by triggers. When view comes it will filter data from underlying table with only one conditions in WHERE clause. Unfortunately this column is not very selective. Currently we have many indexes on that table, and many are used in the query hints during daily work. I have a questions what would be a drawback of such action? During some test I noticed that: - query index hints are not used when are used with a view - view's WHERE condition enforces index choice which is not exactly the most optimal one Do you have any other negative experiences?
Sounds like you're starting to dig a hole. If you have lots of indexes, but in order to get them to work with the queries you're writing you're using hints, I suspect, rather strongly, that there are issues with those queries. I would focus first on why you need so many hints in your queries. You should be using very, very few. The reason that a hint isn't working on the view is likely because the optimizer recognizes that it can't use the hint to satisfy the query because of the WHERE clause in the view. How often is the data in these tables updated? If it's only occasionally, or at scheduled times, I'd consider making a materialized view instead of a standard one. This will literally change the storage of the system to only hold what you're trying to get into the view. And if, that WHERE clause isn't terribly selective within the regular data set, at least you've moved all queries from that non-selective data set to a different location, which will help with blocking and contention on the original table. But this is only possible if you don't update the data too frequently. I still think the problems are more fundamental though.
Grant Fritchey is absolutely right, but to expand slightly on what he said: Hints should work quite nicely inside of views, unless there is a different reason that SQL Server would disregard them. But as Grant said hints should generally be used sparingly and the only one I use on a regulare basis is [NoExpand] One other thing to remember is that indexes can reduce write performance. Normally this isn't a major factor since reads will generally outnumber writes and the trade off is well worth it, but if you start having many indexes on a large table that gets written to frequently it may be something to look at. :