SQL Server Performance Forum – Threads Archive

Simple Recovery – separate transaction logs?

I found this topic which is pretty much the question I am asking about, but I wanted more info on it:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2235&SearchTerms=simple,recovery Basically, I have a database that is in simple recovery mode. We’re upgrading our server soon and were trying to figure out how many backplanes to purchase in order to maximize performance. My thought was that you really didn’t need to put the transaction log on its own array/backplane if it’s not really being used (because of the simple recovery model). However, the above-mentioned thread had two people recommending that the transaction logs be put onto a separate RAID array, but they didn’t really say why it would be better, performance-wise. Can anyone give me a quick summary of this?

just separate disks is probably sufficient,
especially for transaction processsing apps
logs will get activity with any write ops,
regardless of recovery model if your app will do intensive table scans, then a separate backplane is advisable of course, you should still have adequate # of adapters and backplanes for data

As joechang mentioned, ALL transactions in a WAL system such as SQL Server have to written to the transaction log(s). Yes, in simple recovery mode they are only held in the log until the checkpoint process occurs (and they are truncated from the log file if truncate log on checkpoint is enabled), but if the logs are placed on a data path shared with other data files, you’ll sustain higher log flush wait times thus resulting in lessened overall database performance. Basically, the reasoning is: if you put the transaction logs on the same backplace as the data, you’ve got more processes waiting on the same disk (all queued up, backed up, waiting on disk time). FWIW, I always put the transaction logs (if humanly possible) on their own controller/backplane.

]]>