Sunday, October 18, 2015

How much memory does a SQL Server connection use?

Someone asked me the other day how much memory a SQL Server connection uses. I told him that I remember it being about 40 kb or so. We decided to look it up, in the SQL Server internals books it says it is about 24 kb. I was looking in Books On Line and the only thing I could find was the following on the SQL Server Connection Basics page

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

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

No comments: