3

I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an actual table. How can that be possible?

Of course, the physical table is being used but the actual view it is not!

The view is something like this:

CREATE VIEW [ws].[MyVIEWTable]
WITH SCHEMABINDING
AS
SELECT col1, col2
  FROM [dbo].[MyTable]

Why does the SQL Server engine use the view to retrieve data and not the actual physical table, which on this case would be dbo.MyTable?

2
  • Can you post the actual execution plan ?
    – Kin Shah
    Mar 26 '15 at 19:24
  • Thanks for reply. Sorry, I cannot, due privacy. But my question is very simple. The original plan flags a missing Index on ws.MyViewTable, that's it. But there is no ws.MyVIEWTable in the store procedure.The view references an actual physical table that it is actually referenced.
    – Jose
    Mar 26 '15 at 19:30
5

So it's an indexed view - SQL Server can automatically choose to use an indexed view if the base table is referenced and the indexed view can (better) satisfy the query.

From MSDN (emphasis mine):

A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the query optimizer estimates that using the indexed view provides the lowest cost access mechanism, the query optimizer chooses the indexed view [...]

Also, as you've seen, the missing indexes algorithm might nudge you and say, "Hey Jose, if you added this non-clustered index to the indexed view, I might have been able to use the index on the view instead, which would have been more efficient for this query." Again, this correlation can happen even if the view isn't explicitly mentioned in the query.

Generally, unless you are throwing indexes on all of your views because you believe they are some magic performance silver bullet, accessing the indexed view will be better than accessing the base table, because - again generally - the indexed view should be a lot smaller than the table on which it is based.

3
  • As always, you're the man, Aaron! ;-) but you could have saved me 40 min of Google time: technet.microsoft.com/library/Cc917715 You are right, and I actually did not know that.
    – Jose
    Mar 26 '15 at 19:44
  • 1
    @Jose I could have saved you 40 minutes of Google time? Not sure I understand. You only asked the question 20 minutes ago. Mar 26 '15 at 19:44
  • But I spent more time researching before posting here ;-) ... Thanks again.
    – Jose
    Mar 26 '15 at 19:52

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.