asked
In Sql Server 2005, how does Page Fullness affect performance?
In Sql Server 2005, I can view two pieces of data about index fragmentation: "Total Fragmentation" and "Page Fullness". I know that when the total fragmentation gets too high, it can adversely affect performance, requiring an index reorg or rebuild. However, I don't understand the impact of "Page Fullness" and when I should be concerned with it. How does page fullness affect performance?
Page fullness should stay as close to the desired fill factor as possible. For example, if you have a table clustered by an ever-increasing integer (with identity) then there is no reason to have a fill factor less than 100 percent (default) because every new record gets inserted to the tail, there are no page splits caused by inserts. However, if your table is clustered by the uniqueidentifier with newid() default then it would be wise to have a relatively small fill factor for this index (70, 75 depending on the DML activity) because otherwise, inserts will cause a high number of page splits thus both fragmenting the index and reducing the pages' fullness. When a bunch of pages is half full then it means that the engine has to scan through a greater number of pages to fetch desired data. This, combined with the fact that the hard drives physically storing the data are always fragmented at least somewhat, means that every select becomes slower than it deserves to be. Recently, I was giving a presentation about importance of a requirement to have the PK enforced by the clustered index ever-increasing. I had 2 sample tables created and populated with a million records each (both having a PK enforced by a clustered index, no fill factor defined, uniqueidentifier data type with the only difference that the first had newid() while the other - newsequentialid() as default). The initial insert did not cause any problems because the tables were empty, but the insert of 200,000 additional records into each sample table caused the second to survive the operation quite nicely while the first caused the clustered index to become 99 percent fragmented! Additionally, the table with such highly fragmented index surely reduced the page fullness. To sum up, both fragmentation and page fullness relative to its fill factor go in tandem, and the smaller the fullness, the longer it takes to get data. Oleg
@Oleg nailed it for you. Another thing to keep in mind is that full pages consume less disk space and can be retrieved faster when requesting multiple pages. That being said, page splits and fragmentation are still your biggest concerns. I would suggest Oleg's answer be considered the one to accept.
