Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 11:39 PM
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.
Post #590389
Posted Friday, October 24, 2008 9:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 4,463, Visits: 6,391
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
Post #591287
Posted Wednesday, May 13, 2009 7:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #715955
Posted Wednesday, May 13, 2009 1:37 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #716376
Posted Wednesday, May 13, 2009 1:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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).


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #716380
Posted Wednesday, May 13, 2009 3:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 6,748, Visits: 8,544
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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #716471
Posted Thursday, October 29, 2009 2:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 11:46 AM
Points: 27, Visits: 295
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
Post #811211
Posted Friday, October 30, 2009 12:36 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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...


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #811805
Posted Friday, October 30, 2009 2:45 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 4,463, Visits: 6,391
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!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #811876
Posted Friday, October 30, 2009 3:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 7:36 AM
Points: 127, Visits: 517
Intel's new i7 Xeons support Hyperthreading and it actually works this time
Post #811898
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse