一.概述 這篇介紹Stolen記憶體相關的主要三種等待類型以及對應的waittype編號，CMEMTHREAD(0x00B9)，SOS_RESERVEDMEMBLOCKLIST(0x007B)，RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)。也可以通過sysproce ...
一.概述
這篇介紹Stolen記憶體相關的主要三種等待類型以及對應的waittype編號，CMEMTHREAD(0x00B9)，SOS_RESERVEDMEMBLOCKLIST(0x007B)，RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)。也可以通過sysprocesses里查看連接處於某個等待狀態, waittype!=0x0000。
select * from sys.sysprocesses where waittype!=0x0000 and spid>50
再次看下Stolen記憶體的分配場景：
1. CMEMTHREAD記憶體
cmemthread是指多個用戶同時往同一塊緩存里申請或釋放記憶體時，在一個時間點上， 只有一個連接可以做申請或釋放記憶體動作， 其他連接必須等待。原因：出現這種等待的原因通常是發生在併發度非常高的sqlserver里，而這些併發的連接，在大量地使用需要每次都做編譯的動態t-sql語句。 解決：修改客戶連接行為，儘可能更多地使用存儲過程， 或者使用參數化的t-sql語句，減少語句編譯量增加執行計劃的重用，避免大量連接同時申請記憶體做語句編譯的現象。
在生產環境下cmemthread平均每次請求時間為0.20ms(1570876.0/7825922.0=0.20)
2.SOS_RESERVEDMEMBLOCKLIST
sos_reservedmemblocklist是指當用戶要申請MemtoLeave這塊記憶體時而暫時不能滿足就會出現等待。原因：當用戶發過來的語句內含有大量參數，或者有一個in 子句，它的執行計劃在8kb的singlepage里可能放不下,需要用multi-page來存儲。當緩存的執行計劃越來越多，multi-page里的記憶體也會越來越多。 解決：(1)避免使用帶有大量參數或者長in子句的語句，這種語句需要消耗比正常語句更多的記憶體及cpu資源， 改變的方法是可以把參數值存儲到臨時表，用join來連接。(2)定期運行dbcc freeproccache 語句，手工清除緩存中的執行計劃，緩存記憶體壓力。
-- 查看緩存占用空間 SELECT SUM(CONVERT(DECIMAL(18,4),size_in_bytes))/1024.0/1024.0 AS 'sizeMB'
FROM sys.dm_exec_cached_plans
--查看緩存中的對象類型,重用次數,sql語句,緩存空間大小,可以根據幾個維度來統計 SELECT usecounts,size_in_bytes/1024.0 AS 'sizeKB',cacheobjtype,objtype,[text] FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 ORDER BY usecounts DESC
3.RESOURCE_SEMAPHORE_QUERY_COMPILE
resource_semaphore_query_compile是指：當編譯的語句需要的記憶體達到了sqlserver的編譯記憶體上限時(sqlserver會為編譯記憶體設置一個上限)，其它語句將進入等待狀態，等前面的語句編譯完成，把記憶體釋放出來以後，後面的語句才能繼續編譯。解決(1)修改客戶連接行為，儘可能更多地使用存儲過程， 或者使用參數化的t-sql語句，減少語句編譯量，增加執行計劃的重用，避免大量連接同時申請記憶體做語句編譯的現象.(2)簡化每次需要編譯語句的複雜度，降低編譯需要的記憶體量。(3)當stolen 記憶體使用總量比較大的時候，也可以定期執行dbcc freeproccache 。
總結：以上三種等待類型，當緩存的執行計劃越來越多，存放buffer pool里的stolen記憶體在不斷增長，當需要的記憶體超過8kb時，multi-page里的存儲執行計劃stolen記憶體也會越來越多 。能過sys.sysprocess.waittype欄位，可以檢查stolen記憶體上是否有瓶頸。通過sql server 記憶體初探 知道 sql server里的Consumer下的功能組件,第三方代碼,線程都是能過stolen方式直接提交，並不需要先申請記憶體。
查看記憶體使用情況
-- 按申請方式統計記憶體 (Reserve 再commit)（直接commit叫Stolen）
SELECT
SUM(virtual_memory_reserved_kb)/1024.0 AS 'reserved(MB)',
SUM(virtual_memory_committed_kb)/1024.0 AS 'committed(MB)',
(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024.0 AS 'Stolen(MB)'
FROM sys.dm_os_memory_clerks
-- 按申請記憶體頁大小統計記憶體
SELECT
(SUM(virtual_memory_committed_kb)+SUM(single_pages_kb))/1024.0 AS 'Buffer Pool(MB)',
SUM(multi_pages_kb)/1024.0 AS 'MemToLeave(MB)'
FROM sys.dm_os_memory_clerks
按申請方式統計記憶體，共申請了92576MB，提交了83621MB， 在Stolen中有9244MB。 如下圖所示：
按申請記憶體頁大小（<=8kb >8kb）統計記憶體：
