Performance Best practice - Transaction log must on a different drive.

added by jacobsebastian

9/12/2011 9:06:16 AM

193 Views

It is a well-known recommendation and best practice that the transaction log of any database must be on a drive different than the data files are on. This is especially useful to improve transaction log file performance, which manifests itself as a high...

Read the full story

3 comments

dpeterson

9/12/2011 9:07:59 AM

It's also a good idea to take this concept even further if the database is under high load and create multiple log files, one per cpu allocated to sql server, and spread them across multiple drives. So if you have 4 cpu cores available, and all are assigned to sql server, creating 4 transaction log files across 4 drives will give you the highest possible concurrency rate for transactions.

vijayst

9/12/2011 10:55:29 AM

I am not sure if creating a transaction log for each available physical drive is a best practice. Does SQL Server know that these transaction logs can be written concurrently?

dpeterson

9/12/2011 11:03:11 AM

I'm sorry, I actually misspoke. Multiple data files are recommended, not transaction logs. As data files are random access files, having them split amongst multiple drives (and having a data file for each cpu core) improves concurrent access to the database (SQL Server will automatically split working with the files amongst the available cpu cores if possible).
Transaction log files are sequential access only, so multiple files will not improve performance.
Sorry about the confusion on my part, it's Monday ;-)