Indexed View Creation And Underlying Indexes

Last Updated 5 years ago

Accidental Haha

While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view.

Starting off with no indexes, this query runs in about six seconds.

DECLARE @blobeater SQL_VARIANT;

SELECT DISTINCT
       @blobeater = b.UserId, 
       @blobeater = p.LastEditorUserId
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0;

DECLARE @blobeater SQL_VARIANT;

SELECT DISTINCT

@blobeater = b.UserId,

@blobeater = p.LastEditorUserId

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0;

Here’s the plan and the query stats:

Table 'Badges'. Scan count 5, logical reads 50379, 
Table 'Posts'. Scan count 5, logical reads 4206390, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 19079 ms,  elapsed time = 5489 ms.

Table 'Badges'. Scan count 5, logical reads 50379,

Table 'Posts'. Scan count 5, logical reads 4206390,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 19079 ms, elapsed time = 5489 ms.

Not bad, at 5.5 seconds elapsed. The optimizer thinks an index on Posts would help.

The join column is an include, which is kinda weird, but hey…

CREATE INDEX ix_whatever
    ON dbo.Posts ( LastEditorUserId )
    INCLUDE ( OwnerUserId );

CREATE INDEX ix_whatever

ON dbo.Posts ( LastEditorUserId )

INCLUDE ( OwnerUserId );

Out of curiosity, I added it, and re-ran the query.

Here’s the new plan and stats:

Table 'Badges'. Scan count 5, logical reads 50379, 
Table 'Posts'. Scan count 5, logical reads 13160, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 11969 ms,  elapsed time = 3396 ms.

Table 'Badges'. Scan count 5, logical reads 50379,

Table 'Posts'. Scan count 5, logical reads 13160,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 11969 ms, elapsed time = 3396 ms.

Saved a couple seconds, but now… The optimizer is asking for a different index.

It wants on on OwnerUserId, LastEditorUserId. Okay then.

CREATE INDEX ix_apathy 
    ON dbo.Posts ( OwnerUserId, LastEditorUserId );

1 2	CREATE INDEX ix_apathy ON dbo.Posts ( OwnerUserId, LastEditorUserId );

… And it doesn’t get used. It’s the same plan and stats as the last time.

I think that’s silly, and I also think it’s silly that The Optimizer doesn’t want an index on the Badges table at all.

Wonder why. Let’s add one.

CREATE INDEX ix_ennui 
    ON dbo.Badges ( UserId );

1 2	CREATE INDEX ix_ennui ON dbo.Badges ( UserId );

Sweet death, now the query takes about a minute and a half.

Here’s the new plan and stats:

Table 'Posts'. Scan count 5, logical reads 13188, 
Table 'Badges'. Scan count 5, logical reads 14076, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 214016 ms,  elapsed time = 75999 ms.

Table 'Posts'. Scan count 5, logical reads 13188,

Table 'Badges'. Scan count 5, logical reads 14076,

Table 'Workfile'. Scan count 0, logical reads 0,

Table 'Worktable'. Scan count 0, logical reads 0,

SQL Server Execution Times:

CPU time = 214016 ms, elapsed time = 75999 ms.

I can’t have a query run for this long. Clearly I need a way to make it faster.

This is where things got funny, I promise.

Creating The View

Here I am, a fairly experienced SQL Server user. I at least remembered that you can’t use DISTINCT in an indexed view.

You can GROUP BY, as long as you remember to COUNT BIG.

Let’s do that.

CREATE OR ALTER VIEW dbo.distincto
WITH SCHEMABINDING 
AS
SELECT b.UserId, 
       p.LastEditorUserId, 
       COUNT_BIG(*) AS why_is_this_still_a_thing
FROM   dbo.Badges AS b
JOIN   dbo.Posts AS p
    ON b.UserId = p.OwnerUserId
WHERE  p.LastEditorUserId > 0
GROUP BY b.UserId, 
         p.LastEditorUserId;
GO

CREATE OR ALTER VIEW dbo.distincto

WITH SCHEMABINDING

SELECT b.UserId,

p.LastEditorUserId,

COUNT_BIG(*) AS why_is_this_still_a_thing

FROM dbo.Badges AS b

JOIN dbo.Posts AS p

ON b.UserId = p.OwnerUserId

WHERE p.LastEditorUserId > 0

GROUP BY b.UserId,

p.LastEditorUserId;

Then I went to add the unique clustered index.

CREATE UNIQUE CLUSTERED INDEX cx_distincto 
    ON dbo.distincto (UserId, LastEditorUserId);

1 2	CREATE UNIQUE CLUSTERED INDEX cx_distincto ON dbo.distincto (UserId, LastEditorUserId);

Then I waited.

And waited.

And after about 30 seconds (seriously, that’s how impatient I am), I decided to see what the create index was up to.

It had the same plan as when I ran the query, and took about the same amount of time.

Feeling a little bit crazy, I backtracked up to the point where I created the index on Badges, and re-created the indexed view.

The new plan for creating indexed view clustered index morphed back into the “fast” plan for the query.

It also only took about 6 seconds.

Is There A Moral?

There are a few!

Missing index requests are kinda bananas
Sometimes the lack of a missing index request isn’t bananas
If you want to create an indexed view for a slow query, you can expect creating the index to take at least as long as running the query
If you want to create indexed views faster, tuning the underlying indexes might not be a bad idea

Thanks for reading!

How to Log Active Queries with sp_BlitzWho

Indexed View Matching With GROUP BY And DISTINCT

1 Comment. Leave new

Thomas Franz
December 6, 2018 2:17 am

Wouldn’t a filtered index be better in this case (I know, SQL Server does not suggests it, but see 1.)? Together with the userid-index on the Badges table it should produce the best performance

CREATE INDEX ix_filtered
ON dbo.Posts ( OwnerUserId) INCLUDE (LastEditorUserId ) WHERE LastEditorUserId > 0;

Furthermore the originial SELECT / VIEW could be optimized too, if you not need the COUNT() on the badges table:
SELECT p.OwnerUserId,
p.LastEditorUserId
FROM dbo.Posts AS p
WHERE p.LastEditorUserId > 0
AND EXISTS (SELECT * FROM dbo.Badges b WHERE b.UserId = p.OwnerUserId)
GROUP BY p.OwnerUserId, p.LastEditorUserId;

PS: indexed views are a nice feature, but I hate it that I have usually to specify the WITH (NOEXPAND) hint to get it used

Reply

Indexed View Creation And Underlying Indexes

Accidental Haha

Creating The View

Is There A Moral?

1 Comment. Leave new

Leave a Reply Cancel reply

Hi! I’m Brent Ozar.