SQL Server Memory Management

Buffer Pool

The buffer pool is the core memory cache where SQL Server stores data pages, index pages, and system objects. It dynamically grows and shrinks based on workload.

Key DMVs

SELECT
    database_id,
    page_type,
    COUNT(*) AS page_count
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id, page_type
ORDER BY page_count DESC;

Configuring Max Server Memory

Set max server memory (MB) to reserve memory for the OS and other processes.

EXEC sys.sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

Memory Grants (Query Execution)

Memory grants allocate workspace for sorts, hashes, and row aggregates.

Monitoring Grants

SELECT
    session_id,
    requested_memory_kb/1024.0 AS Requested_MB,
    granted_memory_kb/1024.0   AS Granted_MB,
    max_used_memory_kb/1024.0 AS Max_Used_MB,
    query_plan
FROM sys.dm_exec_query_memory_grants
WHERE requested_memory_kb > 0
ORDER BY requested_memory_kb DESC;

Best‑Practice Tips

• Avoid excessive row‑count estimates – update statistics regularly.
• Set resource governor limits for runaway queries.
• Use OPTION (RECOMPILE) sparingly to prevent plan cache bloat.

Monitoring Memory Usage

Performance Counter Overview

• SQLServer:Memory Manager – Total Server Memory (KB)
• SQLServer:Memory Manager – Target Server Memory (KB)
• SQLServer:Buffer Manager – Buffer cache hit ratio

Live Dashboard (Demo)

Best Practices

1. Set a realistic max server memory based on total RAM and OS needs.
2. Enable optimize for ad hoc workloads if you have many single‑use queries.
3. Regularly review sys.dm_os_memory_clerks for unexpected memory consumers.
4. Use DBCC MEMORYSTATUS for deep diagnostics.

FAQ

What happens if max server memory is set too high?

The OS may run out of memory, leading to paging, which degrades performance.

How can I force a buffer pool flush?

Use DBCC DROPCLEANBUFFERS (only in test environments).

Is it safe to change max server memory on a production server?

Yes, but do it during low‑traffic periods and monitor Target Server Memory to ensure the change takes effect.