Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Author
Message
junk0
junk0
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 72
Just a follow up.

After working with MS support - the problem was a memory leak, which was fixed by CU9.
So if anyone else is having the same problem - apply CU9.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 8301
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.


We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
Marios Philippopoulos (5/13/2009)
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.


We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.


I have only found this issue on overloaded servers. but I could be wrong.


* Noel
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
noeld (5/13/2009)
Marios Philippopoulos (5/13/2009)
TheSQLGuru (10/24/2008)
Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a memory leak.


We had the same problem recently. I killed all sessions that were stalled on RESOURCE_SEMAPHORE (there were several of those), and the problem went away. We are on SQL Server 2005 SP2; I wonder if SP3 has a fix for this issue.


I have only found this issue on overloaded servers. but I could be wrong.


Our server is overloaded, mainly because of a large number of ad-hoc sessions (ie. sessions not connecting to the databases through the business application).

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
If your business applications connect providing all possible connection info (application name, workstation id,...) ....
those non-business application connections can easily be rejected using a login trigger and only allow certain predefined applications or hostnames.

In many cases, also in my shop , business responsables will not let you take such rigid measures because they fear support might be endangered.
Which is rubbish btw.

It may be a way out of stressed situations. (as well for you sqlserver, as for your server, as for your team, as for yourself)

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ohmygoshitsbig
ohmygoshitsbig
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 303
a resource semaphore wait type is typically waiting on a memory grant, check pending/outstanding memory grants in perfmon. That's normally because a query plan has compiled in a "non-optimal" fashion.

You can identify which queries are causing this to occur by running the query below.
You may need to be more selective with youre where clause or add an index, identify the query, go through the tuning process and sort it out.

Production server you say, maxdop to 1 mate, every time. Thats many years of experience talking. Maxdop is a waste of time on anything but pure reporting systems in my experience. Ive seen it grind whole companies to a halt, the default setting pre-2008 is really unsafe, unpredictable and shit. Just turn parallelism off for a more predixctable experience IMO.

--Replacement for master.dbo.sysprocesses

SELECT r.session_id -- new column for SPID

,r.database_id

,r.user_id

,r.status

,st.text

,r.wait_type

,r.wait_time

,r.last_wait_type

,r.command

,es.host_name

,es.program_name

,es.nt_domain

,es.nt_user_name

,es.login_name

,mg.dop --Degree of parallelism

,mg.request_time --Date and time when this query requested the memory grant.

,mg.grant_time --NULL means memory has not been granted

,mg.requested_memory_kb --Total requested amount of memory in kilobytes

,mg.granted_memory_kb --Total amount of memory actually granted in kilobytes. NULL if not granted

,mg.required_memory_kb --Minimum memory required to run this query in kilobytes.

,mg.query_cost --Estimated query cost.

,mg.timeout_sec --Time-out in seconds before this query gives up the memory grant request.

,mg.resource_semaphore_id --Nonunique ID of the resource semaphore on which this query is waiting.

,mg.wait_time_ms --Wait time in milliseconds. NULL if the memory is already granted.

,CASE mg.is_next_candidate --Is this process the next candidate for a memory grant

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

ELSE 'Memory has been granted'

END AS 'Next Candidate for Memory Grant'

,rs.target_memory_kb --Grant usage target in kilobytes.

,rs.max_target_memory_kb --Maximum potential target in kilobytes. NULL for the small-query resource semaphore.

,rs.total_memory_kb --Memory held by the resource semaphore in kilobytes.

,rs.available_memory_kb --Memory available for a new grant in kilobytes.

,rs.granted_memory_kb --Total granted memory in kilobytes.

,rs.used_memory_kb --Physically used part of granted memory in kilobytes.

,rs.grantee_count --Number of active queries that have their grants satisfied.

,rs.waiter_count --Number of queries waiting for grants to be satisfied.

,rs.timeout_error_count --Total number of time-out errors since server startup. NULL for the small-query resource semaphore.

,rs.forced_grant_count --Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.

FROM sys.dm_exec_requests r

INNER JOIN sys.dm_exec_sessions es

ON r.session_id = es.session_id

INNER JOIN sys.dm_exec_query_memory_grants mg

ON r.session_id = mg.session_id

INNER JOIN sys.dm_exec_query_resource_semaphores rs

ON mg.resource_semaphore_id = rs.resource_semaphore_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)st



--Is anything timing out

SELECT * FROM sys.dm_exec_query_optimizer_info

WHERE counter = 'timeout'

anyway thats from here, which does a pretty spot on job of explaining resource semaphores.

http://blogs.solidq.com/EN/rdyess/Lists/Posts/Post.aspx?List=46f36841%2D4c69%2D4240%2Db060%2De0712db6f3ba&ID=16
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
Paul Hayes-294329 (10/29/2009)
a resource semaphore wait type is typically waiting on a memory grant, check pending/outstanding memory grants in perfmon. That's normally because a query plan has compiled in a "non-optimal" fashion.

You can identify which queries are causing this to occur by running the query below.
You may need to be more selective with youre where clause or add an index, identify the query, go through the tuning process and sort it out.

Production server you say, maxdop to 1 mate, every time. Thats many years of experience talking. Maxdop is a waste of time on anything but pure reporting systems in my experience. ...Just turn parallelism off for a more predixctable experience IMO.

--Replacement for master.dbo.sysprocesses

...


Thanks very much for the query identifying queries waiting for memory grants, I'll add to my tool box.

BTW, I totally agree on the MAXDOP comment, I've been having the same experience.
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance, and not just on Halloween night... w00t

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5943 Visits: 8301
CXPACKET waits (parallelism) are just a mask of something more sinister going on with query performance


Hmm - in my experience CXPACKET waits are almost always the result of a suboptimal IO system. While I do consider this a client SIN, I don't think it is really SINISTER! w00t

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
bcronce
bcronce
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 517
Intel's new i7 Xeons support Hyperthreading and it actually works this time
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search