May 10, 2008 at 10:39 am
I am beginning to get my hands dirty here with the internals of SQL 2K. I had a question on the indexes, well actually a couple -
1. Isn't it a good idea to have indexes on large tables (like 8GB) placed on a seperate physical disk?
2. If the huge tables on which these indexes are defined get truncated on a regular basis through maintenance jobs, does including "Rebuild indexes" add any value to the maintenance job functions?
3. About fill factor, isn't that something that comes into play ONLY when Indexes are created and not kept track of when indexes are update because of corresponding table changes? So if that is the case, and as I said since huge tables in our environment are truncated on a regular basis through maintenance jobs, do we have any good to come out specifying a Fill Factor if and when we have to recreate any of our tables?
Thanks for your guidance!
Leon
May 10, 2008 at 5:23 pm
leonp (5/10/2008)
1. Isn't it a good idea to have indexes on large tables (like 8GB) placed on a seperate physical disk?
It CAN be, but your mileage may vary. Separate Indexes carry their own costs (extra filegroup(s), extra file(s), extra disk(s) managed, potential conflicts/contention on more disks, increased dependency's, etc.) that should be evaluated vs. the benefits that you may or may not receive. Generally it is *more* important to get you log files and TempDB files separate from your mdf's. As that pushes you to 3+ disks already, adding even more disk dependency probably should'nt be done unless you determine that you need it.
2. If the huge tables on which these indexes are defined get truncated on a regular basis through maintenance jobs, does including "Rebuild indexes" add any value to the maintenance job functions?
If by "truncated" you mean the TRUNCATE TABLE command, then yes, you should rebuild the indexes after you reload the table. Even better, drop the indexes before-hand, and just add them afterwards.
3. About fill factor, isn't that something that comes into play ONLY when Indexes are created and not kept track of when indexes are update because of corresponding table changes? So if that is the case, and as I said since huge tables in our environment are truncated on a regular basis through maintenance jobs, do we have any good to come out specifying a Fill Factor if and when we have to recreate any of our tables?
Fill Factor is used whenever indexes are created or *rebuilt*. Since you may be doing that every night, it is probably worthwhile. Fill factor is a way to avoid/reduce bucket splits resulting from index insertions. since you should be rebuilding them every night, a small fill-factor (0 to 10) would probably be appropiate.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 10, 2008 at 5:44 pm
Barry
Thanks!
1. I agree ... these are significant considerations in the cost-benefit of locating the indexes on a seperate disk
2. I am sure missing something ... wouldn't truncate table clear all index page entries too, so that INSERT batches after the truncate would be practically new entries into the then empty Index pages of the Table. What would be the benefit of rebuilding the Index, or the worse, the cost of not rebuilding?
3. So if it turns out that I have to rebuild Indexes inspite of the Truncates, I should be rebuilding them with a Fill Factor of 0 to 10 since these tables are to be filled in due course??
Leon
May 10, 2008 at 8:53 pm
It depends on how they get loaded after they are TRUNCATEd. If they are loaded in one statement, you probably wouldn't need to rebuild them, but if they are loaded one INSERT at a time, you might need to.
The thing is, however, it is usually faster to just drop (or disable) the indexes before-hand and then recreate (or enable/rebuild) them after the load.
As for the fill-factor: that depends on whether there are a lot on INSERTs (or UPDATEs that change key columns) during the day. If so then throwing on a small fill factor might help a little. It's no big deal though, you would probably be fine to just ignore it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply