CODEWITHSUNDEEP
sql-serversql-server-2012
Metaphor
Metaphor

Reputation: 6405

Performance with materialized (indexed) view

Consider this materialized view:

CREATE VIEW [vwPlaySequence] WITH SCHEMABINDING
AS
SELECT 
    p.SiteIDNumber,
    dbo.ToUnsignedInt(p.SequenceNumber) AS PlayID, 
    p.SequenceNumber
FROM dbo.Play p
GO

CREATE UNIQUE CLUSTERED INDEX 
    PK_vwPlaySequence ON [vwPlaySequence] 
        (
            [PlayID], 
            [SiteIDNumber],
            [SequenceNumber]
        )
GO

The base table has a clustered index on SequenceNumber.

The following query on the base table executes on 160 million rows in 4 seconds:

select SiteIDNumber, min(SequenceNumber), max(SequenceNumber) from Play
group by SiteIDNumber

Here is the execution plan:

Base execution plan

And this is the same query on the view executes in 46 seconds:

select SiteIDNumber, min(SequenceNumber), max(SequenceNumber) from vwPlaySequence
group by SiteIDNumber

Its execution plan:

View execution plan

I'm not seeing what it is in these execution plans that would warrant such a drastic difference in run time. I've run both of these queries many times with the same results.

Upvotes: 0

Views: 287

Answers (1)

usr
usr

Reputation: 171188

Both queries use the view. One is parallel one is not. You say that adding OPTION (MAXDOP 1) to both queries makes all differences disappear. This means that parallelism accounts for all of the differences.

There's no logical reason SQL Server has to pick a serial plan in one of the cases here. It is probably a bug or known limitation. I have encountered many limitations and strange behaviors with indexed view matching. In that sense I'm only mildly surprised.

Now that the difference is (kind of) explained, what to do about it?

  1. You can try to force parallelism: OPTION (QUERYTRACEON 8649) --set parallelism cost to zero. This is an undocumented trace flag that is considered safe for production by some leading experts. I also do consider it to be safe.
  2. You can try to select from the view using WITH (NOEXPAND). This bypasses view matching and hopefully allows SQL Server to find a parallel plan.

Prefer option (2).

Upvotes: 3

Related Questions