Large Network Packet Size and Memory Pressure in SQL Server

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:

Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576

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 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1384 KB
MultiPage 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

 

Sakthivel Chidambaram

SQL Server Support, Microsoft