codebyo (9/8/2011)
If my clustered index's fill factor is set to 80% then 20% will be free for new rows. Adding a new column will not use that space and the page needs to be split to acomodate the new column but also the 20% free space will need to be kept. Would that be correct?
No.
Fill factor is set on rebuilds only. Anything that needs to grow/insert on the page will use the free space..
You could still easily end up splitting. In your example there's no fill factor set, so unless it's an overall database setting (bad idea) those pages will be 100% fill. Even if they are left 20% full...
Your rows are about 20 bytes in size. (int = 4, varchar = size of data + 2). So simplifying the maths, ignoring row headers, null bitmaps, the slot array and other such details, and with 20% fill factor that's 6448/20 rows per page. 320 for a nice round number. There will be 1660 bytes left free.
Persisting the column widens the row by 17 bytes. To widen all the rows on a page needs 17*320 bytes. That's 5 440 bytes, more than 3 times the space that's actually left free on the page. That page, and every other page in that index is splitting, possibly more than once.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability