0

Only user database on instance is ~8.5GBs. MAX Mem set to 16GBs, which is ALL being consumed. Virtual server has 24GBs RAM and 2 vcpus Insert Poltergeist meme "What's happening?!"

***Side notes**** 2 vcpus have low avg utilization(<50%). Server hosts app (using ~500MBs of RAM). Indexes are rebuilt based on size(page count >= 1000) and avg_fragmentation(>=50%). Thought Tempdb may be issue but not seeing wait stats to indicate. Top 10 expensive queries aren't using much CPU, RAM(logical reads), or being executed over and over. Wait stats show ASYNC_IO almost with 1-to-1 wait count per second, but these are coupled with high wiats for backup related waits...

Improve this question
8
  • BTW, there's a FULLTEXT catalog and many indexes on this database...
    – thundercougarfalcon
    Sep 28, 2017 at 16:02
  • Query plans will also contribute to memory usage and sql server will not willing give back memory when it takes it. I would venture to say that your reindexing on top of the query plan cache is what caused sql server to consume the full 16gb. It more than likely isn’t using the full 16gb, it only has it allocated to the process as it used it at one time since the last restart of the service. I would look at the process in process monitor to determine if it’s just allocated vs actually in use
    – Aaron
    Sep 28, 2017 at 16:33
  • Just in case you haven't already seen this:A Sysadmin’s Guide to Microsoft SQL Server Memory
    – Erik Darling
    Sep 28, 2017 at 16:44
  • @Aron Reindex only runs on 1 to indexes a night, and they're barely >1000 pages>thanks baselining! So, I have other instances like this in the environment (same server and database config)and I've not seen this type of behavior... Anyone else?
    – thundercougarfalcon
    Sep 28, 2017 at 17:03
  • @sp_BlitzErik I guess this question would have good been for office hours, but alas performance issues are reported first at a trickle and then in a flood.. So, after re-reading your link, the theory goes that if I have a 1GB database and set MAXMEM on the host instance 100GBs that eventually It will consume all 100GBs?
    – thundercougarfalcon
    Sep 28, 2017 at 17:06

1 Answer 1

Reset to default
0

SQL Server will never ever ever give back any memory that it uses unless pressure is exerted on the service by the operating system. It's designed that way intentionally to do as much processing in memory as possible to prevent disk I/O.

In the comments above there's a linked article to A Sysadmin’s Guide to Microsoft SQL Server Memory which contains some good information explaining the specific use cases and behavior that you can expect. Generally though it's best to remember that the structure of data held in the SQL buffer cache is based on performance, not resource conservation. Make sure your maximum memory is set appropriately for your environment and needs.

If you are running SQL Server in a virtual environment where you are trying to reduce the amount of memory on an ongoing basis then you will need to use a balloon driver or other utility to exert memory pressure on the instance.

For performance purposes if you are seeing a sudden flood of complaints it's possible that the SQL instance is running out of memory and is paging to disk, which will result in a very significant performance hit. There are multiple recommendations, but my personal preference is to not use a page file at all which will result in certain crash dumps not being returned in the event of a windows fault.

Improve this answer
2
  • That's kind of a boiler-plate response: Give as much memory to SQL Server as it needs. I like how you noticed the VM aspect (the only one to do so), as I think this is a vm config issue. BTWM, %pagefile is <1%...
    – thundercougarfalcon
    Sep 28, 2017 at 18:10
  • @thundercougarfalcon I meant more to say that SQL will take as much memory as it can, rather than as much as it needs. The balloon driver I mentioned is a means of forcing SQL to give it back after it's done with it. As far as the VM controller is concerned it thinks the VM is actually taking up all of that memory when it's actually just sitting idle with buffered cache data.
    – David
    Oct 10, 2017 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.