SQL Server execution plan using a view, but no view is inside the stored procedure

Question

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?

• Kin Shah: Can you post the actual execution plan ?
• Jose: 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.

Answer 1

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.

• Jose: 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.
• Aaron Bertrand: @Jose I could have saved you 40 minutes of Google time? Not sure I understand. You only asked the question 20 minutes ago.
• Jose: But I spent more time researching before posting here ;-) ... Thanks again.