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 |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2003-03-28 : 05:57:46
|
| Sit down, and get comfortable. I'm having a debate with a coworker and his arguements just aren't ringing true to me and I need some opinions.We have a table called Status_History. This table gets an insert every time a record is touched and it essentially tracks who handled the record, when and what the disposition of that record is. It does an insert into the table each time the record is updated. This is done rather than updating a row so that we have a history of each touch point of the record. Maybe not the best design, but that's what we've got. So, the table, is indexed as follows:Opid, the user who has the record is clustered, ordered ascending, with a fill factor of 80%, no nulls allowed, if it's the first load of the record, the opid is "system".Status_history_id, primary key, identity(1,1). This has a fill factor of 100% and is the index I need help on.Status_id, non unique index,fill factor 80%. This keeps track of the status of the record, essentially the internal status so our website knows how to treat it.Not too important for our exampleDisposition_id, non unique index, fill factor 80%. This keeps track of what happened when the call center rep spoke to the customer. Did they accept/decline an offer,tell us to call back later, etc etc. Basically, it's what the customer said/did. Not too important for our example.Finally, BAN, non unique index, fill factor 80% A BAN, is the customers Billing Account Number.Now, my dilemna is this. My coworker contends that because status history id is an identity column and primary key that the fill factor can be set to 100% because the new records are going to be tacked onto the end of the index and so would never cause page splits when each one is generated. FYI, our site does 7 transactions per second, we probably update this table 2 rows per second, since there are about 150 call center reps right now. My thought is that if it's set to 100%, even though it's tacking on to the end, the table still must generate new pages and extents which is slowing down our performance. It's my opinion that if we set the status_history_id from 100% down to about 80%(or maybe lower) that this would minimize page splits and while there would be some record shuffling that the amount of pages would stay relatively the same in the end and cause much less overhead than constantly splitting the pages.Am I totally off base here? I'm really about 75% convinced of my side but the other 25% of me can see where my coworker is coming from. Help!!! Mike"oh, that monkey is going to pay" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-03-28 : 07:33:25
|
| I'd go with you......however the best proof is for each of you to put 20$ down on the table on a Monday morning.and then adjust the index to 80%.then leave the 40$ there until the following monday morning.then adjust the index to 100%.leave the 40$ there until the following monday morning.then on the 3rd monday.....examine the performance statistics for the previous 2 weeks and whoever delivers the better option can lift the 40$.HTH! |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-03-28 : 07:55:47
|
| Oh, it's going to come to something like that because I don't think either one of us is going to be persuaded. I'm fairly convinced I'm right and I can always make good use of $20.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Index Fill Factor(get comfortable)