SQL Server sets aside three packet buffers for every connection made from a client. Each buffer is sized according to the default network packet size specified by the sp_configure stored procedure. If the default network packet size is less than 8KB, the memory for these packets comes from SQL Server's buffer pool. If it's 8KB or larger, the memory is allocated from SQL Server's MemToLeave region.
It's worth noting that the default network packet size for the .NET Framework Data Provider for SQL Server is 8KB, so the buffers associated with managed code client connections typically come from SQL Server's MemToLeave region. This contrasts with classic ADO applications, where the default packet size is 4KB, and the buffers are allocated form the SQL Server buffer pool.
So the 24 kb mentioned in the SQL Server internals book make sense since 3 * 8 kb is 24 kb. What about if you network packet size is not 8 kb?
You can change the network packet size by using sp_configure. The minimum network packet size is 512 kb, the maximum network packet size is 32767 kb, and the default network packet size is 4096 kb.
So my question is: does the amount of memory a SQL Server connection use change base on the network packet size?
Leave me a comment here or you can answer it on twitter as well
Does the amount of memory a SQL Server connection use change base on the network packet size? #sqlhelp longer here: http://t.co/JZGWPW2sAN
— Denis Gobo (@DenisGobo) October 18, 2015
SQLSoldier answered the question.It is Approximately (3 * network_packet_size + 94 KB). However when using MARS it is different.... When using multiple active result sets is enabled, the user connection is approximately (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.
You can find the answer here: Memory Used by SQL Server Objects Specifications
