Sql-server – Where to place non clustered index

index-tuningperformancequery-performancesql serversql-server-2008-r2

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

Best Answer

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.

Related Question