2

I have recently read the text below in 70-762 Developing SQL Databases (p. 67):

The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the stored data in the index structure. For Standard Edition, it uses the code of the query unless you use a NOEXPAND table hint, in which case it uses the clustered index representation.

and since we have migrated from Enterprise to Standard (using SQL Server 2016 SP1) and have indexed view, I decide to check how this is affecting the performance. So, I executed the queries below:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view] 
WITH (NOEXPAND);

and:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view];

and the second query was slower and the view definition was expanded, while in the first case, the clustered index scan was used. In this article is said, that NOEXPAND can lead to better performance even on Enterprise Edition.

I am wondering, having the quote above, how indexed view is handled in Enterprise Edition, can we conclude that we should always used WITH (NOEXAPND) in Standard Edition to get the similar behavior?

Why the behavior is not the same in all editions as we have free workaround?

1 Answer 1

1

Why the behavior is not the same in all editions as we have free workaround?

You have no workaround in case when the poorly performing query does not use any wiev at all.

You can have a situation when some application does some aggregations and with the large volumes of data it became slowly. In case of Enterprise Edition you can create an indexed view that query optimizer could be able to use for this query even if a query does not access any view at all. And you cannot use this approach in other editions as you have no control on the application source code.

This case is described in Paul White's article that you mentioned about:

Enterprise Edition and View Matching

On an Enterprise Edition instance, the query optimizer may be able to use an indexed view even if the query does not mention the view explicitly. If the optimizer is able to match part of the query tree to an indexed view, it can choose to do so, based on its estimation of the costs of using the view or not. The view-matching logic is reasonably clever, but it does have limits that are pretty easy to hit in practice. Even where view matching is successful, the optimizer can still be misled by inaccurate cost estimations.

2
  • That's seems as something very rare for me - to have a query that has a part of data sources which match a indexed view definition and to rely on the engine to replace them with the indexed view. It seems that this should be done by the developer.
    – gotqn
    Nov 26, 2018 at 16:40
  • It might be something that should be done by a developer, but often a DBA is tasked with improving performance of a system they cannot change the code for. In such cases being able to add an indexed view that's automatically used by SQL Server with no code changes is a fantastic thing.
    – Rory
    May 12, 2021 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.