睡眠執行緒是否持有記憶體

我們RESOURCE_SEMAPHORE在其中一台伺服器上遇到記憶體壓力問題（等待），但一次只有很少的活動連接。從邏輯上講，睡眠連接似乎不會保留記憶，但我想知道它們是否真的如此？IderaDM 顯示了幾個具有高記憶體使用率的睡眠連接。

這是具有 27 Gb 記憶體的 SQL 2008 SP3 伺服器。任何時候通常只有 2 到 10 個活動呼叫正在進行，其中很大一部分正在進行 resource_semaphore 等待。該伺服器保存我們的第三方數據庫，因此我們無法更改大多數呼叫/過程。

那麼，睡眠連接是否有可能保留記憶，或者我應該尋找其他地方？

如果您正在嘗試跟踪記憶體使用情況以及哪些查詢是最大的違規者，請執行以下步驟：

1. 確定實例上的哪個數據庫消耗的記憶體最多

-- DB using most cache
DECLARE @total_buffer INT
SELECT @total_buffer = cntr_value
  FROM sys.dm_os_performance_counters
  WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
  AND counter_name IN ('Total Pages', 'Database Pages');

WITH src AS
(
  SELECT
      database_id, db_buffer_pages = COUNT_BIG(*)
      FROM sys.dm_os_buffer_descriptors
      --WHERE database_id BETWEEN 5 AND 32766
      GROUP BY database_id
)
SELECT
  [db_name] = CASE [database_id] WHEN 32767
      THEN 'Resource DB'
      ELSE DB_NAME([database_id]) END,
  db_buffer_pages,
  db_buffer_MB = db_buffer_pages / 128,
  db_buffer_percent = CONVERT(DECIMAL(6,3),
      db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

2. 深入研究所述數據庫中的哪些對象消耗最多的記憶體

--objects in DB using most space
USE DatabaseName
GO
WITH src AS
(
   SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;

3. 查看哪些查詢正在命中記憶體中的所述對象

USE DatabaseName
GO

SELECT DISTINCT TOP 100 
       s.total_logical_reads / s.execution_count
   , SUBSTRING(t.TEXT, (s.statement_start_offset / 2) + 1, (
           (
               CASE s.statement_end_offset
                   WHEN - 1
                       THEN DATALENGTH(t.TEXT)
                   ELSE s.statement_end_offset
                   END - s.statement_start_offset
               ) / 2
           ) + 1) AS statement_text
   , s.execution_count AS ExecutionCount
   , s.max_elapsed_time AS MaxElapsedTime
   , ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime
   , s.creation_time AS LogCreatedOn
   , *
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE TEXT LIKE '%ObjectName_From_QueryAbove%'
   AND TEXT NOT LIKE '%sys.dm_exec_query_stats s%'
ORDER BY s.total_logical_reads / s.execution_count DESC

希望這會給您一些麻煩的查詢。您可能會發現實例中沒有足夠的記憶體來滿足它試圖容納的工作負載。或者，您可能會發現使用者編寫的查詢缺少像謂詞（例如WHERE子句）一樣簡單的內容。

如果你想深入研究執行計劃，你可以把plan_handle它發送到這個查詢中：

SELECT query_plan 
FROM sys.dm_exec_query_plan (PLAN_HANDLE_FROM_Query_3)

並審查執行計劃。如果您無法調整所述查詢，這可能對您沒有那麼有用。

一旦弄清楚實例上的記憶體在使用什麼，您就可以更好地了解如何使系統恢復到健康狀態。

引用自：https://dba.stackexchange.com/questions/152091