Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2004-05-20 : 11:40:06
|
| Hi,I'm trying to determine some basic rules to follow when creating indexes. I just wanted to run them by someone to see if it makes sense (I have read the sqlServer documentation but I just want to make sure I am understanding it properly.)If I have a table that will not be changed very often (primarily select statements) then it would be better to have a higher fill factor (ie. 90%).If I have a table that will be changed fairly often (updates, deletes, inserts, selects) then a lower fill factor is better (i.e. 10%)Does this make sense?Also, I don't see when you would want to add STATISTICS_NORECOMPUTEI know there are a lot of variables involved, but I'm just trying to get an overall picture.Nic |
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-05-20 : 11:53:06
|
| There are lots of variables when dealing with indexes. Try this site as well as the articles on sqlteam for indexes....http://www.sqlteam.com/filtertopics.asp?topicID=163http://www.sql-server-performance.com/optimizing_indexes.asp |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-05-20 : 15:03:09
|
| yes, I realize there are no cut and dry answers. I have read those documents. I just want to make sure I'm directionally correct. If a table is primarily used for select statement is a fill factor of 90% better than 10%?If a table is regularly updated (insert, update, delete) is a fill factor of 10% better than 90%?I just want to make sure I don't have it flip flopped etc.Nic |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-20 : 15:11:59
|
| You are "generally" correct on the select statement table.The update table doesn't have any cut and dry answer. 10% would be ridiculously low though. That's where you need to do the research. Sorry for not giving a black and white answer, but there is none. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-05-20 : 15:27:51
|
| cool. I just wanted to make sure I was reading the documentation the right way.Nic |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-22 : 05:03:29
|
quote:
Originally posted by nic
If I have a table that will be changed fairly often (updates, deletes, inserts, selects) then a lower fill factor is better (i.e. 10%)
If you are doing INSERTs of keys that are sequentially increasing, then they will always be added at the "end" of the index, so a 100% FILL seems to me to be appropriate.I reckon for anything else scheduling index rebuilds at appropriate intervals is the best way to sort out the mess the indexes have become, although setting a reasonable FILL will no doubt help (but only effects initial loading, and loading at rebuild, doesn't it).Kristen |
|
|
|
|
|
|
|
|
index questions