3

Does anyone know of a setting or trigger for MS SQL 2014 standard edition that would trigger SQL to start using CPU% when system memory hits over 90% usage. I have a small application that is not accessing SQL but does utilize large amounts of memory. When the memory on the system hits 90%+ utilization SQL Server started showing CPU usage on a continual basis until the toolset is stopped.

I've run multiple analysis such as profiler, queries to see the last statements processed, the connections to the database, etc... Nothing is standing out as being the trigger for SQL Server to spike. What is even more odd is that this particular machine has both SQL Server 2014 standard and SQL Server express on it and both of the versions will trigger the spike (this is not my server but one that I am having to work on).

  • How much memory does the server have and is max mem set for each sql instance? I wonder if memory pressure is causing sql to free up ram and that in turn is causing cpu usage and io. – Sir Swears-a-lot Mar 4 '17 at 3:05
  • @Peter the machine has 24GB of physical. I am not sure of the max me set for each instance. I will look into that and update via comment. edit: it is currently set to: 2147483647 for both SQL Standard and Express instances. – csonon Mar 4 '17 at 3:16
  • Can you reliably predict how much ram the app consumes? Peak? – Sir Swears-a-lot Mar 5 '17 at 7:47
  • @Peter the application that I am using will consume upwards of 24+ GB of memory depending on runtime objects. Yes, that is a LOT of usage, however it does this for speed purposes during runtime. The in-memory processing of hundreds of thousands of objects has taken processing from hours to minutes. Obviously it is a trade off and we are working to resolve it. I've updated the max memory setting and will test during my next run of the application on the machine to see if it resolves it. – csonon Mar 5 '17 at 18:54
  • What did you set max mem to on each instance? – Sir Swears-a-lot Mar 5 '17 at 20:22
2

It sounds like your machine is memory starved. If your app uses a lot of memory and there are two sql instances, one standard and one express, with the default max memory setting if 2 tb, you're probably seeing the sql process cpu spikes as sql's memory is being paged out or returned to the os. You should set you max and min memory for each instance based on the workload, and also set the maximum memory your application will use if possible. That way you can ensure all three applications plus the Os have enough memory to coexist.

  • thanks for the reply. I've adjusted the memory to test and will be doing so this week. – csonon Mar 5 '17 at 18:56
1

When you say you were watching what was happening in Profiler, it probably isn't telling you the whole story.

First off, pick off the easy fruit:

  • Fix your max server memory (mb) setting
  • Make sure both instances are on the current SQL 2014 SP and CU patches. Every CU fixes at least one or two wild worker thread issues.

After this though if the problem persists, which is likely, then you fit the textbook case for a specific problem:

  • Everything is fine.
  • Something happens.
  • CPU or IO skyrocket and the application stops working.
  • Restarting the instance fixes everything (I've presumed this from your text but you can confirm).

That's almost always a bad plan being generated part-way through and then impacting everything that runs afterwards.

When you said you were watching Profiler... well... it's possible you see the same queries but you're missing that their CPU or IO numbers on completion have radically changed. Did you try to see if that was the case?

What about watching the plan recompilation event? You might be able to find which exactly one is recompiling before things turn bad.

You can of course also look into the plan cache but that's not trivial either.

For me all of that is too time consuming. If you run out of time, then use a free trial of some SQL performance monitoring software (I have a license for SentryOne which I use explicitly for this purpose alone). These often represent query execution times and plan cache changes in a GUI which helps track down this kind of problem way quicker and without all the effort.

  • thank you for the response. I will be running a test sometime this week on the customers machine with adjusted max memory settings to validate. Our tool is only run one time (live process) after the testing is complete so a temporary adjustment to the memory is a very acceptable solution. – csonon Mar 5 '17 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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