Wah Wah Wah
Yes, indexed view maintenance can be quite rough. I don’t mean like, rebuilding them. I will never talk about that.
I mean that, in some cases locks are serializable, and that if you don’t mind your indexes you may find run-of-the-mill modifications taking quite a long time.
Let’s go look!
Let’s get update a small chunk of the Posts table.
BEGIN TRAN UPDATE p SET p.Score += 100 FROM dbo.Posts AS p WHERE p.OwnerUserId = 22656; ROLLBACK
Let’s all digress from the main point of this post for a moment!
It’s generally useful to give modifications an easy path to find data they need to update. For example:
This update takes 1.6 seconds because we have no useful index on OwnerUserId. But we get a daft missing index request, because it wants to include Score, which would mean we’d need to then update that index as well as read from it. Locking leads to NOLOCK hints. I tend to want to introduce as little of it as possible.
With an index on just OwnerUserId, our situation improves dramatically.
Allow Me To Reintroduce Myself
Let’s see what happens to our update with an indexed view in place.
CREATE OR ALTER VIEW dbo.PostScoresVotes WITH SCHEMABINDING AS SELECT p.Id, SUM(p.Score * 1.0) AS ScoreSum, COUNT_BIG(v.Id) AS VoteCount, COUNT_BIG(*) AS OkayThen FROM dbo.Posts AS p JOIN dbo.Votes AS v ON p.Id = v.PostId WHERE p.PostTypeId = 2 AND p.CommunityOwnedDate IS NULL GROUP BY p.Id; GO CREATE UNIQUE CLUSTERED INDEX c_Id ON dbo.PostScoresVotes(Id);
Our update query now takes about 10 seconds…
With the majority of the time being spent assembling the indexed view for maintenance.
The Problem Of Course
Is that our indexes are bad. We’ve got no helpful index between Posts and Votes to help with the assembly.
Our first clue may have been when creating the indexed view took a long time, but hey.
Let’s fix it.
CREATE INDEX v ON dbo.Votes(PostId);
Now our update finishes in about a second!
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.