Post reply

What does SQL Server use memory for?

  • October 17, 2019 at 8:12 pm

    #3689908

     

    I know that SQL Server uses memory for things like caching data in the buffer pool and plan cache, but what else makes up the memory usage?

     

    For example, I was looking at a small server today. The server itself had 16GB RAM, with a maximum allocation to SQL Server of 13GB. Task Manager (I know, don't trust it) showed that SQL Server was using 13GB, or thereabouts.

    Perfmon showed that there was about 420,000 database pages in the buffer pool (~3.2GB). The plan cache was about 4.2GB. So that's about 7.5GB. I also checked PLE, which was just over 50,000 seconds. While I'm aware that this isn't an ideal metric to use these days, I would think that a value such as 50,000 seconds would suggest we're not under too much memory pressure.

    What else would SQL Server be using the memory for that would account for the remaining ~5GB that is currently allocated to, and being used by SQL Server? Will there be allocated free space in the buffer pool waiting for data pages? (I recall there being a "free pages" counter in earlier versions of SQL Server)

    I was expecting that with 13GB being the maximum allocation, I'd get more than 3.2GB being used for data pages.

    And as a bonus question, does SQL Server remove data from the plan cache before removing data pages when it comes under memory pressure?

    The reason I ask is that I reduced the max allocation to 12GB. The number of data pages essentially remained the same, but the plan cache was reduced.

     

    Thanks for the help

    Steve

  • October 17, 2019 at 9:54 pm

    #3689928

    SQL Server needed the max amount of memory at some point - and will not return that memory to the OS unless the OS requests it (and you don't have locked pages in memory set).

    The fact that at this moment in time SQL Server isn't using all of the memory does not mean it did not need it at some point.  For example - running an integrity check will cause all data pages to be loaded into memory and checked.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 18, 2019 at 2:02 am

    #3689985

    Hi Jeffrey

    Thank you for the reply.That

    makes sense, and is what I hoped the case would be.Does

    SQL Server record anywhere that it has this memory, but isn't using it for anything?

    Thanks again

    Steve

Viewing 3 posts - 1 through 2 (of 2 total)

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