Post reply

SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

  • January 6, 2012 at 3:20 am

    #249145

    Hi,

    I've read http://jefferytay.wordpress.com/2011/02/16/sql-server-having-high-resource_semaphore_query_compile-waits/

    But I can't get a clear picture.

    Hopefully, someone can elaborate with a better explanation

    Really need help

  • March 7, 2012 at 1:50 pm

    #1456237

    I have experienced this issue when load testing an application at a client site. We simply could not exceed 64000 concurrent users while the CPU was barely at 20%.

    I have been looking for a parameter which will allow us to override default compilation throttling and allow for more of it on SQL 2008 and above. To no avail.

    So, here are some tips:

    1. If you are lucky enough to have a few repetitive queries, try to rewrite or isolate the ad-hoc portion and place it into its own precompiled structure (function, SP).

    2. Use plan guides for the ad-hoc queries which are causing the mess. Not trivial either.

    Other options: Scale out the DB, or add CPUs as throttling is based on the number of CPUs.

    Good Luck

    Yassine Elouati

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

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