Server becomes very slow [RESOURCE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Server becomes very slow [RESOURCE_SEMAPHORE]...

35 posts, Page 2 of 4

««1 2 3 4 »»»

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Rate Topic

Display Mode

Topic Options

Author

Message

junk0

Posted Thursday, October 23, 2008 5:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 3:17 AM
Points: 5, Visits: 70

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

TheSQLGuru

Posted Friday, October 24, 2008 9:02 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,574, Visits: 5,113

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

Marios Philippopoulos

Posted Wednesday, May 13, 2009 7:24 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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

noeld

Posted Wednesday, May 13, 2009 1:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

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.

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

* Noel

Post #716376

Marios Philippopoulos

Posted Wednesday, May 13, 2009 1:42 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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.

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

ALZDBA

Posted Wednesday, May 13, 2009 3:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

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

     Jul 13

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

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #716471

ohmygoshitsbig

Posted Thursday, October 29, 2009 2:42 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, August 20, 2012 6:15 AM
Points: 27, Visits: 290

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

Marios Philippopoulos

Posted Friday, October 30, 2009 12:36 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Paul Hayes-294329 (10/29/2009)

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

__________________________________________________________________________________

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

TheSQLGuru

Posted Friday, October 30, 2009 2:45 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 3,574, Visits: 5,113

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

Post #811876

bcronce

Posted Friday, October 30, 2009 3:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 03, 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 »

35 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions