Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tips on creating indexes


Tips on creating indexes

Author
Message
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2073 Visits: 3197
Dear All,
Can anyone give me some tips on creating indexes in sql server 2005. when to created index on single/multiple columsn etc...
I need to create some indexes very urgently.


Thanks..San Digo..
nitinpatel31
nitinpatel31
SSC Eights!
SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)SSC Eights! (881 reputation)

Group: General Forum Members
Points: 881 Visits: 293
There are few thing you need to keep in mind

-How you query the database? Means which fields do you use in WHERE clause and which fields do you use in SELECT caluse. Fields used in WHERE caluse should be used as index column and fields used in SELECT caluse should be used in INCLUDE list.
-What is the INSERT/UPDATE/DELETE frequency on table. You should be carefull while creating the many indexex on single table.
-And for single or multi column index you should check execution plan for major queries of your application. And Try applying the different column combination for good result.

and at last, there is no proven way to create best index for any application. Index creation always depends upon current database usage.

You can try DTA tool to get the index recommendation for current database usage.

Regards,
Nitin
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
nairsanthu1977 (1/6/2009)
Dear All,
Can anyone give me some tips on creating indexes in sql server 2005. when to created index on single/multiple columsn etc...
I need to create some indexes very urgently.

Thanks..San Digo..


It's going to be very hard to give you enough information to create indexes "very urgently." But here are a few tips.

First, make sure you've got automatic statistics turned on. It's on by default, but you never know. Second, run some statistics maintenance (you can look that up in BOL or do a search here) to be sure the statistics are up to date. You need these to make indexes workable.

Next, get to know the execution plans for your queries. That's going to tell if you an index is being used properly. Seeks are good, scans are bad. There's a lot more to it, but that'll get you going.

Next, you only get one clustered index per table. Pick it carefully. My own suggestion is to put the cluster on the most frequently access data path. For example, the table is a parent and the ID is always passed in. There's your clustered index. But, let's say the table is a child that is almost always accessed through the FK, there's the cluster.

After you've got the cluster picked, go back to the execution plans. You may be done. If not, you may need some non-clustered indexes. Pick these based on the WHERE criteria, like the last poster said, and the JOIN criteria. You may or may not want to use columns in the INCLUDE statement to try to make a covering index.

Keep your indexes as small as you can. As few fields as possible and the fields themselves should be as small as possible.

I've just given you enough information to be dangerous.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
Grant Fritchey (1/6/2009)

Keep your indexes as small as you can.


But no smaller than necessary. If the index needs to be four columns wide to be useful, making it only 3 columns wide will probably mean it won't 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


GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
You say you need to create some indexes very urgently, but how can you be sure indexes are what you need if you don't know how to create them?

What problem are you trying to solve with indexes? What makes you think they will solve that problem?

- 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search