help on indices

are there any thumb rules to follow to set up indices on tables and to set the fill factor. can somebody suggest any link where I can read about this.
thanks in advance.
RohitSQL Server Central has some real good articles. Unfortunately, I haven't found any standard guidlines for creating indexes and setting fill factors. It realy depends how your system is designed and what you're trying to do. I found testing is the key and learning simply by trial and error.

Heer's the link:
http://www.sqlservercentral.com/|||The biggest factor I have found is the placement of the clustered index. I typically place the clustered index on the identity column (if I have one) so I don't have to worry about pagesplitting as new data always falls at the bottom of the leaf level. You're big thing with fill factors is you are paying a price in storage to reduce or eliminate page splits during operations. Kalen Delaney's Inside SQL Server 2000 is good for researching indexing and page splits.

HTH|||Page splitting is really what can eat time. But that only happens when a new key-value is inserted or changed "at random" or when a record-delete occurs. It really depends on your data and your application. In an awful lot of applications, deletions and even key-changes occur "almost never."

I'd suggest that you make no changes at all unless, after quite some time of running the database, you can establish that insert/delete/change-key activity is really creating a "human noticeable" performance problem. Establish that; don't "fiddle and tweak and guess."

Another thing to really bear in mind is that every index you place on the database ought to have a clear purpose for being there. It's real easy to have too many, and I think that's what really munchie-munchies the time away for any database. Some indexes might need to exist only for certain purposes, e.g. once-a-month, in which case it might be profitable to drop or deactivate them except when they're needed. (A computer can rebuild an index "from scratch" uncommonly quickly. Obviously if you are dealing with "billions and billions" of records, you might not... etc.)

An index will never be "optimal." Not at any time; not if it's being used. :p They don't need to be reorganized frequently... they will maintain themselves. Let them do that! This means that they will always have fairly-empty pages, somewhat lopsided record-counts in the tree... and "who cares." You should really only plan to take action when, and if, you factually establish the presence of unacceptable pain in the current status quo.|||The reason you would care about the amount of empty space in your indices are that it makes your queries have higher logical IO than neccessary.

To see if you have page splits issues, run perfmon and check your page splits/sec. To check your fragmentation level of your tables run dbcc showcontig, to see your logical IO and how you can tune your queries, use "set statistics IO on"

HTH|||Now I know where to start from, thank you all for the inputs.
Rohit