April 2, 2015 at 3:49 am
Good Morning,
We are currently in the progress of upgrading from SQL 2008 SP4 (10.0.6000) to SQL 2012 SP2 (11.0.5548), both enterprise edition; alongside this the business is making some changes to our data structure that were initially flawed (e.g. address id is held on the customer table rather than in a linking table).
As part of this change, I'm reviewing the performance aspect following the change. My current focus is on a particular indexed view that is used extensively by various applications to bring together several key tables and present that data in a consistent format.
What I've noticed, however, is that in 2012 if I select from the view then it does not consider the indexed view and instead uses the view definition. The only way I have been able to get it to consider the index on the view at all, is by using the NOEXPAND hint. I've rebuilt the statistics on the underlying table and on the indexed view, and also rebuilt the index on the view, but to no avail.
This behaviour does not occur in 2008, with or without the noexpand hint.
Is this how this should be behaving? The estimated cost of the plans are substantially different, always in favour of the indexed view, so it seems odd that in a product that's supposed to have the benefit of considering the index on a view automatically that it's picking a vastly inferior plan. The amount of code that would need to be rewritten to add the noexpand hint which was not previously needed is quite substantial.
Is there anything other than statistics that I need to look at that might address this problem?
Plans from both servers attached, with and without the NOEXPAND hint.
As always, thanks in advance for any tips/pointers.
Cheers,
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 4:00 am
The optimizer can decide to expand the view and the behavior is not completely predictable (as you have seen).
A quick fix could be to incapsulate the view in another view adding the NOEXPAND hint:
-- myIndexedView is the name used in the application:
-- you can rename the view to myIndexedView_BASE
CREATE VIEW myIndexedView_BASE
WITH SCHEMABINDING
AS
SELECT Whatever
FROM SomeTable;
GO
CREATE UNIQUE CLUSTERED INDEX IX_myIndexedView ON myIndexedView_BASE (Whatever);
GO
-- Then you create a new view with the name used in the application,
--based on the indexed view with the NOEXPAND hint
CREATE VIEW myIndexexView
AS
SELECT Whatever
FROM myIndexedView_BASE WITH (NOEXPAND);
GO
This would allow you to put the NOEXPAND hint in a single place and avoid refactoring several parts of your application.
-- Gianluca Sartori
April 2, 2015 at 4:09 am
OK yeah that would work as a workaround; though it does add another layer of abstraction from the code being run.
Is there any information on how the optimizer considers indexed views and where the change is between 2008 and 2012 for the behaviour is?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 4:26 am
This is internal behaviour, which is not extensively documented.
As far as the additional abstraction layer, SQL Server handles it gracefully: you shouldn't be worried about that.
-- Gianluca Sartori
April 2, 2015 at 5:08 am
I suppose the other advantage of creating the view on the view is that if future patches/upgrades change the behaviour again, then it's a small matter of changing the intermediate index back to not having the NOEXPAND hint.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 6:51 am
Good point. I'm afraid that hunting down the reason why the expand/noexpand behaviour changed in all the queries is a daunting task.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply