March 26, 2015 at 1:06 pm
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?
March 26, 2015 at 1:57 pm
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
March 26, 2015 at 2:03 pm
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.
March 26, 2015 at 2:17 pm
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.
March 26, 2015 at 3:40 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply