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
-
5Please post your DDL, as well as your query. – Thomas Stringer Jul 20 '12 at 15:03
-
2When index tuning, please don't guess. :) – Jon Seigel Jul 20 '12 at 15:14
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:
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:
Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.
-
@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