MSSQLWIKI

Karthick P.K on SQL Server

SQL Server -g

Posted by Karthick P.K on March 5, 2013

I decide to write this quick blog after seeing a lot of confusion around sqlserver -g switch.  sql server -g switch is nop in 64-Bit sqlserver and it is used in 32-bit sqlserver to increase the size of MTL(AKA MemToLeave).

The default value of -g switch is 256 MB. I.e. if you do not specify value for sqlserver -g switch it is defaulted to 256 MB.

Initialization of sqlserver memory during the startup of SQL Server is as follows.

1. Calculate the size of MemtoLeave and reserve it using the algorithm below

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space (By default 256 MB and can be controlled by -g).

Stack size =512 KB per thread for 32 Bit SQL Server and 904 KB for 32Bit SQL Server running on 64-Bit systems.

I.e = (256 *512 KB) + 256MB =384MB

-g switch is used to increase the additional space from 256 to any desired value.

2. Calculate the size of BPOOL using below algorithm.

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL- BUF structures”

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

Buf structures are arrays maintained by sqlserver to track the status of each buffer in BPOOL . SQL Server makes maximum of 32 allocation requests to the OS to reserve bpool pages.

SQL Server maintains contiguous array to track status information associated with each buffer (8 KB page) in the BPool. In addition SQL Server maintains a second array to track the committed and reserved bitmap pages.

This bit can be 0 or 1 . 1 indicates buffer is committed and 0 indicated page is reserved.

Size of Buf structure is approximately 16 MB when AWE is not enabled and when AWE is enabled buf structures use additional 8MB per each GB of RAM in the system.

3. Release the MTL region which is reserved initially. We reserve MTL at startup and releases it after BPOOL is reserved to ensure MemToLeave region are contiguous.

More details about the SQL Server memory architecture in https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/

If you are in SQL Server 2012 read https://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/

 

Related posts:

Troubleshooting SQL Server Memory

A significant part of SQL Server process memory has been paged out

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

14 Responses to “SQL Server -g”

  1. GS Sohal said

    Hey Karthick, Great quick post ..

    Thanks for sharing …

    just wanted to check .. the first line says ” switch is nop in 64-Bit sqlserver ” .. you meant ‘NOT’ in 64 bit ‘ . is that right ?

  2. […] SQL Server -g. […]

  3. Thanks for sharing your thoughts about personalized mothers rings.
    Regards

  4. Kelle said

    Thanks for your personal marvelous posting!
    I genuinely enjoyed reading it, you’re a great author.I will ensure that I bookmark your blog and will often come back from now on. I want to encourage you to definitely continue your great posts, have a nice afternoon!

  5. Oh guy! Reading you is like sitting near to you everytime.
    …you set my soul in that trip and I’ve noticed your face when you said that San Diego means…oh dude: my face was like (yeah, you have to be suitable)!Can freakin’ waiting to examine everytime new stories from you!
    You’ve to buy a camera and do some kinda of documentary of it, reading what you wrote and filming what you see: can be much better than Discovery Channel! Appear forward to see you in Rome in a very month!!! Speaking about your friend, I truly hope that modern drugs will help him really feel greater the same it occurred to you…Friendship’s just the best drugs
    at times, just give him a 2 minutes-call when you’ll be able to, it assists! Hold ON GREG! WE Care ABOUT TEDD’s FRIENDS TOO!
    “It’s not easy love, but you’ve obtained friends you may trust, Friends will be friends, When you’re in need of love they give you treatment and attention, Friends will be friends, When you’re by way of with lifestyle and all hope is lost, Hold out your hand cos friends will be friends ideal till the conclude …”

  6. Thank you for sharing your info. I truly appreciate your efforts and I will be waiting for your further write
    ups thank you once again.

  7. […] SQL Server -g […]

  8. Marcela said

    This website certainly has all of the information and facts I
    needed concerning this subject and didn’t know who to ask.

  9. Levi said

    you’re really a excellent webmaster. The web site loading speed is amazing.
    It kind of feels that you’re doing any distinctive trick.
    Furthermore, The contents are masterpiece. you have
    performed a wonderful process in this subject!

  10. Tanesha said

    This paragraph gives clear idea in support of the new viewers of blogging, that really
    how to do running a blog.

  11. southlake said

    Good post. I learn something new and challenging on sites I stumbleupon on a daily basis.
    It’s always helpful to read articles from other writers and
    practice a little something from other web sites.

  12. Great blog you have here but I was curious about if you knew of any
    community forums that cover the same topics talked about here?
    I’d really like to be a part of online community where I can get opinions from other knowledgeable individuals
    that share the same interest. If you have any suggestions, please let me know.

    Kudos!

  13. Dena said

    I read this article fully on the topic of the comparison of most up-to-date and previous technologies, it’s remarkable
    article.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: