Search results matching tags 'Database Administration' and 'Memory Allocation'

TSQL Tuesday #11 Misconceptions – Enable AWE on 64bit SQL Servers

jmkehayias — Tue, 12 Oct 2010 16:58:25 GMT

This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

Next to SQL Server having a Memory Leak, the misconception that you have to enable AWE on 64 bit SQL Servers has to be one of the most prevalent pieces of incorrect advice that I see on the MSDN Forums. This has been blogged about multiple times, yet it continues to be a problem.

Paul Randal: A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers
Bob Ward: Fun with Locked Pages, AWE, Task Manager, and the Working Set…
Books Online: awe enable Option

This post was originally started in early March of this year, and I haven't ever gotten back around to finishing it based on a thread on the MSDN Forums that I got involved with where misinterpretations of posts by Bob Ward and Slava Oks were the center of the confusion. (Note here that I said it was someone's misinterpretation, and not incorrect information by Bob or Slava whose posts point out that ‘awe enabled’ is a NoOp in 64bit systems) It seems that the confusion here centers around information that are contained in a couple of blog posts by Slava and Bob where they provide some internals information about memory in SQL Server and use terminology that can be confusing to people just learning about memory internals.

The most common reference that people provide for using ‘awe enabled’ on 64bit servers is Slava Oks blog post Be Aware: Using AWE, locked pages in memory, on 64 bit, where he says:

To some people it comes as a surprise that AWE mechanism is still present and actually could be useful on 64 bit platforms. As you remember the mechanism consists of two parts allocating physical memory and mapping it to the given process's VAS. The advantage of allocation mechanism is that once physical memory is allocated operating system can't reclaim it until either the process is terminated or the process frees memory back to the OS.

Notice that this says AWE mechanism, not enabling AWE. What exactly is the AWE mechanism? It is just a different memory API, AllocateUserPhysicalPages(), used to allocate the memory. On a 64 bit server you enable Lock Pages in Memory to make use of this API instead of using VirtualAlloc() to allocate all of the memory for the buffer pool.

The other common example cited by people is the following comment by Bob Ward on his blog post listed above:

Well….the developers of the product discovered that if they still use the AWE APIs to allocate memory even though it is not really needed, two things would happen:

A small performance gain occurs within the kernel. For more details, read this blog post from Slava Oks: http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
Just as with 32bit systems, any memory allocated using the AWE API is not part of the working set and therefore cannot be paged to disk. Therefore it is considered “locked”.

Thus was born the concept most refer to as “locked pages” for 64bit SQL Server editions.

On more than a few occasions people have pointed to this statement as being proof that you have to use ‘awe enabled’ for a performance boost in 64 bit servers. What always amazes me is that in the same blog post, Bob specifically addresses this:

2. Do I need to use the “awe enabled” sp_configure option on 64bit systems for SQL Server to “lock pages”?

No. In fact, the code for SQL Server for 64bit systems ignores this sp_configure option. It is a “no-op” for 64bit SQL Server systems. You may ask why is this the case if I just told you that AWE APIs are used in 64bit SQL Server systems to “lock pages”?

The answer is based on the purpose for that sp_configure option. The purpose of this sp_configure option on 32bit systems is for the user to “enable” the “AWE” feature, which is I explained above is to extend the ability to reference memory > 4Gb. Now as I mentioned already in order to use the AWE APIs you must have the “Locked Pages in Memory” Privilege. So, when you try to use sp_configure to set ‘awe enabled’ on a 32bit we actually will fail this command if “Locked Pages in Memory” is not set.

You don't have to do a thing with "awe enabled" on 64bit servers to scale memory, and you don't even need to use Lock Pages in Memory. A 64 bit server can utilize the memory available on the server entirely through calls to VirtualAlloc() because it has 8TB (7TB on IA64) of user mode Virtual Address Space within which to allocate the available memory. A 32 bit server doesn't have this ability since it only has 4GB of total Virtual Address Space spit 50/50 into user and kernel modes. The maximum amount of memory that a 32 bit SQL Server can allocate without using AWE is around 1.6GB through VirtualAlloc(), or 2.6GB if the /3GB boot.ini switch has been used (which consequently limits the maximum amount of memory that could be mapped with AWE enabled since it reduces kernel mode address space and the available Page Table Entries for mapping AWE allocated memory).

The performance benefit that Bob and Slava talk about has to do with the fact that when Lock Pages in Memory is set, the memory allocated can't be paged out by the Operating System, a problem that exists when the memory is allocated using VirtualAlloc() only, allowing SQL Server to manage its memory entirely on its own. This is specifically important on NUMA systems as detailed in Slavas blog post. On a 32 bit server having the buffer pool paged is bad, but its impact is low since the buffer pool is 1.6GB. However, on a 64 bit server with 32GB or memory that has 28GB allocated to the buffer pool, the impact of having the memory paged out is very significant. When the SQL Server Service account has the Lock Pages In Memory set in the User Rights Assignment the database engine in Enterprise Edition, or Standard Edition with the correct Service Pack, Cumulative Update, and trace flag 834 set, will use the "AWE Mechanism" to lock the memory allocated for the Buffer Pool.

So there you have it, once again, there is no need to enable AWE on 64bit servers.

Troubleshooting the SQL Server Memory Leak (or Understanding SQL Server Memory Usage)

jmkehayias — Tue, 25 Aug 2009 01:32:00 GMT

I guess this could be considered a continuation of my last post about SQL Server Memory, Understanding the VAS Reservation (aka MemToLeave) in SQL Server, but for a long time I have noticed that when people don’t understand how SQL Server uses memory on their server, they immediately begin to think that SQL Server has a memory leak. So in this post I’ll dive into how SQL Server allocates and uses the physical memory available on a server.

Proper management of server resources is crucial to maintaining good performance, and as we all know a server only has a finite amount of resources. Often when performance problems occur, the first counters looked at by system administrators are the CPU utilization and Memory Utilization counters, most often I would suspect through the Windows Task Manager and then PerfMon. When a Windows Administrator/User that doesn’t understand SQL Server sees that SQL Server is consuming 2.7GB of memory on a 4GB server, for whatever reason, the first conclusion that they come to is that SQL Server has a memory leak. Nothing could be farther from the truth. This has become even more common lately as 64 bit servers become more common and the expanded VAS (I covered this in my last post) allows SQL Server to commit all of the available memory on the server without advanced configuration of options like AWE.

So what exactly is going on here? Well first recall that SQL Server has two types of memory usage, VAS (aka MemToLeave on 32bit servers) and the Buffer Pool or BPool. When SQL Server starts up the maximum size of the BPool is calculated based on the servers configuration. For 32bit servers without AWE enabled, the VAS reservation is calculated and reserved first, then the remaining user mode VAS is compared to the amount of physical memory available on the server. The smaller of the two values is then compared to the max server memory configuration option and the smallest number becomes the maximum size for the BPool. For example, a 32bit server with 4GB RAM, the /3GB boot.ini switch, and a max server memory configuration of 2048MB the maximum size of the BPool would be 2048MB:

3072MB (3GB) user VAS - 384MB = 2688MB available VAS
4096MB (4GB) Physical Memory > 2688MB available VAS
2688MB available VAS > 2048MB max server memory configuration

However, when AWE is enabled the BPool maximum size isn’t restricted by the available user mode VAS. Instead the maximum size of the BPool is based on the physical memory on the server or the max server memory configuration setting, whichever is smaller. For example, a 32bit server with 8GB RAM, the /PAE boot.ini switch, AWE enabled, and a max server memory configuration of 6144MB the maximum size of the BPool would be 6144MB:

8192MB (8GB) Physical Memory > 6144MB max server memory configuration

For 64 bit servers, there is no need to use AWE to allocate memory above 3GB for SQL Server since the user mode VAS is 8TB there is always ample VAS to utilize all of the physical memory available on the server. However, the AWE mechanism for allocating memory is still useful and available on 64 bit servers. It is used whenever the the Lock Pages in Memory security right has been granted to the SQL Server Service Account and can improve the stability and performance of a 64 bit system. (see Slava Ok’s blog post Be Aware: Using AWE, locked pages in memory, on 64 bit ) For 64 bit servers, the maximum size of the BPool is the size of physical memory or the max server memory configuration, whichever is smaller. For example, a 64 bit server with 16GB RAM, and a default max server memory configuration, the maximum size of the Bpool would be 16GB.

Once the calculations have been made, the address space for the BPool is reserved, but not committed. SQL Server will only commit the physical memory as it needs it. However, and this is what most people don’t seem to understand, once committed the memory will not be release back to the operating system unless the server gets into memory pressure. (see Slava Ok’s blog post Q & A: Does SQL Server always respond to memory pressure?) When data is needed for a query, SQL Server first checks the BPool to determine if the data is already available. If it is, the data in the BPool is used to run the query, which is why performance improves after the first time a query executes. If the data isn’t in the BPool, it is read from disk into the BPool where it is then used to run the query. SQL Server attempts to keep data loaded in the BPool for as long as possible once it has been physically read from disk, and will commit additional memory up to the maximum calculated limit to accomplish this. Once the maximum calculated limit has been reached, stale data is flushed from the cache by the memory clerks based on the counter ticks maintained internally in the caches (See Slava Ok’s blog post SQLOS Caching).

Since the operating system still requires memory to operate, you should set the max server memory configuration option to ensure that the operating system has sufficient memory once SQL Server has committed its memory for use. This is especially important on 64 bit servers where the larger VAS allows for the SQL process to take all of the physical memory which can result in a paging situation for the SQL processes, degrading performance. For this reason, 64 bit servers should use the Lock Pages in Memory right so that the AWE Mechanism is used to allocate memory, preventing paging of the BPool. In the RTM of SQL Server 2005 and SQL Server 2008, this was a Enterprise Edition only feature. This however was changed based on community feedback and is available in Standard Edition through the use of a trace flag 845 when the appropriate updates have been applied to SQL Server (see An update for Standard SKU Support for Locked Pages…)

For further information see the Memory Management Architecture Topic in the Books Online

Some example posts on this topic from the MSDN Forums are:

SQL server memory leak
how to release memory from sql
Max Workspace Memory (KB) is high
sql takes too much ram
Memory consumption for SQL Server
Memory Consumption?
sqlservr.exe is continually eating more memory
Memory getting full

Hopefully this clears up the misunderstanding regarding how SQL Server allocates and consumes memory.

UPDATE:

Based on Aaron’s comment (thank you Aaron for pointing it out), I should clarify that the Task manager doesn’t show the correct numbers for memory allocated to SQL on 64 bit servers and servers using AWE. To get the amount of memory used, you should read the following memory counters:

Performance object: Process
Counter: Private Bytes
Instance: sqlservr

Performance object: Process
Counter: Working Set
Instance: sqlservr

The Private Bytes counter measures the amount of memory that is currently committed. The Working Set counter measures the amount of physical memory that the process currently occupies. SQL Server also uses the following performance counter to expose the amount of memory that the buffer pool allocates:

Performance object: SQL Server:Memory Manager
Counter: Total Server Memory(KB).

Note If the instance of SQL Server 2005 64-bit is a named instance, the name of the performance object is MSSQL$InstanceName: Memory Manager.

Understanding the VAS Reservation (aka MemToLeave) in SQL Server

jmkehayias — Tue, 07 Jul 2009 05:41:00 GMT

For a long time now I've had a Word Document I was working on to cover the subject of the VAS Reservation (aka MemToLeave) in SQL Server. I've been quite busy this year, so I never got around to completing it, and I wasn't sure of where I wanted to publish it, on my blog or as an article submission somewhere else. Today an article on SQL Server Central titled SQL Server Memory Configuration, Determining MemToLeave Settings was published that has incorrect information and fails to adequately cover the subject. I've left a comment on the article, but the subject of VAS Reservation in SQL Server is to vast to cover in the comments on a article that are unlikely to be read by someone who has a problem with VAS in their SQL Server instance.

To start off with, MemToLeave is a moniker, or even better put a misnomer. It was named MemToLeave based on the way that SQL Server allocates memory when it starts up, but it lead to confusion over the idea that it was memory that SQL Server wouldn't allocate. The appropriate terminology for what is commonly known as the MemToLeave is the VAS Reservation. This area of memory in SQL Server is frequently the source of many questions online that relate to Out of Memory Exceptions inside of SQL Server, and it is one of the most misunderstood portions of SQL Server memory. Before getting into the internals of SQL Server memory, lets first take a look at Virtual Address Space in general, and the differences between 32 bit and 64 bit servers.

Differences in VAS Sizing 32bit vs 64bit

Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB. This is by default split 50/50 into kernel mode and user mode VAS regions. Applications like SQL Server, run inside the user mode VAS region (this article will forgo discussions on AWE and PAE which can be used in conjunction with one another to allow the SQL Server Buffer Cache to utilize memory beyond 4GB), which is why non-AWE instances of SQL Server max out their buffer pool size around 1.6GB for a server with 4GB of memory installed. To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined proportion (/USERVA) to allow SQL Server to address more memory for its buffer pool.

Servers that are x64/IA64 or 64 bit have a VAS region that is 2^64 in size or roughly speaking 16 trillion GB (I am sure there is a proper name for this size, but I don't know it off hand, lets just call it a ridiculously large number for today's memory needs). Since this is well beyond the physical capabilities for memory, the user mode and kernel mode VAS are capped at 8TB each for x64 based 64 bit servers (IA64 has a 7TB user mode cap). This expanded VAS changes the dynamic of how applications such as SQL Server allocate memory.

Startup Allocation of Memory

When SQL Server starts up, the first memory item that is reserved by the SQLOS is the VAS Reservation memory area. This is a special contiguous memory area that is separate completely from the Bpool, and is for use by external consumers such as Extended Procedures, COM objects (OLE Automation calls), Linked Servers, OLEDB providers, SQL CLR, and is also used for allocations larger than 8KB (>8KB), also known as multi-page allocations, by the database engine (extemely large and complex query plans). It has a calculated fixed size that is determined at startup based on the following formula:

(MaxWorkerThreads * StackSize) + DefautReservationSize

The MaxWorkerThreads default on SQL Server 7.0 is 128 and on SQL Server 2000 it is 255. However, on SQL Server 2005 and 2008 it is dynamic and depends on the number of schedulers (processors) you have configured in the SQL Server. The formula for the number of worker threads is:

((NumberOfSchedulers - 4) * 8) + BaseThreadCount

For 32 bit SQL Servers the BaseThreadCount is 256. For 64 bit SQL Servers the BaseThreadCount is doubled to 512.

Note: On SQL Server 2005 and 2008, if the number of schedulers is less than four, then the BaseThreadCount is the default value for the MaxWorkerThreads; 256 for 32 bit and 512 for 64 bit servers respectively. You can also query this information from the system:

select max_workers_count
from sys.dm_os_sys_info

For a 32 bit SQL Server the default StackSize is 512KB so to determine the memory allocation to the VAS Reservation for a 2 processor 32 bit SQL Server the calculation would be (256*512KB) + 256MB or 384MB of space.

For a 64 bit SQL Server the default StackSize is 2048KB or 2MB, so for the same server only 64 bit, it would be (512*2048KB) + 256MB or 1280MB. Keep in mind that only 256MB of this space is available for use by external objects both of these scenarios since the worker thread memory reservation is used to manage the worker threads in SQL Server.

Once this calculation and allocation has been completed, the next memory space setup by the SQL Server is the BPool or buffer pool. The buffer pool size is determined by a number of factors, beginning with whether the server has AWE enabled on not. If AWE is not enabled on the SQL Server, then the buffer pool is sized based on the remaining memory in the user address space minus the VAS Reservation allocation that has been previously calculated. On 32 bit SQL Servers, the VAS is 4GB in size, and by default is split 50/50 into user address space and kernel address space, with 2GB assigned to each. By using the /3GB or /USERVA startup switches in the Windows boot.ini file, this default configuration can be overridden to provide 3GB of VAS to the user address space, leaving 1GB for the kernel address space.

Once the user address space size has been determined by the SQL Server, the previously calculate VAS Reservation reservation is subtracted from it. Then the resulting value is compared to the amount of available physical memory on the SQL Server, and the lower of the two numbers is the maximum available space for the buffer pool size. This is then compared to the max server memory setting and the lower of the two values is then the maximum size of the buffer pool. For a SQL Server with 1GB of Physical RAM and the default configuration of the Operating System and SQL Server options, this would equate to a maximum buffer pool size of 1GB based on the following calculations:

2048MB (2GB) user VAS - 384MB = 1664MB available VAS

1024MB (1GB) Physical Memory

Since the smaller of the two is the physical memory size, then this is the buffer pool size. However, on a 2GB of Physical Memory server, the maximum size of the buffer pool would be 1664MB since the available user VAS is the smaller amount. This is why adding the /3GB switch is often done in the boot.ini, which would change the calculation to be:

3072MB (3GB) user VAS - 384MB = 2688MB available VAS

4096MB (4GB) Physical Memory

Here the maximum size of the buffer pool is going to be 2688MB. If however AWE is enabled on the SQL Server, then the maximum size of the buffer pool is the upper limit of the Physical RAM on the SQL Server or the max server memory setting, whichever is smaller.

On 64 bit SQL Servers, the VAS Reservation at startup does not occur due to the size of VAS available on 64 bit systems. ( Thank you Linchi Shea and Bob Ward for correcting me on this.)

After reserving and allocating the BPool memory area, SQL Server will then release the VAS Reservation area it reserved in the first step in order to have this region of address space available for objects which utilize VAS Reservation memory.

Understanding allocations from the VAS Reservation

The VAS Reservation itself isn't necessarily the limit of VAS available to SQL Server after the BPool size is calculated. Any remaining user mode VAS can be used by SQL Server for VAS/multi-page allocators inside of SQL Server. For this reason, the available VAS region inside SQL Server can be larger than the VAS Reservation computed and reserved at startup. Consider for example the 32 bit server with 1GB of physical memory in the previous example. The VAS reservation + the Buffer Pool size of 1GB is still short of the 2GB user mode VAS on the server. When this occurs, the balance of user mode VAS is still useable for multi-page allocations by SQL Server, though it can lead to over commitment of memory which can itself be problematic. For 64 bit servers, the available VAS is always multiple terabytes in size, so the limit to memory allocation is the limit of uncommited physical memory on the server.

One of the biggest problems associated with the VAS Reservation on 32 bit servers is fragmentation, which occurs when blocks of memory are allocated from the VAS Reservation and then released later, leaving smaller contiguous blocks of memory available. When SQL Server allocates memory from the VAS Reservation it requires contiguous blocks of memory to make the allocation. This means that you can have 100MB of memory available but if the largest contiguous block is only 4MB and a 8MB allocation is required the request will fail. VAS fragmentation is one of the most difficult scenarios to troubleshoot because there is no means to defragment the VAS space, and it often requires looking at the VAS consumers in your workload to determine why the fragmentation is occurring.

Troubleshooting VAS and Out of Memory Exceptions

How you actually go about troubleshooting Out of Memory (OOM) problems related to VAS in SQL Server depends on a number of factors including what version of SQL Server you are running, what architecture it is running on, and what potential VAS consumers you have in your system. By far the worst problems troubleshooting VAS problems occur on SQL Server 2000, where the ability to look at VAS information is very limited. SQL Server 2005 and 2008 offer much more in the way of troubleshooting problems with the Dynamic Management Views. For SQL Server 2000, the only way to get information on VAS inside SQL Server is to use the vmstat executable. This tool is available as a part of the PSS Labs download from Microsoft in Lab #3:

http://www.microsoft.com/downloads/details.aspx?FamilyId=AEC18337-887F-4EC6-A858-81F84DE8082F&displaylang=en

The output from the tool is a table that shows information similar to:

TYPE       MINIMUM    MAXIMUM    AVERAGE BLK COUNT
~~~~       ~~~~~~~    ~~~~~~~    ~~~~~~~ ~~~~~~~~~
Commit        4096 769654784    2141981        833
Reserve       8192    4128768     387799        121
Free          4096 166080512    1964121        161
Private       4096 769654784    4089322        438
Mapped        4096    1019904     131891         40
Image         4096    7598080      73099        476

The free Type and Maximum column show the largest available contiguous block of VAS available to SQL Server. This information is in bytes. In SQL Server 2005 and 2008, this same information is available in the DMV's. Christian Bolton offers a great VAS Summary query on his blog post SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog:

WITH VASummary(Size,Reserved,Free) AS (SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VaDump GROUP BY Size) SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0

Further when troubleshooting what is consuming memory in SQL Server 2000, the best you can get is the output from DBCC MEMORYSTATUS, which is very limited in SQL Server 2000 when compared to the same output from SQL Server 2005 and 2008. However, in SQL Server 2005 and 2008, the sys.dm_os_memory_clerks DMV can be used to look at where VAS allocations are being used in much more detail.

SELECT type, virtual_memory_committed_kb, multi_pages_kb FROM sys.dm_os_memory_clerks WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

From this, you can begin to look at the clerks that are the highest consumers and begin troubleshooting form there. Understanding the consumers of VAS in your environment is one of the keys to troubleshooting VAS allocation issues. As previously covered, common consumers of VAS in SQL are Extended Procedures, COM objects (OLE Automation calls), Linked Servers, OLEDB providers, SQL CLR, and is also used for allocations larger than 8KB (>8KB), also known as multi-page allocations, by the database engine. Other consumers can include third party backup tools (LiteSpeed for example), and connections that have a network packet size > 8000 bytes (this puts them into multi-page allocation).

When any one of combination of many of these exists in your environment you need to look beyond the VAS size being the issue and determine if a problem in design, code or utilization is actually the problem. For example, backups that use a large maxtransfersize or buffer size, or a network packet size > 8000 bytes can become heavy VAS consumers. You may need to balance any performance increase realized by these tools in their default configuration with the need to resize your VAS allocation. If you utilize OLE Automation to call COM objects, you need to make sure that for every call to sp_OACreate, you have a corresponding call to sp_OADestroy, or you will leak VAS from the open pointers.

If you are on a 32 bit SQL Server, one thing that can be tried is to bump the size of the Base VAS Reservation so that more memory is reserved up front. To increase the available size of space in the MemToLeave area, the -g startup parameter can be used to specify a value larger than 256MB. Values less than or equal to 256MB specified with the -g startup parameter are ignored by SQL Server and the default 256MB allocation is used. This can not be used on 64 bit servers since the VAS Reservation calculations do not occur on 64 bit servers.

The problem with using this startup parameter on a 32 bit server is that it reduces the size of the buffer pool, especially on servers that don't utilize AWE, so there is a trade off that happens. For this reason, any use of this startup parameter should be carefully considered, and then slowly increased if necessary. If the VAS problem is with fragmentation or worse a true memory leak from a custom Extended Stored procedure, increasing the base allocation may not resolve the problem, it will only make the time to encounter the error longer, so it isn't a catch all fix to the problem. If your VAS problems are related to overuse of VAS then adding this parameter may solve the problem, you have to test to find out, but one note is that changing this value requires a server restart to take effect.

If you are on a 64 bit SQL Server, the only real solution to the problem that I have found to date is to back down the size of the max server memory setting, which leaves additional physical memory available to be used for multi-page allocations and VAS requirements. This essentially is doing the same thing that bumping the VAS reservation with the -g startup parameter does, by removing memory from the BPool allocation and leaving it available for VAS allocations.

I hope this helps those who have had questions on how memory is determined during the startup of SQL Server.

References:

Guru Guide to SQL Server Architecture - Chapter 11

Memory-mapped files and SQL Server - Ken Henderson's WebLog

Be careful with xproc memory allocation - Ken Henderson's WebLog

Inside SQL Server 2000's Memory Management Facilities

SQL Server Memory - Microsoft SQL Server Team

SQLOS's memory manager and SQL Server's Buffer Pool

SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog