Wait Types concern

  • Hi Experts,

    In one of our DW servers the performance is very slow that a simple select statment is taking hours. When checked i found many lastwaittype

    SOS_SCHEDULER_YIELD

    RESOURCE_SEMAPHORE

    CXPACKET

    Please let me know where to start

  • Start by posting the query, the index definitions and the execution plan, so that we can see what's happening.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail,

    Actually its SSIS packages running behind .

    Some queries used are

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dba].

    ') AND type in (N'U'))

    DROP TABLE [dba].

  • Got the above waittypes but CPU never reached more than 70%.

    The server is a vmware machine with 36 Core and 9GB RAM.

  • No where near enough information. Identify the queries with excessive wait times and investigate the cause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Checked for processes having huge wait time and they are jobs calling SSIS packages with waittype RESOURCE_SEMAPHORE.

  • Not an expert on SSIS, but from BOL:

    RESOURCE SEMAPHORE

    "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."

    Do you think the SSIS package(s) are trying too much parallel operations? I wonder what would happen if you scaled back the amount of concurrent operations it was trying to do at once what would happen.

    Joie Andrew
    "Since 1982"

  • You are not giving enough information to help solve the problem.

    I suggest you get a copy of the book 'troubleshooting SQL Server' and work through chapter 1.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dev team is running around 37 .dtsx files. In job they are calling the main .dtsx file which in turn calls the other files.

  • As Gail has said several times you aren't giving us much to go on.

    First thing to try is spreading out the SSIS activity so fewer things are running concurrently. You have likely reached what I call the "exponential breakover point", whereby one or more resources is so exhausted that things slow down exponentially due to the overload.

    Much more importantly is to bring on a qualified performance tuning expert. You could go back and forth on this issue for days or weeks and never get is solved via forum posts whereas a good consultant could isolate and resolve the issue(s) in a matter of hours or days.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks Kevin,

    Ours is a small organization and cant afford consultants at this point.

  • Ratheesh.K.Nair (11/16/2012)


    Thanks Kevin,

    Ours is a small organization and cant afford consultants at this point.

    Hopefully you can afford to have database servers that take hours to return answers to queries. I would be willing to bet there are any number of other suboptimal/unfortunate things in place, such as maintenance practices (LOTS here), integrity checks, server/sqlserver/database settings, ability to recover in an outage, etc. Many of the clients I come across (especially the small ones) truly could not afford to NOT get a person on board for at least a short time to fix critical items. I have also seen many clients spend WAY more on hardware than was necessary because their systems were not properly tuned/maintained.

    Anyway - best of luck resolving your performance issues!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 12 posts - 1 through 11 (of 11 total)

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