Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


The 8k Page
Published: May 22, 2015
This will be a relatively short post simply explaining the way in which SQL Server stores databases records on disk. The reason that I’ve included this post is because I’ve come across a good few people who didn’t realise this was how SQL Server worked and yet this is an important precursor to my next few posts.

I’ll not be going into too much detail surrounding the makeup of the page, but will cover a few salient points which will hopefully clear up a little mystery surrounding SQL Server storage and reads. Therefore this is a post that I can see myself revisiting a few times, but I wanted the basics in here immediately to help with future posts.

So… how does SQL Server store your data on your hard drive?

I’ve spoken to a good few people who happily believe that SQL simply writes records to disk “as is”. As in, you create a record, SQL writes that record to the next free portion of the hard disk, wherever that may be. However, this is not the case at all.

There are a few intricacies I’m going to gloss over here (specifics around internals such as the slot array) but it will allow you to understand the concept of what’s happening internally.

Let’s say that you have a blank database and blank table… you want to add some data, so where does it go? Well, SQL Server creates 8k data pages within the data file and uses those to store your data.

Now, there are a couple of key points to note here and that is that SQL Server requires certain parts of the page to function as internal only. For example, references back to the table and database or a slot array which it uses to know where each record starts and in which order it’s being held.

Anyway, because of this internal usage (96 bytes per page), an 8k page (8192 bytes) has just 8096 bytes available for record data.

The next step is to look at how a record itself is stored in a page.

Basically a record is put together as follows:

Record Header:
  • 4 bytes

  • Fixed Length Columns

    NULL Bitmap:
  • 2 bytes
  • Additional 1 bit per column in the table


  • Variable Length Offset:
  • 2 bytes
  • Additional 2 bytes per variable column

  • Therefore if we had a table such as the following:

    create table testRecord
    (
      
    id int identity(1, 1) not null,
      
    randomString char(10) not null default('a'),
      
    myVariable varchar(10) not null default('b'),
      
    myDate datetime not null default(current_timestamp)
    )


    Then, by the following formula we could fit 218 records in each data page on disk…

    4 bytes (header) +
      8 bytes + 10 bytes + 8 bytes (fixed length) +
       2 bytes + 1 byte (NULL bitmap… 4 columns, therefore 4 bits which uses 1 byte) +
        2 bytes + (2*1) bytes (Variable Offset)

    = 37 bytes

    So...
      8096 / 37 = 218 records per page.

    Why does this all matter?

    This will all become clear in my subsequent posts as although you may not realise it straight away, knowing this can make a vast difference to the way in which you view, design, and use SQL Server in the future.
    Leave a Comment
    Your email address will not be published. All fields are mandatory.
    NB: Comments will only appear once they have been moderated.

    SQL  World  CEO
    Kevin  Urquhart

    iPhone Selfie

    I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

    Categories


    © Copyright 2020 SQLTraining Ltd.