SQL Server Performance


Discussion in 'Performance Tuning for DBAs' started by ajitgadge, Jul 30, 2007.

  1. ajitgadge New Member

    I am facing slowness issue on my SQL 2000 Server with Std edition. Actually, there is lot's of processes is running on server but which goes in Sleeping mode. When i checked in Sysprocesses, it shows me LastWait Type is WriteLog. I assume that it is writing a log so that i changed database growth pattern and make it to 500 MB for data and log each. I have also changes parellilesam and set to 2 ( As i am usingt 4 CPU), but still our processes is not showing in runnable state.
    Do anyone help me to increase performance?
  2. ghemant Moderator

    Seems it takes longer time then it should take please ensure your disk i/o rate, hope it is not disk bottleneck issue. What kind of performance issue you are facing, are you facing timeout or deadlock!!

  3. satya Moderator

  4. ajitgadge New Member

    Hello All,
    Thanks for your suggestion. I have checked I/O related counters in perfmon and seems to be not issue.Actually Select quiries running fine on server but Insert and update takes a bit time and going into sleeping mode. Any idea why it is going in sleeping mode. I only found in perfmon that User Connection is highe i.e 272 .If user connection is making problem then how can i resolve this?.
  5. ghemant Moderator

    Their may be many reason, please check:

    * do you have clustered index on identity/incrementing datetime column on a table which has heavy transaction ratio then others
    * does this happen on particular table, what is the size of the index and table
    * do you have trigger on the table for insert
    * does this table have full-text index on it


  6. satya Moderator

    As you can see, the majority of the wait time is consumed by a wait type called WRITELOG. This is the time spent waiting for log information to be written to the transaction log file. The count ('waits' column) is equal, more or less, to the number of rows we inserted in the loop.
    The other wait, ASYNC_NETWORK_IO, is caused by the SQL Server client program dealing with commit feedback; SQL Server needs to wait for the client to accept the data. So you have to consider about writing stored procedure for frequently accessed queries to avoid running them from client side.
  7. ajitgadge New Member

  8. satya Moderator

    Don't forget to leave feedback here once you have found the offending process.
  9. ajitgadge New Member

    Sure, But currently i am tunning bad performing storde procedure and putting on live. I may get a result after some days.

Share This Page