0

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?

  • 1
    TeamCity connections were the ones sleeping that appeared to be holding onto the memory. They restarted the TeamCity service and 18 Gb freed up. – MrTCS Oct 14 '16 at 15:47
3

If you're trying to track down memory usage and what queries are the biggest offenders, run through the following steps:

  1. 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;
    
  2. 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;
    
  3. 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.

1

RESOURCE_SEMAPHORE means you have queries that are waiting for a memory grant.

I would query

 sys.dm_exec_query_memory_grants 

to see what is using up all of your query workspace memory, and what is waiting for memory. This article gives some good tips for troubleshooting RESOURCE_SEMAPHORE issues. Unfortunately, it typically means you need to tune a query or queries, so if it is 3rd Party code, you may have to go back to the vendor. Depending on the specific issue, you may be able to improve it by adding an index, but that is just speculation.

1

I use the below query with nothing excluded because I want to see where the SQL Server background waits are and they will all be at the same percentage. Items like HADR_FILESTREAM_IOMGR_IOCOMPLETION, XE_TIMER_EVENT, and REQUEST_FOR_DEADLOCK_SEARCH. If your RESOURCE_SEMAPHORE is below the system background stuff then you don't have a problem. If it is above the system background stuff, then post what it is.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.