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:

total_virtual_address_space_kb="137438953344" virtual_address_space_reserved_kb="20263356" virtual_address_space_committed_kb="693112" virtual_address_space_available_kb="137418689988" 

Can anyone tell me why Instance 2 is not using the memory it has been allocated, and how best to troubleshoot?

  • Please add the output of select @@version from both the machines, does both machines have LPIM enabled ? – Shanky May 4 '17 at 11:46
  • LPIM is not enabled as far as I can see - or should I say, checking through gpedit there are no users groups with priveledges to lock pages in memory. – Molenpad May 4 '17 at 12:17
  • Has Instance 2 been used to the same degree as Instance 1? Have you looked at performance counters and wait stats to determine where you're getting held up? – alroc May 4 '17 at 12:38
  • 1
    Can you provide LPIM to SQL Server service account on server which is giving the issue and let me know you again see the irregularity, it requires restart. Also please add output of select * from sys.dm_os_process_memory from instance which is giving the issue – Shanky May 4 '17 at 13:52
  • I'm waiting for confirmation I can reboot the server to apply the LPIM, but I've added output of sm_os_process_memory above. – Molenpad May 5 '17 at 8:17

Your Answer

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

Browse other questions tagged or ask your own question.