6 Kicks

Performance Best practice - Transaction log must on a different drive.(beyondrelational.com)

submitted by jacobsebastianjacobsebastian(3639) 8 months, 14 days ago

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

3 comments |category: |Views: 31

tags: another

new Add a live kick counter to your blog >> liveImage

You can even customize the image by choosing your own colors, and then clicking the button below to update the preview and the html code:

  • "Kick It" text
  • "Kick It" background
  • kick count text
  • kick count background
  • border

Simply copy and paste this HTML into your blog post.


Users who kicked this story:
BILE_GRAN_CORREABILE_GRAN_CORREA(0) - Bile0Gran0Vieira0CorreaBile0Gran0Vieira0Correa(0) - vijaystvijayst(1311) - bsenoffbsenoff(1583)
More who kicked this story...
Comments:

posted by dpetersondpeterson(4397) 8 months, 13 days ago 0

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.

Reply

posted by vijaystvijayst(1311) 8 months, 13 days ago 0

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?

Reply

posted by dpetersondpeterson(4397) replied to vijaystvijayst(1311), 8 months, 13 days ago 0

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 ;-)

Reply

information Login or create an account to comment on this story
Submit a Story
View upcoming stories
How else can I help?
Add our feeds to your site

Most Active Users

What's this?