SQL Server Sleeping會話佔用記憶體資源淺析?
在SQL Server中,會話的狀態有執行(Running)、睡眠(Sleeping)、休眠(Dormant)、Preconnect 等狀態,有時候你會在資料庫中看到很多會話處於睡眠(Sleeping)狀態,那麼這些睡眠(Sleeping)狀態的會話會消耗CPU、Memory資源嗎?如果消耗資源的話,那麼sleeping會話具體消耗多少記憶體資源呢? 另外它會影響資料庫效能嗎?
首先,處於睡眠(Sleeping)狀態的會話意味著當前沒有執行任何請求。這樣也意味著這種狀態的會話是不會消耗CPU資源的,那麼它是否消耗記憶體資源呢?答案是會,它會保留了一定數量的記憶體,用於儲存與會話相關的結構。 這包括會話上下文等資訊, 關於每個處於睡眠狀態會話消耗的記憶體具體大小,可以使用下面指令碼檢視睡眠(Sleeping)狀態會話消耗的記憶體(不確定其值的準確性,文末有介紹):
SELECT s.session_id , s.status , s.last_request_start_time , s.memory_usage*8 AS memory_usage_kb , t.task_state , s.host_name , s.program_name , s.transaction_isolation_level --, s.open_transaction_count FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id LEFT JOIN sys.dm_os_tasks AS t ON t.session_id = s.session_id WHERE s.status='sleeping' ORDER BY s.memory_usage DESC;
使用這個這個指令碼查出,發現處於睡眠(Sleeping)狀態會話消耗的記憶體有很多情況: 0KB、16KB、24KB、32KB、40KB、.....216KB. 似乎沒有什麼規律,當然以32KB、40KB居多。官方文件關於這方面的介紹幾乎沒有,僅僅在 “ Memory Used by SQL Server Objects Specifications ” 中有部分介紹,如下所示:
下表列出 SQL Server 中不同物件所用記憶體的大致數值。列出的數值為估計值,具體取決於環境和建立物件的方式。 SQL Server 2005 管理某些項的方式與早期版本存在顯著差異。
SQL Server 2000 |
SQL Server 2005 |
SQL Server 2008 |
|
鎖 |
每個所有者 64 位元組 + 32 位元組 |
每個所有者 64 位元組 + 32 位元組 |
每個所有者 64 位元組 + 32 位元組 |
開啟的資料庫 |
每個檔案 3924 位元組 + 1640 位元組,每個檔案組 336 位元組 |
不適用於 SQL Server 2005 |
不適用於 SQL Server 2008 |
開啟的物件 |
物件中每個開啟的索引 256 位元組 + 1724 位元組 |
不適用於 SQL Server 2005 |
不適用於 SQL Server 2008 |
使用者連線 |
12 KB + (3 * network_packet_size) |
約為 (3 * network_packet_size + 94 KB) |
約為 (3 * network_packet_size + 94 KB) |
網路資料包大小是表格格式資料流 (TDS) 資料包的大小,該資料包用於應用程式和 SQL Server 資料庫引擎之間的通訊。預設的資料包大小為 4 KB ,由 “ 網路資料包大小 ” 配置選項控制。
啟用多個活動的結果集時,使用者連線約為 (3 + 3 * num_logical_connections) * network_packet_size + 94 KB 。
但是在SQL Server 2008之後的文件,沒有看到這方面內容的介紹。在SQL Server 2005、SQL Server 2008資料庫中測試發現(使用最上面的指令碼)memory_usage大小為16KB、24KB,遠小於94KB, 這個文件和測試結果也一度讓我懷疑sys.dm_exec_sessions這個DMV檢視中memory_usage欄位計算Sleeping會話消耗記憶體的準確性 。很遺憾,官方資料關於memory_usage的介紹僅為:Number of 8-KB pages of memory used by this session. Is not nullable. 不過,有點可以確認的是:Sleeping狀態的會話消耗的記憶體資源真的很少。基本上對資料庫效能影響很小。
參考資料:
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/aa337559(v=sql.105)
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/62d19c9e-7ccb-4e1b-a18f-f249c23f0376/sleeping-spids-consume-cpu-and-memory-?forum=sqlgetstarted