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).
Best Answer
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.