Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tips on creating indexes Expand / Collapse
Author
Message
Posted Tuesday, January 06, 2009 1:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 9:50 AM
Points: 1,458, Visits: 2,435
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..


Post #630357
Posted Tuesday, January 06, 2009 2:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:08 AM
Points: 879, Visits: 274
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.


Best Regards
Nitin

Try this: www.EnlinkURL.com
Post #630387
Posted Tuesday, January 06, 2009 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #630493
Posted Tuesday, January 06, 2009 6:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #630506
Posted Tuesday, January 06, 2009 12:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #630874
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse