I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where performance has become a serious concern. I started to investigate and went through the following steps.
Checked the Processor and Memory usage on the sql server (nothing is maxed out) plenty of free resources.
Index fragmentation was minimum yet rebuilt the indexes and updated statistics.
No changes has been made to the code in application (application code/sql server code) hence poor performance because of the poorly written code is unlikely to be the cause of overall poor performance of the application.
Finally got Paul Randal’s script
Wait statistics, or please tell me where it hurts. The result of the script shows that sql server has to wait a lot when writing to log file. The biggest wait type is WRITELOG.
I used the SQLIO tool to measure the disk Read/Write performance and it seems as fast as any other disk.
I know for a fact I suspect that it is more likely that there is something wrong with the log write process, but I am running out of places to look for, can someone please advise me where else I should look for issues.
What can be the possible causes of slow log writes? or any advice or pointers in the right direction are much appreciated. Thank you.
╔═══════════════╦═════════════╦═══════════╗ ║ WaitType ║ Wait_S ║ WaitCount ║ ╠═══════════════╬═════════════╬═══════════╣ ║ IO_COMPLETION ║ 4850500.20 ║ 4553514 ║ ║ WRITELOG ║ 25291893.90 ║ 795877 ║ ╚═══════════════╩═════════════╩═══════════╝