2

I have a Windows 7 64-bit machine with 16GB ram, 24GB pagefile, 500GB HD and SQL Server 2012 Standard Edition 64-bit running on it.

With 5 minutes of starting up, it routinely uses all my virtual memory and triggers warnings on my machine i.e. ProcessExplorer says sqlservr.exe is using Private Bytes of 38GB, Virtual Size of 61GB.

I believe the issue results from mis-information that I can see when I run DBCC MemoryStatus command, which indicates that the Available Virtual Memory is 8,763,135,270,912. This number is at least 8TB. Other stats returned by the command appear to be accurate.

Does anyone know how the DBCC MemoryStatus command gets this number and how I might correct it? I think this would affect how much virtual memory it would try to allocate.

  • Starting with SQL Server 2012 you cannot find its memory usage in task manager. The buffer pool seems to not be represented at all. If your sqlservr.exe has that much private bytes something other than the buffer pool is consuming memory. Query the DMV *memory_clerks. Whatever it is called. Virtual size is irrelevant btw. – usr Nov 3 '14 at 18:02
  • Thanks for your reply. I'm actually using ProcessExplorer rather than Task Manager and it gives a very clear indication of virtual memory usage. I've checked sys.dm_os_memory_clerks and the figures it provides do not sufficiently explain the virtual memory usage. I wouldn't care about this except that it crashes other programs running under Windows 7. – user1281639 Nov 3 '14 at 18:09
  • Again, virtual size is irrelevant. Virtual size is not memory "in use". I can reserve 1PB of virtual memory in a single API call to Windows on any box. Order by the pages_used_kb column. That's actually "in use" memory.; Procexp has not a single counter that you can use to see memory used by SQL Server's buffer pool. I tested this. It just does not appear anywhere. – usr Nov 3 '14 at 18:17
  • What is your goal actually? I understand you want to find out why SQL Server consumes that much memory and how to stop that? Or do you really care about the counter values of MemoryStatus (as you have asked)? – usr Nov 3 '14 at 18:22
  • Well, my goal is to prevent SQL Server from consuming all the virtual memory available and causing my system to crash. I'm not sure I care about what's used by the SQL Server buffer pool. The Buffer Pool may not be the problem at all. I only care that Windows complains that virtual memory is low and that SQL Server is by far the single greatest user of virtual memory. I thought that the counter values of MemoryStatus had an impact on this. Perhaps I was wrong on that - so yes I think my question is changing. – user1281639 Nov 3 '14 at 18:35
2

x86-64 Virtual Address Space Details:

Although virtual addresses are 64 bits wide in 64-bit mode, current implementations (and all chips known to be in the planning stages) do not allow the entire virtual address space of 264 bytes (16 EB) to be used. This would be approximately four billion times the size of virtual address space on 32-bit machines. Most operating systems and applications will not need such a large address space for the foreseeable future, so implementing such wide virtual addresses would simply increase the complexity and cost of address translation with no real benefit. AMD therefore decided that, in the first implementations of the architecture, only the least significant 48 bits of a virtual address would actually be used in address translation (page table lookup).1

In addition, the AMD specification requires that bits 48 through 63 of any virtual address must be copies of bit 47 (in a manner akin to sign extension), or the processor will raise an exception.1 Addresses complying with this rule are referred to as "canonical form."1 Canonical form addresses run from 0 through 00007FFF'FFFFFFFF, and from FFFF8000'00000000 through FFFFFFFF'FFFFFFFF, for a total of 256 TB of usable virtual address space. This is still approximately 64,000 times the virtual address space on 32-bit machines.

[...]

The first versions of Windows for x64 did not even use the full 256 TB; they were restricted to just 8 TB of user space and 8 TB of kernel space. Windows did not support the entire 48-bit address space until Windows 8.1

There you have it. As the OS supports more than the default 8TB VA, it has become necessary to report in the DBCC MEMORY_STATUS how much is the OS actually supporting. Your OS is still the classical 8TB flavor.

1

There is nothing wrong in Available Virtual Memory is 8,763,135,270,912. This number is at least 8TB actually starting from SQL Server 2012 this new feature has been introduced in SQL Server 2012 DBCC MEMORYSTATUS output. Below is output of memorystatus on my system

Process/System Counts                    Value
---------------------------------------- --------------------
Available Physical Memory                1886949376
Available Virtual Memory                 8781736599552
Available Paging File                    10989400064
Working Set                              135262208
Percent of Committed Memory in WS        61
Page Faults                              15069059
System physical memory high              1
System physical memory low               0
Process physical memory low              0

Before giving information about actual memory distribution it gives information about current status of system. SQL Server 64 bit has theoretically has VAS of around 8 TB this is basically virtual address space. All process which would run on SQL Server would see this much VAS so if system is pointing out available Virtual Memory as almost 8 TB I dont think there is anything to worry about it. This is system pointing to amount of VAS visible to any process running on 64 bit SQL Server instance.

Please always use below query to see SQL Server memory utilization because task manager only shows process private bytes and if SQL Server service account has locked pages in memory privilege it would not show correct value. As a fact task manager would not show memory allocated through AWE API

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Edit:

Regarding low virtual memory condition its coming because page file is not set correctly. You must use This Link and This Link to configure appropriate value. This link will help to guide how to change

There can be many reasons for low virtual memory condition. The current workload which is running on OS is memory hungry and OS due to limited RAM has to use virtual memory for temp storage and ultimately its getting so much utilized that its giving warning. So you must check what all processes are running. Page file is adjusted as per memory committed if more memory is committed page file value changes use below counters to determine appropriate value of page file

Memory: Committed Bytes Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage Percentage of the paging file committed

Paging File: % Usage Peak Highest percentage of the paging file committed

  • Thanks Shanky for your response. When I try your query I see memory_usedby_sqlserver_mb = 169 (which seems fine). Total_VAS_in_MB is 8388607 and all the other values are zero. I'm not sure if that information explains why Windows complains that I'm low on Virtual Memory, though. – user1281639 Nov 3 '14 at 18:39
  • @user1281639: I have edited my answer please see it. Tentatively max size for page file should be 32 G and min should be 16 – Shanky Nov 3 '14 at 18:58
  • Ok It was not clear from your question that you are actually talking about low memory notification from Windows. What is max server memory ? have you set it. Have you left enough for OS to function properly ? Are you running any third part tool ?. You can use this link posted in answer for setting appropriate value for page file . Can you also add message in question which you are getting – Shanky Nov 3 '14 at 19:09
  • Hi Shanky. Max Server Memory in SQL Server was set to 1GB. The only other relevant software running on my machine at this time is the SQL Reporting Service. I'll try adjusting the pagefile, but I would rather not see SQL Server using more memory. I would love to see a way to reduce how much virtual memory it was trying to use, rather than givingn it more. – user1281639 Nov 3 '14 at 19:10
  • Why have you set max server memory as 1G ? Its blunder. You are taking message in incorrect way what system is saying is that amount of memory it is committing is not backed by correct page file usage its you system(OS) committing more memory and page file varies as per memory committed and since committed is increasing page file is not as per what it needs to be so its saying low virtual memory. You need to find out what process on system (OS) is committing more memory. – Shanky Nov 3 '14 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.