Today I worked on a interesting performance issue whereby SQL Server 2005 x86 (32-bit) had sever Non-BPool memory pressure causing all sort of errors lilke
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
Downgrading backup log buffers from 1024K to 64K
Failed allocate pages: FAIL_PAGE_ALLOCATION 6
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection
Server had AWE enabled and memory capped to 9 GB. There was no -g switch enabled to increase MTL size.
When I looked the DBCC MEMORYSTATUS which gets printed during memory pressure, this is what I saw:
Memory node Id = 0 VM Reserved = 1879968 KB VM Committed = 364976 KB AWE Allocated = 10289152 KB SinglePage Allocator = 81072 KB MultiPage Allocator = 227920 KB
When I scanned through the list of clerks, objects to find out who is eating the 225 off MB from Multipage allocator, got this:
OBJECTSTORE_SNI_PACKET (Total)VM Reserved = 0 KBVM Committed = 0 KBAWE Allocated = 0 KBSM Reserved = 0 KBSM Committed = 0 KBSinglePage Allocator = 1384 KBMultiPage Allocator = 201456 KB
This looks to be a problem with high memory used by TDS Packets.
We looked into sp_configure to see whether default network packet size is changed but it was default 4096 bytes.
Queried the DMV sys.dm_exec_connections and it reported that there are 1700 active connections with network packet size of 32576 bytes so this answers the problem.
Being a 32-bit SQL with 256 MB MTL (Non-BPool), having more number of connections with higher network packet size is causing memory pressure in SQL Server.
Recommendation in this situation is to:
--> Reduce network packet size to < 8K
--> Upgrade to 64-bit
--> Use -g to allocate more memory to MTL as a interim solution
SQL Server Support, Microsoft