Optimizing You Log File

Optimizing your transaction log could easily fill a 50 page white paper, or a week's consultancy, but here are a few points to get you started:

How many physical log files?
This one is easy...One! Exactly one, always one, the answer is one! There is an urban myth that having multiple log files will improve performance, but this is just a myth. Log files are written sequentially, so if you have multiple files, it fills one, then fills the next one, etc. Pointless!

How many virtual log files?
This one is a bit more complicated. Your physical log file is managed internally with a series of virtual log files. See this post for more details. If you have too few, then the active portion of your log may be too large, leading to longer backup times. If you have too many VLFs however, this leads to "log fragmentation" and cause performance issues.

The general rule of thumb is to have VLFs sized at around 500MB. To achieve this, grow your log in 8000MB chunks. If you already have log fragmentation, the way to resolve it, is to shrink your log file (thus deleting the existing VLFs) and then grow it again, using the desired chunk size.

I/O Subsystem
This breaks down into two parts. Firstly, make sure that you put the log file on a separate physical disc to your data files. Otherwise you will get I/O contention.

The second part is a little more complex, and that is what RAID level should you use? Well, this depends on what features of SQL you are using. Before we begin, it may be beneficial to bring some context, by looking at the standard RAID levels, and how many IOs they generate.

RAID 1 - IOs = R + (W*2) / 2
RAID 5 - IOs = R + (W*4) / No Of Discs
RAID 10 - IOs = R + (W*2) / No Of Discs

If you are just using SQL Server at a basic level, then RAID 1 is a good choice. This is because almost all activity on the log will be write activity. The only read activity will be backups and restores. In this scenario, RAID 1 is better than RAID 5 because for every one write, there will be two physical writes to the volume with RAID 1, as opposed to 4 writes with RAID 5. It is also better than RAID 10 in this scenario, because RAID 1 will perform the operations as sequential IO, where as RAID 10 will perform them as random IO.

However, this recommendation changes if you are using some of the more advanced feature of SQL, especially the HA features. If you are using Database Mirroring, Replication or Log Shipping, then you are liable to have a much higher read to write ratio, and hence need better read performance, whilst maintaining write performance. In this scenario, RAID 5 is still a no-go, because you will double the number of physical writes for every write operation, from 2 to 4, as discussed above, so your best choice would be RAID 10. RAID 10 will maintain the 2 writes per write operation ratio, but also give you improved (and more scalable) read performance. This is because the the overall IOs will be divided by the number of discs in both RAID 1 & RAID 10, but in RAID 1, you always have exactly 2 discs, where as with RAID 10, you will have a minimum of 4, and as many as you require.

Log Manager Bottlenecks
One reason that your log can become a bottleneck, is because in SQL Server 2008, the Log Manager will only allow you to have 3840K of data that has been sent to be written, but where no acknowledgement of the write has been received back. Before SQL 2008, this limit was even lower.

Monitoring Log Performance
Within SQL, keep an on the sys.dm_os_wait_stats dmv. If you see lots of WRITELOG waits, then the log is probably coming under pressure.

In Perfmon, you should be monitoring the obvious disc counters for you log drive, such as Avg Disk Queue Length, Disk Writes/Sec, etc, but you should also keep track of the Log specific SQL counters. In particular, pay attention to Log Flushes/Sec and Log Flush Wait Time.

Together, these counters should give you an all-round idea of how your log is performing. From a performance aspect, the Log tends to be the forgotten relative, but be careful, as forgotten relatives have a nasty habit of turning up unexpectedly, and making a nuisance of themselves!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom