Boost performance and -g512 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Boost performance and -g512

This is related to the question i asked here: my coworker was looking to do the same in SQL2005/64bit, but he couldnt find it under "properties".. No such thing in SQL2005? (boost performance and setting memory to -g512)?
-g switch is to increase MEMTOLEAVE memory which 256MB by default in sql2000 32 bit..and an explanation from GURU KEN HENDERSON.
Mohammed U.

read it but didnt understand it much…we’re on sql2005/64bit…in sql2000, we could right click on the server, choose properies and then change that setting (from that first link i sent, it’s not a good idea but that’s what the vendor is asking us to do)… so no such settings in sql2005?
I don’t see any requirement to perfom such switch change on a 64 bit system, as it is already equipped with more memory to tackle the resource intensive processes. fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
it is very important to understand that this is virtual memory, ie, address space,
not physical memory,
the 64-bit version has 8TB user mode address space,
compared with 2-3GB for 32-bit, hence the importance of setting aside a certain amount for functions other than data buffers also, -g xxx does not boost performance.
it helps reduce the chances of a badly design app from crashing

He wants to check the "AWE" in sql2005, i found this : "The AWE memory above 3GB functions as an extension of the BPool and provides additional
space for caching data and index pages."
1.should that be checked? 2.One more questin about that -g512… does sql2005 have that setting? or was it removed in sql2005? The app IS a memory hog…
1. Kind of Yes, read the following articles…
Address Windowing Extensions (AWE) and SQL Server
Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform. On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server. From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information. Host: EuanG (Microsoft)
Q: SQLDBA: Is support for more than 4GB improvised in 2005 ? Do we still monkey with AWE and /3B and /PAE switches?
A: The 4GB config is part of Windows, in SQL2000 we can support 64 GB of AWE on 32 bit and 512GB of RAM on 64 bit. 2. -g switch still exists… you can check BOL Mohammed U.