3

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    ║
╚═══════════════╩═════════════╩═══════════╝
  • 1
    Just because the drive is fast according to SQLIO (what tests did you run specifically? what were the results?) does not mean it can keep up with log writes. How many log files are on the drive? – Aaron Bertrand Feb 18 '15 at 21:35
  • There are 3 log files, other two log files are very small just under 1 GB, but the log file for this database is about 32GB , I understand it is a HUGEEEE log file for a database of almost 120GB in size but we have a nightly index rebuild job which produces a lot of logs, I also have a job in place to take tran log backups every 15 mins – M.Ali Feb 18 '15 at 21:42
  • The IO test was done by the system administrator and he used the SQLIO tool and confirmed me that disk is in healthy state and has a "good" IO. I can ask the person for more details about the IO tests he used to measure the IO performance. – M.Ali Feb 18 '15 at 21:44
  • Sorry, I have no idea what "good" means, or whether that test has any relevance at all when we're talking about multiple log files being written to at the same time. – Aaron Bertrand Feb 18 '15 at 21:46
  • Exactly "good" is not a unit for measuring the IO performance, but this is the information given to me to work with, but @AaronBertrand what is your initial assessment about these wait stats, the bottle neck is around the log file isn’t it? – M.Ali Feb 18 '15 at 21:49
1

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.

  • @AaronBertrand do you think 20 are the right number of VLFs in a 36GB log file? from 1600 down to 20 seems a lot less VLFs I am a bit concerned if it is a very small number maybe a bit more VLFs will have a better impact on performance? – M.Ali Feb 19 '15 at 18:46
  • 2
    You started off on the right path, but you probably shouldn't have grown the log file back to 36gb all at once. There is good advice here about how to deal with VLFs. Re-growing in more reasonable chunks will give you more proper VLFs. – Erik Darling Feb 19 '15 at 19:35
  • @sqldriver thank you for your advice but the problem is I have a nightly index rebuild job which bumps up the log file size to 36GB if I reduce the log file size it will grow back to 36GB with a lot more VLFs :S , unless I increase the tran log backup job frequencey while the indexes are being rebuilt. – M.Ali Feb 19 '15 at 19:43
  • 2
    I didn't say not to re-grow it, or not to re-grow it to 36gb. I suggested that you re-grow it to 36gb in smaller increments. Read about the log growth/VLF algorithm in the article I linked you to for clarification. – Erik Darling Feb 19 '15 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.