SQL server MAX server memory configuration
Introduction:-
This document addresses SQL Server memory issues, including errors in resource pools internal and default, which impact query execution and system performance. It provides a step-by-step guide for troubleshooting, including health checks for blocking sessions, long-running jobs, and memory-intensive queries. The document also explains how to configure the maximum server memory to optimize performance. Regular monitoring and proper configuration are emphasized to ensure smooth database operations.
Error messages:-
There is insufficient system memory in resource pool ‘internal’ to run this query.
There is insufficient system memory in resource pool ‘default’ to run this query.
Step 1:-
Before proceeding with memory expansion, we need to perform a health check on our SQL instance first.
1. Blocking session check.
Make sure there are no blocking or locking sessions consuming memory or CPU resources.
Use Below query to check blocking sessions:-
SELECT
blocking_session_id AS BlockingSessionID,
session_id AS BlockedSessionID,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
Or
We can check using sp_who2
2. Long running Jobs
Identify any long-running jobs that are holding memory usage or consuming CPU resources
SELECT
session_id,
DB_NAME(database_id) AS DatabaseName,
start_time AS StartTime,
status,
DATEDIFF(SECOND, start_time, GETDATE()) AS RunningTimeInSeconds,
SUBSTRING(text, 1, 100) AS QueryText — Shows first 100 characters of the query
FROM
sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
r.status = ‘running’
AND DATEDIFF(SECOND, r.start_time, GETDATE()) > 60 — Only queries running longer than 60 seconds
ORDER BY
RunningTimeInSeconds DESC;
3. Check for any sessions that are holding memory pressure using the query below.
SELECT
session_id,
memory_grant,
memory_used,
memory_reserved
FROM sys.dm_exec_query_memory_grants;
4. Server level checks.
Use Task Manager or Resource Monitor to identify if other processes are consuming a significant portion of the available memory
Step 2:- Increase max server memory:
1. Check server available memory:-
SELECT
available_physical_memory_kb / 1024 AS AvailableMemoryMB,
total_physical_memory_kb / 1024 AS TotalMemoryMB
FROM
sys.dm_os_sys_memory;
2. Check the current max server memory setting in MB
SELECT
name,
value_in_use / 1024 AS MaxServerMemoryMB
FROM
sys.configurations
WHERE
name = ‘max server memory (MB)’;
In this case we notices server memory 9000 MB we have added another 1000 MB. As we noticed server available memory 3 GB
Execute below statements one by one to configure SQL server max server memory
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘max server memory’, 10000;
RECONFIGURE;
Result:-
SELECT
name,
value_in_use / 1024 AS MaxServerMemoryMB
FROM
sys.configurations
WHERE
name = ‘max server memory (MB)’;
Conclusion:-
To resolve memory issues in SQL Server, it is essential to identify the root cause, such as blocking sessions, long-running queries, or insufficient memory allocation. By performing checks on the system’s memory usage and adjusting the “max server memory” setting, we can ensure that the SQL Server runs efficiently. Regular monitoring and proper configuration of memory will help prevent performance issues and ensure smooth operation of the database.
