Post reply

Resource_Semaphore wait types

  • December 7, 2011 at 9:57 am

    #249227

    Hi All

    Noticed when diagnosing blocking using Spotlight, that the "type" is coming back as Resource_semaphore instead of something like the following LCK_S_IS.

    I have had a quick google and there was a bit about MAXDOP, its already set to 1, the server had 6GB RAM, and SQL has 5GB assigned to it.

    Just wondering what direction I should take now as I have never seen this before.

  • December 7, 2011 at 4:21 pm

    #1418420

    Generally, when you have many sessions waiting on RESOURCE_SEMAPHORE, then you are starved for working-space memory. One or more big queries are consuming lots of memory and preventing others from getting the memory grants they need.

    I'm not familiar with what Spotlight provides, I just use a query against the sys.dm_exec_* DMVs to get info on running processes (one of them is copied below), but you're looking for the ones with the largest granted_query_memory values in sys.dm_exec_requests (displayed a MBytesRAM in the query I pasted below). Usual suspects include large reporting queries and other similar ones that have lots of aggregation going on.

    SELECT CONVERT(decimal(4, 1), percent_complete) AS [%], estimated_completion_time/100/60 AS MinLeft,

    DB_NAME(st.dbid) AS [Database], convert(varchar(24), r.start_time, 20) AS StartTime, r.session_id, r.wait_type,

    wait_resource, r.wait_time, r.reads, r.writes, r.logical_reads, r.cpu_time,

    convert(decimal(9,1), (r.granted_query_memory / 128.0)) AS MBytesRAM,

    ss.host_name, ss.login_name, ss.program_name,

    CASE ss.transaction_isolation_level

    WHEN 1 THEN 'NoLock' WHEN 2 THEN 'ReadCom' WHEN 3 THEN 'ReadRpt!' WHEN 4 THEN 'Srlize!' WHEN 5 THEN 'SnapShot' ELSE '???'

    END AS LckLvl,

    IsNull(OBJECT_NAME(st.objectid, st.dbid), 'Ad hoc') AS ObjectName,

    SUBSTRING(st.text, (r.statement_start_offset/2)+1,

    ((CASE r.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE r.statement_end_offset

    END - r.statement_start_offset)/2) + 1) AS CurrentStatement

    FROM sys.dm_exec_connections c INNER JOIN

    sys.dm_exec_sessions ss ON c.session_id = ss.session_id INNER JOIN

    sys.dm_exec_requests r ON ss.session_id = r.session_id CROSS APPLY

    sys.dm_exec_sql_text(r.sql_handle) st

    WHERE r.session_id > 45 --and wait_time > 0

    ORDER BY r.start_time

    -Eddie

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply