As system engineers we often run into situations where SQL servers are unexpectedly running hot at 100% resource usage. Even when many times there is nothing going inside SQL to warrant such a state.
In the majority of instances, engineers don’t seem to realize that SQL out of the box is very opportunistic and will consume ALL resources if allowed. The solution is simple, limit the maximum memory consumed within SQL studio.
You can limit the maximum memory usage for the SQL server instance which can limit the memory usage to SQL services and leave the remaining memory to other programs.
Use the Management Studio to set Max memory to SQL
- In SQL Server Management Studio, connect to the desired SQL Server database engine, right-click the desired instance and click Properties.
- In the Server Properties dialog box, select the Memory item from the list on the left.
- In the Maximum server memory (in MB) option, specify the desired maximum value.
- Click OK to save your changes.
Use the below link to calculate SQL max memory