Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?
I am not going to write how Optimizer can some times choose suboptimal plans when we have large amount of memory on the system but We will see how the memory which can be used by other memory clerks (aks: stolen memory) can shrink when we have large physical memory and AWE enabled.
If you notice performance of 32-Bit SQL Server degraded after you added additional RAM or if you see SQL Server memory errors like ones below after adding RAM then it could be because of Large BUF structures which reduced the size of Bpool.
Errors:
SQL Server 2005/2008
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 SQL Server memory design
{
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 (904K under WOW)
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.
Related posts
Troubleshooting steps for all SQL Server Memory errors
SQL Server lock pages in memory should I use it?
What is new in SQL Server 2012 Memory
How to set max server memory and min server memory
If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
MSSQLWIKI 