In my previous blog, I tried to explain How SQL Server Manages your data in pages and extents. (Link for previous blog:  https://sqldose.wordpress.com/2015/08/30/how-sql-store-your-data/ )

But how SQL Server maintains information about the pages? How SQL Server know which page is free and which has been allocated?  There has to be a metadata* for the above.  See no matter how fast, how magical SQL Server is. Ultimately it is been made by developers who look like me( a little less smart but surely richer.. arghhh ). So, Everything SQL server does it need to acquire that information from somewhere.

This is where GAM(Global Allocation Map) and SGAM(Shared Global Allocation Map) comes into picture.

GAM and SGAM itself are pages. In your datafiles(.mdf, .ndf), The First Page is reserved for File Header. Second Page is for PFS(Page Free Space), Third and Fourth Page is for GAM and SGAM Respectively.

Let us dig more in GAM(I like this name J JAM) and SGAM.

Global Allocation Map  :

GAM stores the information whether a page is being allocated or free. GAM has a bit for every extent. 1 represent extent is free and 0 is exactly the opposite.

Shared Global Allocation Map :

SGAM hold the information about the mixed extents. Again SGAM is bit for an extent. 1 represent that Extent is a mixed extent and it have at least 1 page free to be shared. 0 represent either the extent is not a mixed extent or it is mixed extent but does not have any free page to be shared.

Isn’t it nice when whatever theory we read can be seen live through some magical query. I love that. Whenever I read something theoretical, First thing i say is “OK! Theory was boring show me some practical now”.

Below are few magical queries for you.

DBCC Traceon(3604) : It is just to activate the query DBCC Page. Without setting up the Trace Option, DBCC Page will not give any result

DBCC Page(DBName, FileName, PageNumber, PrintOption) : Result of this command will give you the information about the page Including the data within it. However it will not be in best readable format but you will be able to figure out.

DBCC Page (‘myPrac’,1,2,3) => ‘myPrac’ is my database name, ‘1’ is for primary data file which is where my data actually stored(.mdf). ‘2’ is for page number 2. As I previously mentioned Third Page is for GAM. Do not get confused, ‘2’ represent the third page. As Page Number Count Starts from 0. ‘3’ is for print option. Other Print Option Available are 0,1,2.

Highlighted field represent that All the extent From Page 0 to 168 is being allocated. Page from 176 till 248 are available.

Similarly you can check for SGAM by replacing the page Number with ‘3’.

Shoot me with your questions and views.

*Metadata :  The Most basic Definition about metadata is “The Data About Data”. Wooo! What that means?  Let us say you have a DB in SQL with a table ‘Customer’. All the information present under this table is your data. Now you want to keep the track of by whom and when this information is being added to customer table. For that you have a separate table CustomerRecordInfo. CustomerRecordInfo is acting as metadata because it has the data which give me some information about the data present in Customer Table. I hope this explains what metadata is.