Sql-server – Is using indexed view with no expand on standard edition a must

materialized-viewsql serversql-server-2016

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?

Best Answer

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.

Related Question