主页 / dba / 问题 / 152091
Accepted
MrTCS
Asked: 2016-10-13 05:33:17 +0800 CST

睡眠线程是否保留内存

  • 772

我们的RESOURCE_SEMAPHORE其中一台服务器存在内存压力问题（等待），但同时进行的活动连接很少。从逻辑上讲，睡眠连接似乎不会保留记忆，但我想知道他们是否真的这样做了？IderaDM 显示了几个内存使用率较高的休眠连接。

这是具有 27 Gb 内存的 SQL 2008 SP3 服务器。在任何时刻通常只有 2 到 10 个活动调用正在进行，其中很大一部分正在等待 resource_semaphore。该服务器拥有我们的第三方数据库，因此我们无法更改大部分调用/过程。

那么，睡眠连接是否有可能保留在内存中，或者我应该去别处寻找？

sql-server sql-server-2008

3 个回答

  1. Best Answer

    如果您正在尝试跟踪内存使用情况以及哪些查询是最大的违规者，请执行以下步骤：

    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)

    并审查执行计划。如果您无法调整所述查询，这可能对您没有用。

    一旦您弄清楚什么在使用实例上的内存，您就可以更好地了解如何让系统恢复到健康状态。

    • 3

  2. 我使用下面的查询，没有排除任何内容，因为我想查看 SQL Server 后台等待的位置，并且它们都将处于相同的百分比。HADR_FILESTREAM_IOMGR_IOCOMPLETION、XE_TIMER_EVENT和等项目REQUEST_FOR_DEADLOCK_SEARCH。如果您RESOURCE_SEMAPHORE在系统背景下，那么您就没有问题。如果是上面系统后台的东西，那就贴出来是什么。

    SELECT TOP 20 'Waits' as Waits
  ,GETDATE() AS check_time
  ,[Wait type] = wait_type
  ,[Wait time (s)] = wait_time_ms / 1000
  ,[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
  ,waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
    • 1

  3. RESOURCE_SEMAPHORE表示您有正在等待内存授予的查询。

    我会查询

     sys.dm_exec_query_memory_grants

    查看什么正在用尽您所有的查询工作区内存，以及什么正在等待内存。 本文提供了一些解决RESOURCE_SEMAPHORE问题 的好技巧。不幸的是，这通常意味着您需要调整一个或多个查询，因此如果它是第 3 方代码，您可能必须返回供应商。根据具体问题，您可能可以通过添加索引来改进它，但这只是推测。

    • 1

相关问题