1

Five 2012 instances on a 2-node cluster using AVGs. Two of them doing nothing yet, no user databases or users. Customer insists on specific max mem settings while waiting for additional memory approval. We lowered max mem from 1gb to 256mb on both and each instances CPU usage jumped to 20% and stayed there. Raising it to 512mb brought it back down to basically nothing. Regardless of mem setting each instances memory usage stayed right around 200mb.

Any ideas? I'm still doing research for the technical answer they are requesting.

Improve this question
5
  • 1
    Windows Performance Toolkit may allow you to understand why that is happening if you have the luxury of setting up a test system (ie do not use WPT on a production system!)
    – Hannah Vernon ♦
    Dec 11, 2015 at 21:14
  • 1
    If the instance is not being used, just shut it down. Why waste 256MB when you cannot afford even 1GB.
    – Kin Shah
    Dec 11, 2015 at 21:15
  • We are aware of our options and probably will end shutting it down. But for now, folks are perplexed (and blaming inefficient mirroring overhead) and looking for an answer.
    – Rick
    Dec 11, 2015 at 21:25
  • 1
    Why would you waste your time? Anything less than 1GB is not supported by the vendor (Microsoft). While the fact that they let you set it that low is problematic it self, it doesn't mean you should - msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx
    – Andrew Loree
    Dec 14, 2015 at 5:29
  • Can you monitor paging and context switching counters at the different memory settings? SQL Server needs a minimum amount of memory just to run (as indicated by Andrew) the base engine and 256MB is right on the edge of the threshold. An idle bare bones server instance uses just about 200MB to run system threads. Additional services/features (like AVG) will have incremental consumption. Also, check the error logs to see if there are memory related entries or other stuff causing problems.
    – SQLmojoe
    Dec 14, 2015 at 17:07

1 Answer 1

Reset to default
0

If the Page Life Expectancy performance counter is at a decent value, this can be due to constant (re) compilation : because of the low amount of memory available, plans get evicted quite quickly from the cache, and need to be compiled every time thier corresponding query is submitted to SQL Server.

Improve this answer

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.