Max Memory Configuration set too low [SQL Server]

We have been experiencing an issue with a clustered node where instances will run extremely slow and the only process eating CPU resources is “NT Kernel & System”. We have upgraded drivers and even wiped out and reinstalled Windows and SQL Server (both 2008 R2) and the issue reappears.

In the process of troubleshooting with Microsoft, we needed to fail over instances to collect log information. We have done this several times. Because our latest cluster implementations include Active/Active nodes, we need to adjust Max Memory to SQL Server whenever all instances are in one node.

Yesterday something did not go right and a human error caused the instances to failed. Even experienced DBAs will eventually break something, but they will do anything possible to correct. Memory was set to 27 instead of 27000 MB. No connections could be made to the instance.

There are 3 ways this situation can be resolved:

  1. Opening a dedicated connection to the instance (DAC) and changing the max memory configuration.
  2. Shutting down the instance and starting it via command prompt using the -f option. It will fire up the instance with minimal configuration. After that change the max memory configuration.
  3. Bouncing the instance and changing the memory configuration as soon as the instance starts. We are taking advantage of a fresh instance with no allocated cache yet.

Changing the memory configuration:

1
2
3
4
5
6
7
8
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 27000;
GO
RECONFIGURE;
GO

Coaching point, double or even triple check before issuing a configuration command.

SQL Server Memory Configuration via SSMS

SQL Server Memory Configuration via SSMS

Share
  • T_grzeg

    I have simillar problem with NT Kernel & System process. We also have cluster on Win2008 (2 nodes) and SQL 2008 R2 on it. In about 1 min intervals process “NT kernel & System” starts using CPU resources and SQL hangs for this time. Did you find solution?

    • We have not solved the problem yet and been trying several steps, including a node rebuild. We were able to upgrade the MPIO hit kit with the latest driver provider by Dell (Equallogic). We are not seeing high CPU utilization by “NT kernel & System” and are going to schedule a fail over to test it with an instance running on that node. I will be able to see if the issue was resolved completely after it.

    • Wanted to give you an update that after the MPIO driver and hit kit upgrade we have not observed this issue anymore. As per the System Engineer: “It was allowing uneccesary connections to the volumes when not needed. It was spiking because the older HIT kit would go seek for connections”

      I hope this helps you.

      • T_grzeg

        Thank you for informations. We also solved our problem but in our case locking pages in memory solve it. Thank you again for help.