Well, this is often a tricky situation, where you are running into Virtual Address Fragmentation and getting OOM (out-of-memory) errors in your SQL Server. More often than not there is confusion between physical memory pressure vs. virtual memory pressure. Adding more RAM is definitely not a solution here!
Here are some sample error messages you might have seen in your SQL Errorlog which will indicate if this is physical memory or virtual memory issue:
SQL 2000WARNING: Failed to reserve contiguous memory of Size= 65536.SQL Server could not spawn process_loginread thread. SQL 2005/2008Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880Error: 701, Severity: 17, State: 123.There is insufficient system memory to run this query.As the errors above indicate, the problem is in reserving a fixed size in the virtual address space of SQL Server. Note: the size indicated above in Bytes. E.g. 65536/1024 = 64 KBTypical symptoms you would notice in these situations are:-1) Database or Log Backups might start failing2) You are unable to make a new connections to SQL.3) Certain jobs which require memory from non-BPool region will fail.Many a time, these problems go away automatically without any action taken. This indicates an intermittent problem when at a certain point in time; there was virtual memory pressure which resulted in above messages being printed to the SQL Errorlog.Dealing with VAS fragmentation or running out of VAS on SQL server 2000 was rather painful and required setting up additional debugging techniques to get down to the bottom of the issue. You might have used some of the following tools:-
1) VMSTAT2) Debugging Tools for Windows (to capture a manual dump of sqlservr.exe)3) T2551 to generate a filtered dump in SQL Server when running into a OOM condition.4) TLIST.exe to identify modules loaded in SQL Server.
Luckily, starting with SQL 2005 there is an in-memory DMV which tracks the virtual address space (VAS) of your SQL Server process. Here are some queries which will help you find out how much virtual address is available on sqlservr.exe which is FREE and how much is total available (Free+InUse)1. Will tell you the size of the biggest contiguous block in VASSELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB]
from sys.dm_os_virtual_address_dump where region_state = 0x00010000 --- MEM_FREE2. Will also tell us size of largest contiguous block plus the region marked as MEM_RESERVE (this is your non-BPool area reserved during SQL Startup, sometimes referred to as MTL - MemToLeave)With VASummary(Size,Reserved,Free) AS
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)
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
WHERE region_allocation_base_address = 0x0
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]
WHERE Free <> 0
3. The below query will identify the memory reserved by non-BPool components in SQL Serverselect SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb from
where type not like '%bufferpool%'
4. To identify if any of the space used is by SQL Server procedure cache itself, you can use this query
SELECT SUM(PAGESUSED)*8/1024 'MB of MemToLeave memory consumed by procedures'
FROM MASTER.DBO.SYSCACHEOBJECTS WHERE PAGESUSED >1
DBCC MEMORYSTATUS also has good information on whether the usage from the non-BPool region is coming from SQL or non-SQL components. If it is SQL 2000, check the value of “OS Committed” and it is SQL 2005/2008 look at the value of “MultiPage Allocator” for each memory clerk. Just to re-state my assumption in case you are not sure1 SQL Server Page = 8 KB –> SinglePage Allocator> 1 Page or > 8KB –> MultiPage Allocator
Apart from these you need to pay special attention to the following components are all of the below do not use memory from the Buffer Pool region but make direct VirtualAlloc() calls to reserve memory and then commit them,1) Any OLE/COM components loaded in SQL Server2) Extended Stored Procedures (use sys.dm_os_loaded_modules to identify the module loaded in sqlserver process space).3) SQL Mail components4) Any XML prepared documents using sp_xml_preparedocument5) Linked Server Providers6) Large Plans stored in Procedure Cache7) Very frequent Backups also may cause MTL area depletion. (Please investigate using the parameters MAXTRANSFERSIZE and BUFFERCOUNT, if this is the case).8) SQL CLR (recommended to be used on 64-bit SQL Servers)
Above list is certainly not exhaustive, but is more enough to get started in looking at the right areas. While we are on this topic, it is important to understand the difference between a Reserve and a Commit. These are windows concepts are remain the same for SQL Server as well, after all its VirtualAlloc() underneath the covers.MEM_COMMIT – Region in VAS this is backed by RAM/paging fileMEM_RESERVE – Region in VAS with no actual physical storage either in RAM or in the paging file.The problems we talked about so far occur when a call to VirtualAlloc() with MEM_RESERVE is made and that “reservation” fails resulting in the errors printed to errorlog. Most times, the call to reserve is subsequently followed by a COMMIT, but its not mandatory. I can reserve now and commit later on using the base address of the reservation. SQL Server is smart enough and during starting reserves a certain region of the address space referred to as MTL or Non-BPool region. It is here the crunch is and it is here the issue needs to be investigated/fixed.
A quick workaround for above issues is to add the startup parameter –gXXX. (Sample usage- -g512)XXX- is the amount in MBytes to reserve on startup. I would advise against doing this as this is a workaround where you are increasing the MTL region rather than find out who/what is consuming it. Slava’s blog is a good read also on this topic.Sudarshan NarasimhanTechnical Lead, Microsoft SQL Server CSS
A a very nice post.
Memory is tricky to understand at times, but way you have explained the symptoms and solution is commendable.
Keep up gud work Sudarshan!
Your below query will produce TotalPages under Procedure Cache section of the DBCC MEMORYSTATUS which is bit different that Mem to leave
Usefull information , keep posting all such good informations.Its really appreciatableae
By executing the below query in one of systems which has 98214 MB , returned 98715 MB (TO SQL server we set MAX memory as 80 Gb).Lock pages is enable in this sql server 2008.
So do you mean to say all the system memory is allocated to NON PUFFER POOL?
select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb from
Great post ... Thanks for sharing ..
wht do you by ....MEM_RESERVE – Region in VAS with no actual physical storage either in RAM or in the paging file.
is this just a logical reservation ?
The queries are fine but you did not expained what inference to draw from output of queries,how one can reach to fact that this part of OS or SQL server is causing Issue.
What do I do with the results of the 4 queries? How do I know if the results are good, bad or as expected? I think this post is interesting but incomplete because I don't know what conclusions to draw from the data that is gathered.
trying to find out if my pc is being used as a virtual server I belive it is but this stuff is over my head will take more learning however i did find it helpful for I was able to look up my sql log wich i didn'tknow I had.