SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM.
Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?
SQL Server performance degraded in 32-Bit SQL Server after I added additional RAM or Do you see SQL Server memory errors after adding RAM ?
Following SQL Server Memory errors are logged in SQL error log after adding additional RAM:
Errors:
SQL Server 2005/2008
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880
Buffer Pool errors:
BPool::Map: no remappable address found.
Either BPool or MemToLeave errors:
Error: 17803 “Insufficient memory available..”
Buffer Distribution: Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…
Extract from https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/
{
SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool
Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start up as below.
MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space to load Dll’s.
Stack size =512 KB per thread for 32 Bit SQL Server
I.e. = (256 *512 KB) + 256MB =384MB
Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store COM objects, Extended stored procedure, Linked server in SQL Server process
Note: Additional space to load Dll’s can be modified using -g startup parameter.
on any machine with less than 4 processors the Maximum worker Thread’s is
always 256 by default (unless we change the value using SP_configure)
SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL- BUF structures”
BPool = Minimum (Physical memory, User address space – MTL) – BUF structures
}
When AWE is enabled in 32-Bit SQL Server M_pbuf (part of BUF structures) which is mentioned earlier is calculated and allocated for entire physical memory on the system . Regardless of “MAX Server Memory” This is to adjust Max server memory without restarting SQL Server.
SQL Server requires 8MB to create M_pbuf for every 1GB of RAM available on the server.
Machine with 64 GB RAM can consume 64 (RAM) *8MB (M_pbuf for each GB) =512 MB just for the BUF array alone.
So the amount of BPOOL available for SQL Server is adversely affected.
Going back to the previous formula for BPOOL. Size of Bpool for 32-Bit SQL Server with AWE enabled and 64 GB of RAM would be.
BPool = Minimum (Physical memory, User address space – MTL) – BUF structures
BPool= Minimum (64GB, (2GB-384MB)) - BUF structures (512+ MB)
Bpool would approximately become 1GB. Since size BPOOL become very small we might end up with memory errors.
Note: In 32-Bit SQL Server Only data pages an index pages can be placed in AWE memory. So the memory available for other SQL Server memory objects is still limited to BPOOL and MTL.
How to resolve this issue?
Remove few GB of RAM from server J if you can convince your management that removing RAM will improve performance.
(Or)
There is a startup trace flag TF 836 which you can use to indicate that BUF's need to be allocated only for the configured max server memory setting. Enable this Trace Flag (836) and Reduce the “MAX Server Memory” of SQL Server.
(Or)
Enable /3GB. This will increase the Size of SQL Server BPOOL by 1GB providing relief to SQL Server BPOOL pressure.
Note: When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed.
Reference:
https://mssqlwiki.com/sqlwiki/sql-performance/troubleshooting-sql-server-memory/
Thanks
Karthick P.K