(Boston continues its reputation - with me at least - for great seafood - calamari and pan-seared fresh halibut this evening. Yum! I was tempted to get on the T - Boston's subway system - and see what's happening downtown but with no jacket I'd get wet. So, instead I'll be boring and geeky and write another blog post - you guys are spoiled today.)
Hopefully I'm getting close to resolving the chicken-and-egg problem of being able to discuss topics without having to promise further posts to explain terms.
In the previous post in this series I explained what records are. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file.
A page has a structure as shown below (all pages have the same basic structure):
The 96-byte page header is the same for every page. You can see an example of the fields that are stored within a page header in the last post on DBCC PAGE. When considering fragmentation, the most important fields are:
Other interesting fields in the page header are:
It is a very common misconception that records within a page are always stored in logical order. This is not true. There is another misconception that all the free-space in a page is always maintained in one contiguous chunk. This also is not true. (Yes, the image above shows the free space in one chunk and that very often is the case for pages that are being filled gradually.)
If a record is deleted from a page, everything remaining on the page is not suddenly compacted - inserters pay the cost of compaction when its necessary, not deleters.
Consider a completely full page - this means that record deletions cause free space holes within the page. If a new record needs to be inserted onto the page, and one of the holes is big enough to squeeze the record into, why go to the bother of comapcting it? Just stick the record in and carry on.
Ah - but hold on a minute, what if the record should logically have come at the end of all other records on the page, but we've just inserted it in the middle - doesn't that screw things up somewhat?
No, because the slot array is ordered and get reshuffled as records are inserted and deleted from pages. As long as the first slot array entry points to the logically first record on the page, everythings fine. Each slot entry is just a two-byte pointer into the page - so its far more efficient to manipulate the slot array than it is to manipulate a bunch of records on the page. Only when we know there's enough free space contained within the page to fit in a record, but its spread about the page do we compact the records on the page to make the free space into a contiguous chunk.
One interesting fact is that the slot array grows backwards from the end of the page, so the free space is squeezed from the top by new rows, and from the bottom by the slot array.
You know what these are already - we talked about them last time!
Pages are the basic unit of IO that SQL server uses. The buffer pool maps individual pages and torn-page protection and page checksums are implemented per-page. Fragmentation is mostly concerned with pages, and the efficiency of being able to read them in logical order (as defined by their next page pointers).
They can be allocated individually or in extents, which we'll cover next time...
On day-to-day basis a DBA might come across with the issues on the fragmentation on the database, it
PingBack from http://www.glorf.it/blog/2006/07/03/sql-talk/sql-server-storage-engine
PingBack from http://backyardshed.info/story.php?title=sql-server-storage-engine-fragmentation-part-2-what-are-pages
PingBack from http://toenailfungusite.info/story.php?id=4803
PingBack from http://hairgrowthproducts.info/story.php?id=5537
PingBack from http://insomniacuresite.info/story.php?id=8933
PingBack from http://thestoragebench.info/story.php?id=5258