Why Sql Indexed View always use Clustered Index

Question

I need some pointer on how to debug the following problem.

Environment: SQL Server 2005 Enterprise.

I have an indexed view with contains clustered index and multiple non-unique, non-clustered index. However when I execute the query, SQL server always perform Clustered index scan instead of index seek on my key.

Here is a simplify version.

CREATE VIEW MyIndexedView WITH SCHEMABINDING
SELECT a.Col1, b.Col2, c.Col3, d.Col4
FROM a JOIN b on a.id = b.id 
       JOIN c on a.id = c.id
       JION d on c.id = d.id

There is a clustered index on Col1, and non-unique, non-clustered on Col2, Col3.

When I run the following query

SELECT a.Col1, b.Col2, c.Col3 FROM MyIndexedView WITH(NOEXPAND) WHERE b.Col2='blah'

and look at execution plan, I see SQL server run Clustered index scan on a.Col1 instead of perform index seek on Col2.

I tried to recreate the view and index.

Updated: I did some additional testing and running these two queries side by side in Query Analyzer.

    a) SELECT a.Col1, b.Col2, c.Col3 
       FROM MyIndexedView WITH(NOEXPAND) WHERE b.Col2='blah'

    b) SELECT a.Col1, b.Col2, c.Col3
       FROM MyIndexedView WHERE b.Col2 = 'blah'

Query 'a' will take 95% of the time and use Cluster Indexed scan. Query 'b' will only take 5% of the time and use Index Seek on col2. I try to swap the order of queries (run b first and a later) yield the same percentage.

• This little experiment confirm that if sql use index seek it will be faster then cluster index scan.
• Second I though if I don't include "WITH(NOEXPAND)" then SQL server will not use index on Indexed view. (Maybe I should start another question on the exact step to create indexed view).

Answer 1

How many records are there in your view?

If the result of the join is small then it is most cost efficient to scan the clustered index than seek another.

Answer 2

I reproduced your sample and came up with the expected results with the index seek on Col2. The only way I was able to get it to do the clustered index scan was if I disabled the index. So first try rebuilding the index on Col2 to make sure it is actually enabled (or check the "Use Index" checkbox in index properties - options).

---

Here are the scripts I used to create the tables, view & indexes

 CREATE TABLE [dbo].[a](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Col1] [varchar](100) NOT NULL,
 CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[b](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Col2] [varchar](100) NOT NULL,
 CONSTRAINT [PK_b] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[c](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Col3] [varchar](100) NOT NULL,
 CONSTRAINT [PK_c] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[d](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Col4] [varchar](100) NOT NULL,
 CONSTRAINT [PK_d] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE VIEW [dbo].[MyIndexedView] WITH SCHEMABINDING
AS
SELECT a.Col1, b.Col2, c.Col3, d.Col4
FROM dbo.a JOIN dbo.b on a.id = b.id 
    JOIN dbo.c on a.id = c.id
    JOIN dbo.d on c.id = d.id
GO

/****** Object:  Index [IX]    Script Date: 11/13/2009 21:50:01 ******/
CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[MyIndexedView] 
(
 [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX2]    Script Date: 11/13/2009 21:50:39 ******/
CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[MyIndexedView] 
(
 [Col2] ASC,
 [Col3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

---

And I populated the tables like this:

declare @x int

SET @x = 0
while @x < 10
begin
INSERT INTO a (Col1 ) VALUES (newid())
INSERT INTO b (Col2 ) VALUES (newid())
INSERT INTO c (Col3 ) VALUES (newid())
INSERT INTO d (Col4 ) VALUES (newid())

SET @x=@x+1
end

---

Executing your query

SELECT Col1, Col2, Col3 FROM MyIndexedView WITH(NOEXPAND) WHERE Col2='blah'

shows an index seek on IX2

but if I disable that index ALTER INDEX [IX2] ON [dbo].[MyIndexedView] DISABLE

and rerun, I see the clustered index scan on MyIndexedView.IX