Do Sleeping Threads Hold Memory We are having memory pressure issues (RESOURCE_SEMAPHORE waits) on one of our servers but there are very few active connections going on at one time. Logically, it wouldn't seem like sleeping connections would hold on to memory but I'm wondering if they actually do?  IderaDM shows several sleeping connections with high Memory Usage.

This is a SQL 2008 SP3 server with 27 Gb of memory.  There's typically only two to 10 active calls going on at any one moment and a good portion of them are having resource_semaphore waits.  This server holds our third party DBs so we can't change most of the calls/procs.

So, is it possible that sleeping connections are holding onto memory or should I be looking elsewhere?
If you're trying to track down memory usage and what queries are the biggest offenders, run through the following steps:


Identify what DB on the instance is consuming the most memory

-- 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;

Dig into what objects within said db are consuming the most memory

--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;

Review what queries are hitting said objects in memory

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



Hopefully this gives you some troublesome queries.  You may find that you just don't have the proper amount of memory in the instance for the workload it's trying to accommodate.  Alternatively you may find a query written by a user missing something as simply infuriating as a predicate (e.g. the WHERE clause).

If you want to dig into the execution plan, you can take the plan_handle and send it into this query:

SELECT query_plan 
FROM sys.dm_exec_query_plan (PLAN_HANDLE_FROM_Query_3)


and review the execution plan.  If you are not able to tune said query, this probably isn't as useful to you.

Once you figure out what's using the memory on the instance, you can come up with a better idea on how to get the system back to a healthy state.