Hello, 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?
HI, 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!!
As suggested by Hemant better to check the disk contention in this case and also what is the service pack level on SQL: http://support.microsoft.com/kb/897284 fyi
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?.
Hi, 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 Regards
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.
Sure, But currently i am tunning bad performing storde procedure and putting on live. I may get a result after some days.
