Indexed View Creation And Underlying Indexes

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.

Here’s the plan and the query stats:

Whole Lotta Hashing

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…

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

Here’s the new plan and stats:

Still Hashing Things Out

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

It wants on on OwnerUserId, LastEditorUserId. Okay then.

… 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.

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

Here’s the new plan and stats:

No Use For An Index

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.

Then I went to add the unique clustered index.

Then I waited.

And 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!

  1. Missing index requests are kinda bananas
  2. Sometimes the lack of a missing index request isn’t bananas
  3. 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
  4. If you want to create indexed views faster, tuning the underlying indexes might not be a bad idea

Thanks for reading!

Previous Post
How to Log Active Queries with sp_BlitzWho
Next Post
Indexed View Matching With GROUP BY And DISTINCT

1 Comment. Leave new

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}