3

We have a new application that is making use of both traditional MS SQL and MongoDB for data storage. We're running both instances on the same Windows 2008R2 x64 machine, with 64GB RAM.

There have been some anecdotal reports of poor performance that I've begun to investigate.

I can see that SQL Server is not consuming as much memory as I would expect, and is showing signs of memory pressure.

Eg. 

AppDomain 16 (mssqlsystemresource.dbo[runtime].15) is marked for unload due to memory pressure

In contrast, MongoDB is using a huge working set, which is expected behavior based on the documentation

It relies on Windows to manage memory use.

It seems that SQL server isn't able to, or isn't requesting use any of the memory in the Mongo working set and that is impacting performance. The server is under very low load at the moment.

  • Page Life Expectancy seems high at: 1536
  • and Buffer cache hit ratio: 404002
  • Free list stalls/sec: 695
  • Free pages: 26529

Has anyone come across this issue? What it the expected behavior of SQL server in this scenario? I'm considering using the min server memory configuration option.

Improve this question
4
  • What is the Page Life Expectancy on the server? Also how much memory is on the server and how much is currently being used?
    – Mark Wilkinson
    Mar 20, 2014 at 10:34
  • Edit: included additional facts and figures.
    – reticentKoala
    Mar 20, 2014 at 11:27
  • 1
    What is your max memory setting for SQL Server?
    – Mike Fal
    Mar 20, 2014 at 13:42
  • Max memory is default.
    – reticentKoala
    Mar 20, 2014 at 17:27

2 Answers 2

Reset to default
3

Set the min server memory option for SQL Server. It will still release pages under memory pressure, unless you also allow SQL Server to lock pages in memory. The combination of setting min server memory and locking pages in memory should resolve the problem.

I've never used MongoDB, so I don't know if you can manage memory usage. A quick search makes it seem you can't.

Assuming you're running a 64-bit server, and have more than 4 GB of RAM, you could run a 32-bit MongoDB instance, which will limit the memory to 2, 3, or 4GB depending on it's architecture. Does a 32-bit MongoDB even exist?

Improve this answer
2
  • 2
    +1 for the min memory, as otherwise SQL Server will have to take MongoDB leftovers. You might consider setting up a dedicated MongoDB server.
    – KookieMonster
    Mar 20, 2014 at 16:20
  • If your total dataset is large don't use 32-bit MongoDB: it is limited to 2Gb on disk as well as in-memory in such a setup, see blog.mongodb.org/post/137788967/32-bit-limitations. As KookieMonster suggests you could physically separate the databases, or if wouldn't kill performance too much for your application's load patterns you could run one of them in a HyperV VM to segregate their memory use (there may be a financial cost to consider too, depending on how you purchase/manage your Windows licenses, unless you run Mongo on Linux in the VM but that potentially has a skills cost...).
    – David Spillett
    Jun 2, 2014 at 9:45
-1

Right click on your server in SSMS, select Properties.

Go to the Memory tab and set the mininum server memory to some reasonable value.

We were having perf issues due to mongo starving SQL Server, causing it to swap to the disk a lot. We then set the minimum to be 8GB (out of 32GB) and within a minute it started to get better.

Improve this answer

Your Answer

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

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