When you examine the following information points, you will find that there is very low available virtual address space:
- DBCC MEMORYSTATUS - Process/System Counts section - Available Virtual Memory
- DMV: sys.dm_os_process_memory - column virtual_address_space_available_kb
When you're at the stage where the available virtual address space is very low, queries that try to perform memory allocation may also encounter a wait type of CMEMTHREAD.
The following data points will continue to increase over time:
- DMV: sys.dm_os_process_memory and sys.dm_os_memory_nodes - column virtual_address_space_reserved_kb
- DBCC MEMORYSTATUS - Memory Manager section - VM Reserved
These values will typically increase in multiples of the "max server memory" value up to almost 8 TB.
Hotfix informationTo resolve this issue, install the hotfix from FIX: Error when your stored procedure calls another stored procedure on linked server in SQL Server 2014 .
PrerequisitesYou must have Cumulative Update 3 for SQL Server 2014 Service Pack 1 installed to apply this hotfix.
NoticeAfter you apply this update, you have to add the trace flag -T8075 as a startup parameter to enable this change.
Cumulative update informationThe issue was first fixed in the following cumulative update of SQL Server.
- Cumulative Update 4 for SQL Server 2014 SP1
- Cumulative Update 10 for SQL Server 2014
- Cumulative Update 8 for SQL Server 2012 SP2
This hotfix prevents both the out of memory and the continuous reduction of available virtual address space you may experience.
- Windows 2012 R2 allows virtual address space to grow as large as 128 TB. Therefore, you may not notice this issue in Windows 2012 R2 environments. For more information, see the following topic in the Windows Dev Center:
Memory limits for Windows and Windows Server releases
- If you see continuous growth in virtual address space even after you apply the fix, you can determine which queries or operations are requesting large chunks of memory by using the Page_allocated extended event. A sample script looks like this: Typically these are log backups and index maintenance operations, which occur frequently.
CREATE EVENT SESSION [memory_tracking] ON SERVER
ADD EVENT sqlos.page_allocated(
ADD TARGET package0.event_file(SET filename=N'E:\Data\MSSQL11.MSSQLSERVER\MSSQL\Log\memory_tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
Makale No: 3074434 - Son İnceleme: 10 Eki 2016 - Düzeltme: 1