Sql-server – Why SQL does not use the indexed view

hintsmaterialized-viewoptimizationsql serversql server 2014

I have the following version of SQL Server:

Microsoft SQL Server 2014 (SP3) (KB4022619) – 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

I created the following view

CREATE VIEW [dbo].[vwGroupsOfficesDependencies]
WITH SCHEMABINDING 
AS
SELECT        GC.IdGroup, COUNT_BIG(*) AS countBig, OD.IdOffice
FROM            dbo.Group AS GC INNER JOIN
                         dbo.GroupDependencies AS GD ON GC.IdGroup = GD.IdGroup INNER JOIN
                         dbo.OfficeDependencies AS OD ON OD.IdDependency = GD.IdDependency INNER JOIN
                         dbo.Dependencies AS D ON D.IdDependency = GD.IdDependency
WHERE        (D.Active = 1)
GROUP BY GC.IdGroup, OD.IdOffice

Then I created the clustered index

CREATE UNIQUE CLUSTERED INDEX [IX_vwGroupsOfficesDependencies_IdOficeIdGroup] ON [dbo].[vwGroupsOfficesDependencies]
(
    [IdGroup] ASC,
    [IdOffice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

But when I run the query

SELECT IdGroup, IdOffice FROM dbo.[vwGroupsOfficesDependencies]

I see in the execution plan that it does not use the indexed view, it performs the joins with the tables inside the view. But if I include the hint WITH(NOEXPAND) it uses the indexed view.
I read that I must include WITH(NOEXPAND) for non Enterprise version, but in my case it is not necesary. Why it is not using the indexex view?

Best Answer

Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins.

It may later chose to match part(s) of, or the whole plan, back to one or more indexed views later on in the optimization process.

There are two common reasons that the original expansion isn't reversed later:

  1. The plan tree has been changed by prior optimizer activity in such a way that the indexed view no longer matches (or at least, can no longer be matched by the tools available to the optimizer).
  2. The optimizer finds a good enough (low estimated cost) plan before it goes looking for indexed view substitutes.

There are other reasons to prefer NOEXPAND hints. For example, statistics are only automatically created on indexed views when that hint is present. See my article Another Reason to Use NOEXPAND hints in Enterprise Edition for more.

Related Question