Post reply

SQL Server slow response, network client fast response

  • June 21, 2004 at 3:02 am

    #85302

    Hi All

    We have a sqlserver running on a new 4 way machine with 2 gig of ram and plenty of disk.  On the same server there is an application that loads documents into SQL Server.  When the application first starts loading documents into SQL Server it does so at the rate of about 5 per second. 

    After a few hours at this rate it suddenly runs very slow, only loading documents at the rate of 1 every 2 or 3 seconds.  This continues indefinitely until the server is restarted.  We have tried restarting the document loading application and stopping and starting SQL Server, but it doesn't help.

    We have also noticed the queries performed on the local machine in query analyser take 7 seconds, where as the same query executes in a fraction of second when run from a workstation using query analyser.

    Performance monitor indicates no disk queueing, CPU at 15 percent, plenty of memory to spare. 

    We checked out the waitstates in SQL Server which indicated a high percentage of CXPACKET waits.  We turned of parallelism and the CXPACKET waits dropped significantly, so much so that it isn't an issue anymore. The waitstate that features the highest at the moment is the NETWORK IO, which I believe to be normal.

    Any idea's

  • June 22, 2004 at 3:05 am

    #511357

    How is SQL Servers memory configured?

    Does the doc-loading app compete with SS over memory..

    If memory is set on auto, try to cap the server at some level (ie 1Gig), allowing the app and o/s some free headroom.

    /Kenneth

  • June 22, 2004 at 6:49 am

    #511407

    How is data loaded ? Are they loaded thru. bcp or bulk insert ? Is there any constraints or trigger on the target table ?

  • June 23, 2004 at 3:08 am

    #511582

    Thanks Guys for your response.  The people setting up the Doc loading server had turned on odbc logging to diagnose another problem.  The log file had grown to 17 gig and was causing the problem.  Once logging was turned off and the log file deleted the performance came back

    Thanks again for your response.

Viewing 4 posts - 1 through 3 (of 3 total)

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