March 22, 2009 at 3:19 pm
Hi everyone,
My past experience was to always put the database and log files on different partitions. I have someone who is running the logs and data on the same partition in an EMC SAN environment. The are implementing a MS Dynamics Ax system and performance is a bit sluggish, I have made some minor adjustments, but the logs on the same partition is still making me wonder.
Should thee be split on another partition even tho its on the same physical array and if so why? this is the argument they are sticking with as to not split them and I would like concrete proof why they should be moved.
Thanks,
G
March 22, 2009 at 5:10 pm
If your SAN is properly configured it will mask a lot of the issues of having the data and logs co-located.
However, the way the two types of files are accessed is very different. A data file is accessed very randomly, with reads and writes occurring at different "points" in the file. Conversely, a log file is accessed almost purely for sequential writes; new data is added at the "end" of the current position and is deleted from the "start" of the sequence. If they're on different LUNs then the two LUNs can be optimised for the different type of access.
The other potential argument is in the event of drive corruption. If they're on different drives then:
- if the data drive corrupts then a tail backup of the log should still be possible, allowing all data to be recovered with the backups already existing (there are backups being taken, right:-)?).
- if the log drive corrupts then the only data loss should be the data written since the last checkpoint.
Having said that I haven't seen a drive corruption for several years now except where there was some manual activity being performed that shouldn't have been.
March 22, 2009 at 5:36 pm
craigl5678 (3/22/2009)
Hi everyone,My past experience was to always put the database and log files on different partitions. ...
Should thee be split on another partition even tho its on the same physical array and if so why? this is the argument they are sticking with as to not split them and I would like concrete proof why they should be moved.
They should NOT be moved to different partitions on the same physical device. This makes performance markedly WORSE and not better.
In fact, I strongly advise that you not use partitions at all in a SQL Server, they are a real worst practice.
What you want to do is to move them to separate physical devices.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply