Post reply

High memory usage - SQL server

  • September 3, 2015 at 7:35 am

    #1824289

    Thanks Raj. The total server memory is 100GB. RAMMAP shows 'sqlservr.exe' as the top process (24GB).

  • September 3, 2015 at 7:40 am

    #1824291

    Yes Grant, Its using 24 GB more than the max memory set for it.

  • September 3, 2015 at 7:43 am

    #1824292

    How so? You've set it to use max 100GB, and it's using 24GB. You need to find out what's using the other 100GB.

    John

    Edit - although you've changed your story since your original post about what the total server memory is, so I have to admit I'm a bit confused.

  • September 3, 2015 at 7:56 am

    #1824296

    Hi John, Sorry if my description was not good enough.

    Here is more in detail:

    Windows server 2012 R2

    SQL server 2014 Enterprise(2 node cluster)

    Total memory for windows server: 128 GB

    Max SQL server memory set: 100 GB

    (Total and Target SQL server memory: 100 GB)

    On RAMMAP/TASK MANAGER, memory usage for 'sqlserver.exe' : 24 GB

    Available Windows server memory: less than 4 GB

  • September 3, 2015 at 8:10 am

    #1824303

    Thanks for the clarification. So, as I first thought, you have 128GB, of which only 4GB is free. SQL Server is using 24GB. What is using the other 100GB? We can't see your screen.

    John

  • September 3, 2015 at 9:08 am

    #1824337

    There is 4 GB left out of 128 GB total.

    here's the breakdown:

    target sql memory: 100 GB

    +

    'sqlserver.exe' memory usage on RAMMAP: 24 GB

    =

    124 GB used

    --24GB extra used on top of MAX memory(100GB) allocated for SQL Instance.

  • September 3, 2015 at 9:37 am

    #1824362

    Max memory means maximum memory that can be used, not memory that is actually being used. So just because max memory is set to 100GB, doesn't mean you're using 100GB. You've also used the terms "target" and "total" memory - what do they mean and where do you get the numbers from?

    This is my last attempt to get a comprehensible answer. SQL Server is using 24GB. You have 4GB free. That's 28GB accounted for. What's using the other 100GB? Feel free to help us by including results from sp_configure, screen shots, or anything else that may assist our understanding of where your numbers come from.

    John

  • September 3, 2015 at 10:21 am

    #1824378

    Max memory means maximum memory that can be used, not memory that is actually being used. So just because max memory is set to 100GB, doesn't mean you're using 100GB.

    My understanding is that SQL server.exe can use more than this MAX memory which is used for Buffer pool and Procedure cache. Like SQLCLR, Extended SP, linked servers, binaries...

    You've also used the terms "target" and "total" memory - what do they mean and where do you get the numbers from?

    ::For this instance, Min and Max memory is set to same values (100GB) and it has consumed/locked to 100GB in total

    This is my last attempt to get a comprehensible answer. SQL Server is using 24GB. You have 4GB free. That's 28GB accounted for. What's using the other 100GB? Feel free to help us by including results from sp_configure, screen shots, or anything else that may assist our understanding of where your numbers come from.

    :: In windows server 2012 R2 Task manager->Memory ->Details, Memory(private working set) for sqlservr.exe is 24 GB. Its not showing the whole memory actually used by sqlserver (100 GB+24 GB). Sorry I'm not able to attach a screen shot here.

    So finally the memory left on the windows server is 4 GB (128-100-24)

  • September 3, 2015 at 10:32 am

    #1824385

    If what you're looking at says that SQL Server is consuming 24gb, that's what it's consuming, not 24 + 100. You're missing the point.

    Also, while there is some OS memory used by some processes within SQL Server, what you listed, the buffer pool and the procedure cache, are absolutely not in there. They are only within the allocated memory of SQL Server, which you have capped at 100gb.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 3, 2015 at 10:51 am

    #1824394

    Don't. Use. The. Task. Manager. To. Check. SQL. Server's. Memory!

    The task manager does not show accurate values in several cases. Do not use it. Use perfmon and look at the total server memory counter, or look at SQL's DMVs.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2015 at 11:58 am

    #1824441

    Thanks Gail!

    Total server memory used when checking perfmon counter is: 100 GB

    But there is additional usage by sqlserver.exe of 24 gb more as 'private working set' showing in RAMMAP tool. Since this is additional to the SQL allocated memory, how can I figure out/troubleshoot the extra usage by sql process?

    Is this a memory leak?

    Thanks!

  • September 3, 2015 at 12:50 pm

    #1824463

    Honestly, part of the problem here is that you're looking at disparate things and assuming they mean what you think they mean, when they probably don't.

    I don't have time to explain Windows memory management here. Google for a couple of long videos "Mysteries of Memory Management" by Mark Russinovich

    I'll guess one thing though. You have Lock Pages in Memory enabled? That'll be why Task Manager and RAMMAP are showing 24GB for SQL's memory usage, not around 100GB (and note I said 'instead', not 'as well as')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2015 at 1:39 pm

    #1824479

    Thanks Gail. Lock pages in memory is enabled.

  • September 3, 2015 at 2:23 pm

    #1824486

    Yup, and hence Test Manager and RAMMap are both lying to you. Neither considers physically allocated memory (what locked pages allows SQL to take), and hence they're both showing 24GB instead of around 100GB.

    SQL's not taking the 100 GB from max server memory + another 24GB. The tools you're using are only showing you 24GB of SQL's total allocation, which will be around 100GB (max server memory, plus the non-buffer memory)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2015 at 2:53 pm

    #1824493

    Thanks Gail!

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply