Dear Everyone
I have a custom build application running on .Net and its trying to access the company's SQL Server and the users are complaining that when they try to access its becoming slower than before.
I found some defragmentation and rebuilt the main indexes but i ran Randal's wait query and found the results below.
The main wait is:
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
WRITELOG 4987.11 4076.76 910.35 6773580 39.34 0.0007 0.0006 0.0001 https://www.sqlskills.com/help/waits/WRITELOG
Any advice would be helpful
have a good day
Kal
You still need to do some more digging. It can be anything and slowness when "accessing the database" and checking the waits isn't going to tell you much.
The average wait time really isn't much - it's more the percentage of the waits. Those are hard to say as you would want a baseline to compare to. You can do a capture and then 24 hours later do another to get the waits for that 24 hour period. That would tell you more about the waits. The sqlskills site has an example of capturing for a time frame which you can modify to meet your needs.
Capturing IO latencies for a period of time
If the slowness is just when they first access the database (users typically won't know when they are accessing just the application or accessing the database), you may want to consider looking at what is being executing from the application at the beginning of that process and look into tuning those queries/stored procedures, check the query stats, etc
You may still want to check sys.dm_io_virtual_file_stats to see if a particular file is being hit hard or indicates a lot of stalls.
You can also run an extended events session or server side trace to capture a login process from the application and see what is taking the most time.
I usually check statistics first instead of fragmentation - it often seems to be an issue when user report things becoming slower over time.
Sue
Hi Sue
I tested to run Randals script and it will keep executing for the time specified in the WAITFOR DELAY '00:30:00';
That will make the server slower or am i mistaken especially if we keep it for 24 hours.
As for the statistics comment you mentioned at the end, do i refresh the statistics for that specific database? Is that all were to do?
Kal