SQL Server 2008 R2 – Resolving Biggest Wait WriteLog Performance Issue

performancesql serversql-server-2008-r2

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.

  1. Checked the Processor and Memory usage on the sql server (nothing is maxed out) plenty of free resources.

  2. Index fragmentation was minimum yet rebuilt the indexes and updated statistics.

  3. 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.

  4. 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    ║
╚═══════════════╩═════════════╩═══════════╝

Best Answer

After doing a lot more investigation this is what I have found.

It fixed the issue (significant performance gain and WRITELOG has an average wait time of 0.0126 which was initially 14.681)

Apparently the issue was with the Number of Virtual Log files in my physical log file.

There is a job scheduled to rebuild indexes every night, the job creates 36GB of logs, and until few weeks ago someone had add a job to shrink log file on weekly basis. Log file was being shrunk to 500MB.

Since it is a very busy server the log file would grow in size and it was set to auto grow by 3 percent. Each time it grew it added more and more VLFs. As a result my 35.5GB log file had 1600 VLFs.

To resolve the issue I did the following: 

-- To get the number of VLFs in a physical Log file.
DECLARE @count INT, @DBname SYSNAME;
------------------------------------------------------------
SET @DBname = N'TEST_DB'
------------------------------------------------------------

DBCC LOGINFO(@DBname)
     SET @count = @@ROWCOUNT;

SELECT @DBname AS DatabaseName , @count Total_VLFs;
GO

The above query returned the databasename and number of VLFs (1600)

To reduce the number of VLFs this is what I did:

USE TEST_DB;
GO

DBCC SHRINKFILE (TEST_DB_log, 0, TRUNCATEONLY)
GO

DBCC SHRINKFILE (TEST_DB_log  , 0)
GO

ALTER DATABASE TEST_DB
  MODIFY FILE ( NAME       = TEST_DB_log
              , SIZE       = 36864MB
              , FILEGROWTH = 4096MB)
GO

This reduced the number of VLFs from 1600 to 20. The end-users has seen a sudden improvement in the application performance.

Now one thing I am not sure about is, if 20 VLFs is the right number of VLFs for a 36GB log file. But it seems to have a good impact on the performance of the database.

Related Question