3 thoughts on “GAM and SGAM

  1. Mala says:

    I have a few questions here..

    How many pages are there in the data file, Out of which first page is for file header and 3/4th pages are for GAM/SGAM?
    What exactly is the file header?

    How from the query results shown above do we understand that how GAM stores information about pages..

    And, if SGAM stores information about mixed extents, that means uniform extent information is stored in GAM?

    Also, what is 2nd page “Page Free Space” used for if we have information about free/allocated pages in GAM and SGAM?

    Like

  2. lokesh says:

    Mala answer to your few questions..

    Q. How many pages are there in the data file, Out of which first page is for file header and 3/4th pages are for GAM/SGAM?
    A. It depends on the size you have allocated for your DB. let’s say you have 1g.b (I know it is negligible but just for fun purpose assume the lowest level as we are not being paid handsomely. We can afford only 1 gb). When u create a dB u assign the space amount for your data files(mdf, ldf, ndf). If you have assigned 100 mb(100000kb) for ndf. Then number of pages in ndf = 100 000 / 8 Approx.( 8 is size of one page in kb.)

    Q. What exactly is the file header?
    A. Hmmm.. Okay.. When we were kids and have dozen of notebooks. What we use to write at cover(other than jai Mata di 😊) name, class, section, subject. A small description about your notebook. Similarly a header whether it is a page header or file header give a small description/definition about the object.

    Q. How from the query results shown above do we understand that how GAM stores information about pages..
    A. If you notice in the snapshot. There is a set of pages and status along with them.
    (1,0) – (1,168) : Allocated.
    1 is my data file id ( mdf in this case) 0 to 168 are page number. So it represent that 0 to 168 pages are allocated. So when sql server need any page it know this information and skip all the pages till 168.
    Q. And, if SGAM stores information about mixed extents, that means uniform extent information is stored in GAM?
    A. Yes, exactly.

    Q. Also, what is 2nd page “Page Free Space” used for if we have information about free/allocated pages in GAM and SGAM?
    A. Great catch! Reading the blog seriously.
    PFS gives you the information at page internal level. Like how much free space a page has. On the other hand GAM and SGAM explain if a page is allocated or not.

    Please refer below link. PFS explained beautifully.
    http://www.practicalsqldba.com/2012/10/sql-serverunderstanding-page-free-space.html

    Like

Shoot Your Comments!

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