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.
MSSQLWIKI 
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 ?
Karthick P.K said
Yes NOP= No operation
SQL Server -g | SQL DBA learning curve said
[…] SQL Server -g. […]
Social Media Sites said
Thanks for sharing your thoughts about personalized mothers rings.
Regards
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!
poemreader.ning.com said
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 …”
consciencism.wordpress.com said
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.
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] SQL Server -g […]
Marcela said
This website certainly has all of the information and facts I
needed concerning this subject and didn’t know who to ask.
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!
Tanesha said
This paragraph gives clear idea in support of the new viewers of blogging, that really
how to do running a blog.
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.
Wedding Entertainer said
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!
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.
Von Lickert said
Roseann Creitz
301 Moved Permanently
7Lab Pharm for Sale said
[…] Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall Reviews Roidsmall […]