Post reply

Performance Q

  • February 7, 2007 at 11:00 am

    #116198

    I've got a four way 2000 SP4 server that has a performance issue that is driving me crazy.  Every day at about 8:15 am the server gets timeouts in spite of the fact that avg queue length, processor and all other stats look very good.  These timeouts come from the web and even just people trying to log in with Query Analyzer.

    When I look at Profiler stats (such as cpu, reads, writes, number of queries) summarized by minute, there is nothing unique about this time frame.  I just don't see anything on the server that is different at 8:00 or 8:15 am and it's got me completely stumped.  In fact, the Profiler stats show the server getting worse as the morning goes on, i.e there is more activity from 9:00 to 10:00 am!

    Btw, I did not think it was a traditional blocking/locking issue because 1) some of the web timeouts happen instantaneously and 2) it happens just with someone logging into Sql Server with Query Analyzer.

    Does anyone have any ideas what else I can look at?  I am completely stumped.  Any ideas as to weird things that would cause timeouts once/day might help me out.

    Any help/ideas would be much appreciated.

     

     

  • February 7, 2007 at 11:45 am

    #688358

    Wisper,

    Add counters to the performance log and present results as a chart and see if something stands out:

    SQLServer:Locks(_Total)\Lock Wait Time (ms)

    SQLServer:Locks(_Total)\Number of Deadlocks/sec

    SQLServeratabases(_Total)\Transactions/sec

    Process(sqlservr)\Page Faults/sec

    Memory\Available MBytes

    Check if there are any jobs that lock the database like data import integrity checks or some other maintenance plan. Check Windows task scheduler. Something probably is running at 8:15 on schedule

    Regards,Yelena Varsha

  • February 7, 2007 at 3:44 pm

    #688416

    I only had one of those.  I added the rest.  I'll let you know what I find - thx for the help.

  • February 8, 2007 at 11:19 am

    #688632

    Again, thx for the help.  Here are the stats from the 10 minutes or so where users were complaining this morning:

     

    SQLServer:Locks(_Total)\Lock Wait Time (ms):

    0 for the enitre time in question

    SQLServer:Locks(_Total)\Number of Deadlocks/sec

    0 for the entire time in question

    SQLServer atabases(_Total)\Transactions/sec

    5-7 for the entire time in question although there was one minute of 31 trans/sec

    Process(sqlservr)\Page Faults/sec

    Between 60 and 166 for an average of about 100.  Buffer cache hit ratio was mostly 100 with an occasional 99 and one 98.

    Memory\Available Mbytes

    ~910 M. 

    Note:  Sql Server Working Set is about 2.86G and Total Server Memory is about 2.71G.

    Also, there were no growths of log or database during this time (assuming I understand the perf mon output).

  • February 8, 2007 at 11:21 am

    #688633

    Also here is the context switching during the most critical time:

    3,339
    4,995
    3,593
    2,854
    2,840
    5,756

    And here is the lock requests/second during the same time period:

    4,875
    3,035
    1,185
    9,356
    3,593
    8,883

  • February 9, 2007 at 10:07 am

    #688902

    Whisper, not sure about context switching, but other statistics is OK.

    Did somebody try to login using Query Analyzer being on the server computer, not over the network?

    I would try to open Enterprise Manager on the server locally, go to Current Activity window, Processes view at 8:10 AM and refresh every 30 seconds. You have to refresh the view, it does not do it by itself. Scroll all the way, see who is doing what. See what apps are running and what new process is starting up at 8:15 or whenever they call you about timeouts. I do know that all this info is in sysprocesses, but doing Processes view screenshot sometimes helps better.

    Regards,Yelena Varsha

Viewing 6 posts - 1 through 5 (of 5 total)

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