SQL Server Performance

sporadic performance issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by danj, Sep 29, 2009.

  1. danj New Member

    hi all,
    we are seeing sporadic performance on sql server 2005 where in, the app is running fine in the morning and all of a sudden starts slowing down in the afternoon.
    its a windows 2003 server, with separate disks for transaction log ,data and indexes.
    we have been running traces with duration filter > 2 seconds and when performance starts going bad, we start few queries pop up in the trace. These queries generally run sub 1 second when performance is good.
    we have been logging wait stats every 10 minutes and sos_scheduler_yield and writelog seems to be one bubbling to the top.
    sos_scheduler_yield - absolute value (wait time ms subtracted from previous reading) interval of 10 minutes
    1953,672,2891,2234,19391,94562,217563,1371750 ...
    writelog - absolute value (wait time ms subtracted from previous reading) interval of 10 minutes
    36031,135672,180375,705625...
    does this indicate a problem with sql server 2005? and am i on the correct path?
    could sos_scheduler_yield issue could be related to compile / recompile?
    any hints on isolating the issue?
    thanks
    dan
  2. FrankKalis Moderator

    Welcome to the forum!
    Just to make sure:
    - Can it be that something else is kicking in in the afternoons (job, maintenance, etc...)
    - Overall load is the same at both times. Number of users, type of queries...
  3. satya Moderator

  4. danj New Member

    thank you all.
    1. We have gone over various scheduled job / back up etc... and either changed them to out of 9-5 hours or were already running outside of 9-5 period.This was accomplished more then 2 -3 weeks ago and we could have missed some thing.will check once again.
    2. We had few other issues going on such as reports being run on OLTP database etc..which have been eliminated and this boosted the performance.
    3. We had a missing index on key table (wasn't moved from testing / load test to prod), which again boosted the performance.
    4. we are logging all the statistics and yesterday was one of the better days. The dba has been running perfmon / profiler etc..
    and has added batch req/sec , compile/sec etc.. from yesterday.So it will be interesting see the difference between good day and bad w.r.t the no of request to the database.
    one thing i noticed was that compiles/sec is nearly 10% of batch request /sec. We use java as the middle ware and use quite a few statement objects when compared to prepared statement. Would this cause compiles to be high? Some say 10% compile is ok - on the verge of upper limit and some say it should be way less.
    any thoughts on this?
    thank you
    dan.

  5. Adriaan New Member

    If your Java app is making up query statements, and is using its own libraries to connect to SQL Server, then consider changing to stored procedures.
  6. danj New Member

    what about server side cursors?
    we captured 5 -10 minutes of all queries during poor performance and found quite a few server side cursors being created. The interesting thing, is that the work flow has a higher chance of executing code that created server side cursors or rather implementation of java resultset that created server side cursor in the afternoon then in the morning.we are going to get rid of it as i read it good to avoid server side cursors.
    how do i troubleshoot if this is causing the issue?

    dan
  7. Adriaan New Member

    I'd say the server-side cursors are consistent with sub-optimum queries issued by a client app.

Share This Page