Sql-server – SQL Server execution plan using a view, but no view is inside the stored procedure

performancequery-performancesql-server-2012view

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?

Best Answer

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.

Related Question