September 24, 2009 at 1:38 pm
if I set my fill factor at 50 now every other row there is a blank,with a 100 row table, 50 rows per page, total of 2.
Now I do a delete of the first 50 rows... sql will empty the first page( i know there could be ghost records for a time) and leaving it blank page. assuming I am deleting the top of the order
Will the next insert fill the first page, assuming that is where the data should go according to the cluster index.
or will it create a new leaf page, fill it and place it at the end of the chain and continue to leave the first page empty until the next rebuild?
THANKS...
September 24, 2009 at 1:53 pm
I may be misreading this, but if I understand you correctly, that's not precisely how fill factor works.
Data is stored in 8k pages. If you have small rows, like an pair of integers in a many-to-many table, you'll have a lot of rows per page, since 2 int values is 8 bytes per row. Even adding in the per-row overhead, you'll probably not more than double that to 16 bytes per row, which means 1 page would be approximately 500 rows at 100% fill factor. At 50%, each page would be 250 rows.
It's not a question of alternating blank and filled rows, it's how full are the pages when they are intially created.
The rows will be stored in the pages in the order of the clustered index. The pages will ideally be ordered that way too, but may or may not be, and may or may not even be contiguous, depending on the level of index fragmentation.
Deleting doesn't control what pages will be reused or not. That's going to depend more on the inserted data's relationship with the clustered index.
- 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
September 24, 2009 at 2:28 pm
cool I was interested in this
"Deleting doesn't control what pages will be reused or not. That's going to depend more on the inserted data's relationship with the clustered index."
we often have a clustered table that gets 500 row deletes and then 500 row interest using the same key.
I am using sys.dm_db_index_physical_stats, and I am showing space per page @ 50% with 90% fill factor, bad numbers on avg frag as well. which tells me that there is page splitting going on.
I know I can rebuild the index and this will fix it, but I was trying to find what caused it? and if I need to do a rebuild after each new big delete and reinsert. I will watch it next time I do a big insert and see how it is effected.
THANKS Again
September 24, 2009 at 2:55 pm
Two thoughts:
- how did you come to pick the clustered key combination? Something resulting in a lot of page splits might not end up being the very best choice for the clustered index. Even if that key is great as the Primary key, perhaps switch the clustered key to something less prone to page splits, tying the primary key to a non-clustered index instead.
- You might not have meant it this way - but are you using a non-unique clustered index? Assuming you are, remember that SQL server will make your clustered key unique by tagging on something additional (some random 4-byte integer as I recall), which can then scatter the new values (and possibly push up your page splits again)..
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2009 at 7:25 am
Thanks Matt both Great points. I was aware of them both but had not thought about it
Seems that the cluster index is composite but looks like I can drop a col.that might help
My guess is all the deletes and updates are guess causing the page splits to these tables looks like i will have to add an index rebuild to the end of the SP.
It appears that the contents of the table is deleted each time a a big insert occurs. Looks like this is the cause
Thanks again guys
September 28, 2009 at 8:54 am
Deletes don't cause page splits. Inserts and updates that are out-of-clustered-sequence do.
If page splits and fragmentation are the problem you're working on, focus on inserts and updates that affect the clustered index, and ignore deletes for now.
Deletes can cause wasted disk space, but they can't cause index fragmentation.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply