Group by's in materialized views and query planning [duplicate]

Question

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

• Can indexed views still help me in this scenario somehow?
• Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).

That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.

Answer 1

The feature is called 'indexed view matching' and it can be tricky to get right. There are a number of prerequisites outlined here: Resolving Indexes On Views, plus the indexed view needs to be able to answer the question that your query is asking. I would expect it to work in Developer/Enterprise Edition for straightforward scenarios, and I got it to work with a simple schema and query:

-- Indexed view matching
USE tempdb
GO

IF OBJECT_ID('dbo.BaseTable') IS NOT NULL DROP TABLE dbo.BaseTable
GO

CREATE TABLE dbo.BaseTable (

    rowId       INT IDENTITY PRIMARY KEY,

    slot        INT NOT NULL,
    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
)
GO


-- Add dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.BaseTable ( slot, someDate, someData )
SELECT
    rn % 999,
    DATEADD( day, rn % 333, '1 Jan 2016' ), 
    NEWID()
FROM cte

CHECKPOINT
GO --10


DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

CREATE VIEW dbo.IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;
GO

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);
GO


-- Indexed view matching
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- Indexed view not used as it can't 'cover' the query
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
AND rowId = 42
OPTION ( RECOMPILE );
GO


-- Query Indexed view directly with NOEXPAND hint
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView WITH ( NOEXPAND )
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- EXPAND VIEWS hint ensures Indexed view is NOT used.
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView 
WHERE Slot = @slot
OPTION ( RECOMPILE, EXPAND VIEWS );
GO

Execution Plan:

There are a few other queries in my test script above and you can see the indexed view is not used for some of them.

Here are some quotes about indexed views from one of the architects of the query optimizer, Conor Cunningham:

The Query Optimizer contains logic to use this index both in cases when the original query text referenced the view explicitly as well as in cases when the user submits a query that uses the same components as the view (in any equivalent order).

but ...

[complexity] ... makes it difficult for the Query Optimizer to consider plans that perform the view evaluation first, then process the rest of the query. Arbitrary tree matching is a computationally complex problem, and the feature set of views is too large to perform this operation efficiently.

'Microsoft SQL Server 2008 Internals', Chapter 8: The Query Optimizer - Conor Cunningham

...indexed views work for basic SPJG (SELECT-PROJECT-JOIN-GROUPBY) queries. The joins must be inner joins, not self joins, and join on a key. Each operator has a series of restrictions...

source

I think those quotes are quite telling. You may also wish to consider filtered indexes.