Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Server becomes very slow [RESOURCE_SEMAPHORE] waittype. RE: Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

  • May 13, 2009 at 1:42 pm

    #994044

    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[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]