Index Selection in SQL Server

Question

Index Selection in SQL Server

vinu512

SSCoach

Points: 15729
More actions
February 27, 2013 at 12:22 am

#271352

Hi,
Consider the following example :
I have a table Employees:
Create Table Employees
(
EmployeeId BigInt Identity(1,1) ,
EmployeeName Varchar(30) NOT NULL
)
I have two indexes:
One Clustered and One NonClustered............both on the same column(EmployeeName).
Now, if I run the following query:
Select EmployeeName From Employees

When I see the Execution plan...I can see that the query used the NonClustered Index.
What is the Selection criteria behind the selection of the NonClustered Index. There must be certain factors that SQL Server keeps in mind before selecting an Index.
Could someone please explain the technicalities behind Index Selection.....or may be refer a link which would help me get to know this stuff.....
Thank You very much.
Vinu Vijayan
For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In this case it's simply the smallest index that can be used to satisfy the query.

Selection has to do with which index can filter the largest number of the where clause predicates (if there are any), the index order if there's a sort involved, the estimated number of rows when the index isn't covering, and a hell of a lot more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vinu512 SSCoach Points: 15729 More actions · Answer 2

GilaMonster (2/27/2013)
In this case it's simply the smallest index that can be used to satisfy the query.
Selection has to do with which index can filter the largest number of the where clause predicates (if there are any), the index order if there's a sort involved, the estimated number of rows when the index isn't covering, and a hell of a lot more.

Thanks for replying Gail. Can you give me a link where I can read in detail about Index Selection?

Something that helped you in learning about this when you started.......would be great.

Thanks 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

vinu512 (2/27/2013)
Something that helped you in learning about this when you started.......would be great.

I learnt mostly by write query, run query, analyse exec plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vinu512 SSCoach Points: 15729 More actions · Answer 4

GilaMonster (2/27/2013)
vinu512 (2/27/2013)
Something that helped you in learning about this when you started.......would be great.
I learnt mostly by write query, run query, analyse exec plan.

I am pretty much doing the same but.....still don't have concrete idea about how the SQL server engine picks an Index.....you did help me with that but I was looking for some more explanation.......I'll keep Googling....thanks for your help Gail. 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

If you go to this link http://www.sqlservercentral.com/articles/71564/ there's a link to a free book about how the query optimizer works, its not a deep dive, but goes into the basics of index selection and plan optimization.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 6

The best resources to learn about internals are the Books Online and the book SQL Server Internals by Kalen Delaney, et al. Benjamin Nevarez book is also a pretty good resource, but primarily focused on how the optimizer does what it does, not how indexes & statistics work. It's not a free resource, but I'd pick up Kalen's book if you really want to get a handle on this stuff. From there you can count on other resources such as Gail's blog, the blogs at SQLSkills, Paul White's blog and the rest. But the foundations, they're in Kalen's book.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vinu512 SSCoach Points: 15729 More actions · Answer 7

Thanks Jason and Grant.

I did have a look at Sql Server Internals and even placed an order for it with Amazon. 😉

Till then I downloaded the ebook of the same to get me started.

Have also started with looking at the resources you mentioned.

Thanks a tonne for the headstart guys!! 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉