Indexed Views And Data Modifications

Last Updated 7 years ago

Love/Hate

When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too.

But SQL Server is sort of smart about some modifications. Why it isn’t smart about all of them is beyond me. Let’s take a look at why, with StackOverflow.

Silly Demo

We’ll jump through the usual hoops to create an indexed view first.

CREATE VIEW dbo.UselessView
WITH SCHEMABINDING
AS
       SELECT   u.Id,
                u.DisplayName,
                SUM(u.UpVotes) SumUp,
                SUM(u.DownVotes) AS SumDown,
                COUNT_BIG(*) AS YouHaveToDoThisToCreateAnIndexedView
       FROM     dbo.Users AS u
       WHERE    u.Reputation > 1
                AND u.CreationDate >= CONVERT(DATETIME, '20160101', 101)
       GROUP BY u.Id, u.DisplayName;

GO 

CREATE UNIQUE CLUSTERED INDEX CX_Useless ON dbo.UselessView (Id)
GO

CREATE VIEW dbo.UselessView

WITH SCHEMABINDING

SELECT u.Id,

u.DisplayName,

SUM(u.UpVotes) SumUp,

SUM(u.DownVotes) AS SumDown,

COUNT_BIG(*) AS YouHaveToDoThisToCreateAnIndexedView

FROM dbo.Users AS u

WHERE u.Reputation > 1

AND u.CreationDate >= CONVERT(DATETIME, '20160101', 101)

GROUP BY u.Id, u.DisplayName;

CREATE UNIQUE CLUSTERED INDEX CX_Useless ON dbo.UselessView (Id)

Let’s look at where SQL is smart first. This section is lovingly called “inserts”. Our indexed view is filtered on people with a Reputation higher than 1, and whose account was created in the year of our deterministic date, 2016. When we insert single rows in the table, which is how many OLTP apps work, SQL is able to figure out which data is going to require updating the indexed view index.

--Year 2015, Rep 1.
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Year 2015, Rep 2
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 2, 0, 0, N'www.com', 2147483647 );

--Year 2016, Rep 1
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2016-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Year 2016, Rep 2
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2016-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 2, 0, 0, N'www.com', 2147483647 );

--Year 2015, Rep 1.

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Year 2015, Rep 2

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 2, 0, 0, N'www.com', 2147483647 );

--Year 2016, Rep 1

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2016-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Year 2016, Rep 2

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2016-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 2, 0, 0, N'www.com', 2147483647 );

The execution plans for these are what we’d hope for. Where rows don’t qualify for our indexed view filter, it doesn’t touch the indexed view.

Unfortunately

This doesn’t seem to extend to updates and deletes. If we insert a single row that doesn’t qualify for our indexed view filter, then update it so it really doesn’t qualify, and then delete the rows, SQL turns into a big dumb dumb.

BEGIN TRAN

--Year 2016, Rep 1
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );
GO 

--Update year DOWN to 2014, and Rep DOWN to 0
UPDATE  u
SET     u.Reputation = 0,
        u.CreationDate = '20140101'
FROM    dbo.Users AS u
WHERE   u.Id = 2147483647;
GO 

--Delete the single row that never qualified for the indexed view filter
DELETE  u
FROM    dbo.Users AS u
WHERE   u.Id = 2147483647;
GO 

ROLLBACK

BEGIN TRAN

--Year 2016, Rep 1

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Update year DOWN to 2014, and Rep DOWN to 0

UPDATE u

SET u.Reputation = 0,

u.CreationDate = '20140101'

FROM dbo.Users AS u

WHERE u.Id = 2147483647;

--Delete the single row that never qualified for the indexed view filter

DELETE u

FROM dbo.Users AS u

WHERE u.Id = 2147483647;

ROLLBACK

Oh, get out.

Now, I know. That’s not really fair. We’re just updating and deleting based on the ID, and while it’s reasonable to think SQL might keep track of what’s in the indexed view, or that it might just check the indexed view and then discard the unnecessary operators, it doesn’t. In fact, even if we give the optimizer some extra help, it only kind of works.

Let’s be explicit about the fact that this row couldn’t ever possibly be in the indexed view.

BEGIN TRAN

--Year 2015, Rep 1
INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )
    VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );
GO 

--Update ID and make sure we filter on rows that don't qualify for the indexed view filter
UPDATE  u
SET     u.Reputation = 0,
        u.CreationDate = '20140101'
FROM    dbo.Users AS u
WHERE   u.Id = 2147483647
        AND u.Reputation < 2
        AND u.CreationDate < '20160101';
GO 

--Delete ID and make sure we filter on rows that don't qualify for the indexed view filter
DELETE  u
FROM    dbo.Users AS u
WHERE   u.Id = 2147483647
        AND u.Reputation < 2
        AND u.CreationDate < '20160101';
GO 

ROLLBACK

BEGIN TRAN

--Year 2015, Rep 1

INSERT dbo.Users ( Id, AboutMe, Age, CreationDate, DisplayName, DownVotes, EmailHash, LastAccessDate, Location, Reputation, UpVotes, Views, WebsiteUrl, AccountId )

VALUES ( 2147483647, N'Hi', 99, '2015-01-01', N'Erik', 2147483647, NULL, GETDATE(), N'Here', 1, 0, 0, N'www.com', 2147483647 );

--Update ID and make sure we filter on rows that don't qualify for the indexed view filter

UPDATE u

SET u.Reputation = 0,

u.CreationDate = '20140101'

FROM dbo.Users AS u

WHERE u.Id = 2147483647

AND u.Reputation < 2

AND u.CreationDate < '20160101';

--Delete ID and make sure we filter on rows that don't qualify for the indexed view filter

DELETE u

FROM dbo.Users AS u

WHERE u.Id = 2147483647

AND u.Reputation < 2

AND u.CreationDate < '20160101';

ROLLBACK

Two out of three ain’t bad. The insert and the delete get it. The update is still being a big dummy. I’m not calling update plans easy to implement; I’ve read Conor’s chapter more than a few times. Updates are hard. In this case, though, roughly 72% of the plan cost is invested in the indexed view nodes of the plan.

I think this is weird, too

It’s certainly not what I expected to happen when I first decided to test this out. To be fair, I didn’t invest much time in trying to find a workaround for the update plan. Frankly, you shouldn’t have to. I did try changing the WHERE clause to lower values, and direct equality comparisons, but nothing changed the plan.

Thanks for reading!

No seriously, don’t use SQL_VARIANT for that

sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use

5 Comments. Leave new

Guillermo
March 20, 2017 8:04 pm

Good to know! How do you usually come across these finds?

Reply
- Erik Darling
  March 20, 2017 8:31 pm
  
  I get really bored waiting for new Faith No More albums to come out.
  
  Reply
fleetoiler
March 21, 2017 6:07 am

Hi Erik, great write up.

We’ve come across a similar but opposite(?) issue where the inserts generate “super dumb” plans instead of updates being the issue when updating an indexed view. We’ve worked around this by doing a separate two statement insert-then-update (using similar forcing-redundant-logic-techniques) for rows/data shapes that convinces the compiler into a sensible and efficient plan for both. Our issue however isn’t totally the compilers fault, we have multi-column clustering keys and some other bad/complex statistics and data shapes that lead to parameter sniffing issues and cardinality-row-estimates being partly (or mostly!) to blame for confusing the query compiler.

Does the issue perpetuate if you change the scenario to be purely equality and not equal operators instead of less/greater than operators? Perhaps the compiler isn’t able to reconcile 1 as being opposites?

Reply
- fleetoiler
  March 21, 2017 6:22 am
  
  Sorry I should have read your disclaimer at the bottom about already having tried changing everything to direct equality comparisons, that’s partly what we used to help work around our insert/update multi-table-filtered-indexed-view issue was to match the filters and indexed keys of the view exactly to make sure the compiler knew we really meant it!
  
  Reply
  - Erik Darling
    March 21, 2017 7:55 am
    
    It sounds like you’d have an even better blog post about indexed views than I do!
    
    Reply