Execution plan using views, but store procedure does not list or use that view at all

Question

Execution plan using views, but store procedure does not list or use that view at all

sql-lover

SSCoach

Points: 18530
More actions
March 26, 2015 at 1:06 pm

#296340

This is driving me crazy!
I'm troubleshooting a performance issue on a store procedure that has no view at all! Zero! No select statement, no update, no nothing. However, the execution plan shows that the main bottleneck is a missing Index on a view. :crazy:
The view is a simple one, like this:
CREATE VIEW [ws].[MyVIEWTable]
WITH SCHEMABINDING
AS
SELECT col1, col2
FROM [dbo].[MyTable]
The store procedure has references against [dbo].[Table].
Why the SQL engine is touching or using those views, because the schemabinding?
To add insult to injury, I added the missing Index on that view and fixed the problem.
Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 1

Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view.

From Resolving Indexes on Views

Alex Suprun

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

Would need to see the actual code. Overall this is just not adding up.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

sql-lover SSCoach Points: 18530 More actions · Answer 3

Found the answer via Google:

https://technet.microsoft.com/library/Cc917715

But Aaron confirmed.

I will quote what technet says, the relevant part:

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

Basically, it's up to the engine to pick, VIEW or actual table. It is by design.

Learned something new today.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Yep. 100% by design. Isn't the optimizer the coolest thing ever.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning