Process of storing data in a database.

You might already have read many a times about different allocation bit/byte maps present in SQL Server which are GAM, SGAM, PFS, IAM, BCM, DCM. If you don’t know that refer this to learn about it. None can explain it better than Paul Randal.

The gist for all the allocation bit maps is that these are the pages in SQL Server which are used by the Database Engine when storing data into some pages. So this W&H is about how SQL Server uses these pages to find out the right page to store the data.

For finding and seeing what is being done, I created an empty database and name is as “Testinglogs”. And than created a table named as test and inserted a single value into it. (The reason for wonderful and creative names is that I was sick since last 3 days and I think my brain is not willing to spend time in thinking about a name :))

For checking the log I’ll be using the below query: GitHub Link.

SELECT	[Current LSN],
		Operation,
		Context,
		AllocUnitName,
		[Page ID], 
		[Slot ID], 
		[Transaction Name], 
		[Lock Information],
		Description 
FROM fn_dblog (NULL, NULL);
GO

This is what I have done till now, created the table, manual checkpoint and inserted a value into the table.

--Creating a table
CREATE TABLE test
(NAME VARCHAR(50))

--Checking logs generated for this
SELECT * FROM fn_dblog (NULL, NULL);
GO

--Checking pages associated with the table test
DBCC IND (0,'test',0)

--There are no pages allocated, lets enter 1 value and check it again

CHECKPOINT; --To remove the logs generated for creating the table

INSERT INTO test VALUES ('abc')

--Checking the logs again to see what has happened
SELECT	[Current LSN],
		Operation,
		Context,
		AllocUnitName,
		[Page ID], 
		[Slot ID], 
		[Transaction Name], 
		[Lock Information],
		Description 
FROM fn_dblog (NULL, NULL);
GO

It’s output is as below:

Every thing in SQL Server runs as a transaction whether explicit or implicit. In this case as we have not started the transaction manually SQL Server does it for us which starts at line 4 and ends at line 31.

LOP_BEGIN_XACT initiates the transaction
LOP_COMMIT_XACT is the successful commit of the transaction

1. The first allocation bitmap comes into picture at line 8 and which is GAM, SQL Server uses it to find an empty extent. DE finds the extent and allocates it “Allocated 1 extent(s) starting at page 0001:00000150”.

(copied the whole output into a excel sheet for better visibility)

2. Next it goes to PFS page to find the free space page from the allocated Extent. The information it has for that extent is like this

0x00–>00 0001:00000150;0x00–>00 0001:00000151;0x00–>00 0001:00000152;0x00–>00 0001:00000153;0x00–>00 0001:00000154;0x00–>00 0001:00000155;0x00–>00 0001:00000156;0x00–>00 0001:00000157

As you know the extent are 8 pages it returns back the information of all the 8 pages.

3. The first page is locked and allocated to dbo.test allocation unit.

HoBt 0:ACQUIRE_LOCK_X PAGE: 34:1:336 

4. Similarly another page is locked and allocated which is going to be the first IAM page for this table.

HoBt 72057594043105280:ACQUIRE_LOCK_X PAGE: 34:1:309 

5. Now the table has the two pages it requires, IAM (after being formatted so that can be used as a IAM page) and Data page (still not formatted)

6. The next bitmap that comes is the DIFF MAP. The important thing to note is that bit is set for the extent even before the actual data is even written to it.

SetBit 0001:00000150

7. Now SQL Server can write the data into page, but before doing that it formats it into a HEAP (as we dont have clustered index) and than inserts the data at line 29 into the page.

8. Post that it has to again update the PFS page so as to set the new free space available in the page.

9. Finally commits the transaction.

I hope it helps in understanding how the allocation is done for a new table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s