Index Tuning

Question

Index Tuning

Ananth-431739

Ten Centuries

Points: 1185
More actions
January 4, 2008 at 2:30 am

#154042

Hi,
I'm new to index concept...I need to create an index for an table.
Ex: Employee table has empid, deptid and contains additional fields for storing the employee information. I have created the index as below
Ind1 for empid (clustered)
Ind2 for empid and dept id (Non clustered)
Is the way of creating index is correct? Because empid and depid is an covering index and it is used in the many queries along with the where clause. Is there any correction needs to be done?????.....Suggestions are highly appreciated.
Pl provide useful links on indexes!!!

Viewing 15 posts - 1 through 15 (of 15 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Ananth,

your second index should be only on dept_id, because the key column of your clustered key (emp_id) will be added automatically to all your non-clustered indexes.

Other than that it depends on the way your data is accessed and how selective columns are.

[font="Verdana"]Markus Bohse[/font]

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

Indexes on a table should be based on the queies that will be run against the table. There's no point in having an index on a column if there are no queries that filter or join on that column.

As for your 2 examples, the second one iss unlikely to be used. Since the cluster is on the EmpID, it's very unlikely that a NC on empID will be used. If you switched the columns around (deptid, empid) it might be useful, but again that depends on the queries that will be used.

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

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 3

Thanks.

in my example if the index is created as

ind1 empid (non clustered)

ind2 empid and deptid (non clustered)...

empid is used in many queries for filtering purpose and empid,deptid combination is also used in many queries.

Is the ind1 is required or ind2 itself sufficient...

pls provide the link for index....

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

In that case, inx1 is unnecessary, since it is a left-based subset of another index.

I do recommend that every table gets a clustered index so maybe thins (NB, without any knowledge of the selectivity of the data or the queries, I'm guessing here)

idx1 EmpID clustered

idx2 Dept_id, EmpID nonclustered. (if there are queries that search on boht. If queries only search on dept_id, then make the index just on dept_id)

For index info, take a look at www.sqlskills.com Lots of good stuff there.

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

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 5

ok

if i have two non clustered index and trying to use the column in where clause i.e

ex:

ind1 empid (nonclustered)

ind2 deptid (nonclustered)

if i use the empid and deptid in the where condition then whether ind1 and ind2 index will be utilized ????

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

If you have queries of the form

WHERE EmpID = @Emp AND DeptID = @Dept, then only one of the indexes will be used. Which one depends on the selectivity of the data. SQL will then do a lookup to the clustered index/heap to retrieve the other column and will do a filter on that.

Unless you have many, many rows, it's very unlikely for SQL to use both indexes.

If you have queries of the form

WHERE EmpID = @Emp AND DeptID = @Dept then see which index SQL is using (EmpID or DeptID). Use the execution plan for that, then add the other one as a second column of that index.

You'll end up with either

idx1 - EmpID, DeptID

idx2 - DeptID

OR

idx1 - EmpID

idx2 - DeptID, EmpID

depending which index you choose to widen. There's no real reason to widen both.

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

GSquared SSC Guru Points: 260824 More actions · Answer 7

Based on your original question, the indexes I'd probably use are:

empid (clustered)

deptid (nonclustered)

Then look at a few execution plans for a few of the common queries that use those columns. See if they use the indexes, take a look at the estimated costs of the queries. (If you're not sure how to do this, there's a button in Query Analyzer or Management Studio that says, "Display Estimated Execution Plan". What you're looking for is do the bottom icons on the tree it displays say, "Index Seek", or do they say things like, "Table Scan" or "Clustered Index Scan". There's more to it, but that's the basic idea. The cost will show if you hover the mouse over one of the icons in the tree, where it says, "Estimated Subtree Cost". You want very low numbers.)

Try looking at an execution plan or two without the indexes. Then add the clustered index and see what the execution plan changes to. Then add the nonclustered index and see what it changes to. You're looking for more "Index Seek" and less "Table Scan" or "Clustered Index Scan". Seeks are better than scans is what it basically boils down to. You also want to see the "Estimated Subtree Cost" going down.

Of course, if the plan doesn't change, if the cost doesn't go down, if the indexes aren't being used, or if scans are being used instead of seeks, there are reasons for those things, and you'll need to dig into the queries a bit more.

There are a lot of really good articles available on how to use indexes, how to tune queries, etc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 8

Ananth-431739

Ten Centuries

Points: 1185

January 4, 2008 at 8:42 am

#764872

could you provide me the links?????

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 9

I have an query regarding index.....

ex

idx1 (emp,depid) non clustered index

an covering index was created in the combination of empid and depid. whether i need to use this column in 'where' clause as same order in which index was created???????? I have gone thr' many article saying that it should be in the same order in which index was created. but when i used in my query

i.e depid='aa' and empid=111 not in same order as index created, index is getting used. whether i have done any mistake????or is it correct.

GSquared SSC Guru Points: 260824 More actions · Answer 10

The order of your Where clause doesn't matter. It just needs to make sense to you. The database doesn't care.

On links for index use, try searching "ms sql index use" in Google. Or search the forums here for "index". http://www.simple-talk.com has a number of discussions and articles on indexes as well.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

If it's to speed up a WHERE clause, then order of the columns in the index should be from most slective to least selective. The optimizer should be smart enough to pick it regradless of how you invoke it in the WHERE clause.

On the other hand - if you're trying to help out an ORDER BY, then clearly you need the index columns in the same order as the order by clause.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Test the code in the presence of the indexes and not... that's the only real way to tell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

The order of expressions in the where clause doesn't make the slightest difference. SQL will do them inn whatever order is most efficient, based on the data and the available indexes.

As for links, I gave you one already. www.sqlskills.com. There's a lot of very good material there. Some is rather advanced stuff, some very in depth. Check the blogs on that site out as well.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Ananth,

Books Online (comes free with SQL Server) also has a wealth of knowledge on how indexes work and how to build them...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)