Why Sql Indexed View always use Clustered Index

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 us开发者_开发问答e index on Indexed view. (Maybe I should start another question on the exact step to create indexed view).

---

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

---

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.