Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and which columns should i use in incluse list. I am using SQL Server 2008/R2

share|improve this question
5  
Please post your DDL, as well as your query. –  Thomas Stringer Jul 20 '12 at 15:03
2  
When index tuning, please don't guess. :) –  Jon Seigel Jul 20 '12 at 15:14

1 Answer 1

up vote 4 down vote accepted

In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:

create table SomeTable
(
    id int identity(1, 1) not null
        primary key clustered,
    AnotherInt int null,
    SomeData nvarchar(1024) null
)
go

insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000

update SomeTable
set 
    AnotherInt = id * 3 + 152,
    SomeData = 'My ID value is ' + cast(id as nvarchar(16))

So we have a test table, SomeTable, with a clustered index. Execute the below query:

select SomeData
from SomeTable
where AnotherInt < 200

This causes a Clustered Index Scan, like you are seeing:

enter image description here

So analyzing the query, we have the column AnotherInt that is in the WHERE clause and being searched on. We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE column:

create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt) 
include (SomeData)
go

Now, executing the same query above:

select SomeData
from SomeTable
where AnotherInt < 200

SQL Server will utilize that non-clustered index to return the data. It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:

enter image description here

Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.

share|improve this answer
    
Thanks Shark, that is really helpful –  aasim.abdullah Jul 20 '12 at 17:59
    
@aasim.abdullah No problem at all. If you post your CREATE TABLE batch and any other surrounding DDL (i.e. CREATE INDEX ...) as well as your query, I can use your particular situation and go from there. Otherwise, if this answers your question, please consider upvoting and marking this answer as accepted. –  Thomas Stringer Jul 20 '12 at 18:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.