Sql-server – SQL Server 2012 DBCC memorystatus has wrong information. How to correct it

dbccmemorysql-server-2012windows

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.

Best Answer

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.

Related Question