Marios Philippopoulos (2/19/2008)
Matt, I completely agree with most of your points, but are you sure about the last one?Heaps don't have page splits.
An update of a VARCHAR column in a table with no clustered index cannot cause a page split in the data pages? I think it can.
Sorry if I'm splitting pages (... I mean hairs 🙂 ), but I want to make sure my understanding is correct here.
No issue at all - it's actually not very well documented IMO. Take a read through this:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
The part that's relevant is of course "buried" most of the way down, but here it is anyway:
For the table with the clustered index, the Page Splits/sec is about four times higher than the corresponding number of Page Splits/sec for the table with the nonclustered index. In addition, the ratio of Page Splits/sec to Pages Allocated/sec for the table with the clustered index is higher and increases more rapidly as the number of processes increases. This behavior is due to the fact that page allocations for a table without a clustered index (heap) never result in a page split (the data is just added to the heap), and there are fewer page splits for the nonclustered index pages because for the given table and index structure, almost twice as many rows can fit in a data page. The ratios of Page Splits/sec to Pages Allocated/sec are depicted in Figure 16.
Meaning - when you run out of space in a heap - you just whack a pointer to the end towards some more space somewhere else and put the "extra info over there" (in a REALLY simplified framing of the behavior). As I recall - that's called a "forward pointer".
Of course - GilaMonster has a better handle on this behavior than I, so I hope she'll stop on by and flesh out the explanation where it's lacking.
----------------------------------------------------------------------------------
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?