I have a weird problem I am struggling to troubleshoot.
I have a development server with 18GB RAM and two SQL Server 2012 SP3 instances with @@version output:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
As far as I can see from checking gpedit, Lock Pages in Memory is not enabled on the server.
Each instance has the following memory settings:
Min: 1024 Max: 7168
I'm getting performance issues with Instance 2. Slow queries etc. Instance 1 is fine.
To do a basic check for memory usage I'm running:
SELECT counter_name AS TheCounter ,cntr_value AS Value FROM sys.dm_os_performance_counters WHERE counter_name IN ( 'Total Server Memory (KB)' ,'Target Server Memory (KB)' )
If I run that query against Instance 1 I see this:
Target Server Memory (KB) - 7340032 Total Server Memory (KB) - 7340024
I'm happy with that.
But if I run the same against Instance 2, I see this:
Target Server Memory (KB) - 4349216 Total Server Memory (KB) - 878896
To me that means the instance wants 4GB and can only consume 878MB.
There are no other memory intensive processes running on this server. The output of dm_os_process_memory is as follows:
physical_memory_in_use_kb="589728" large_page_allocations_kb="0" locked_page_allocations_kb="0" total_virtual_address_space_kb="137438953344" virtual_address_space_reserved_kb="20263356" virtual_address_space_committed_kb="693112" virtual_address_space_available_kb="137418689988" page_fault_count="249811" memory_utilization_percentage="100" available_commit_limit_kb="6501636" process_physical_memory_low="0" process_virtual_memory_low="0"
Can anyone tell me why Instance 2 is not using the memory it has been allocated, and how best to troubleshoot?