January 30, 2006 at 1:11 pm
Anyone how of such a paper?
Here are the ideas I have so far. Please comment.
>>
We are looking to use 2 Opteron CPUs,
64-bit edition of Windows 2003
8GB (min) of RAM
drive C: mirrored (for OS and page file)
drive D: (RAID5 for data)
drive E: mirrored for trans logs
>>
TIA,
Bill
January 31, 2006 at 2:57 am
If you wil be running big queries where result srts and interim result sets will spill into tempdb then I wouldf look to put tempdb onto a seperate RAID 5 array.
January 31, 2006 at 3:23 am
You should not place more than one transaction log on each drive. So if you will be having more than one database in use you will need more than drive E: for logs. I agree with the above comment on tempdb, and if you will be using SQL Server 2005 (and some of the new features, especially snapshot isolation) this is even more true.
January 31, 2006 at 4:16 am
Chris, I think this is the first time I have heard this best practice. Are you referring to stand alone drives? On our datawarehouse server, we have all of our logs on a mirrored pair & don't seem to be having any performance hits as a result of it. Just curious where you came up with this?
John
January 31, 2006 at 4:27 am
The reason why it is recommended to put the log on a separate drive is that all the I/O on the file is sequential. Therefore you get increased performance since the disk head will always be in the correct position to write. If you place the data file(s) and log file on the same drive the disk head will need to move around for the random I/O's of the data file(s) and will not be in the correct place when the log shall be written. Placing several log files on a separate drive of course has the same effect, when log A is written the head is moved in position for that, and then when log B shall be written it will not be in correct position and will need to move again.
Just because you are not having performance problems does not mean that you have optimum configuration.
January 31, 2006 at 4:38 am
I am pretty sure I didn't say that I did have the optimum configuration. I just said that I believe this is the first time I have read that this is a best practice & that we haven't had any performance issues with our current configuration.
Would this hold true if you had the logs on a RAID 10 array? Which I believe is a best practice that i have read.
I understand how logs files are written, I am just curious how many people follow this best practice. You could eat a lot of disks up when you have several databases on a single server.
John
January 31, 2006 at 5:06 am
Sorry if I expressed myself badly, I did not mean you said you had optimium configuration. It is just that a lot of people have heard something about moving log files from data files but do not understand the reason to do so.
What is a best practice can be discussed. Should a best practice avoid the common pitfalls or should it provide the optimal result? RAID 10 is of course better but whether or not it is worth the cost needs to be decided in each specific situation.
For me I say that log files should not be on RAID 5 and if you need to achieve maximum performance for a specific database you should place the log file of that database on a separate drive where there is nothing else, including other log files (at least nothing that is in use).
January 31, 2006 at 5:21 am
I would agree with you that a best practice should provide the optimal result.
In our data warehouse environment, we have our main data warehouse database & a few other supporting databases. The data warehouse database is the most heavily used while the other databases have maybe half the workload.
Is there a threshold that a database must cross before you say that it should have its own mirrored pair for its logs? Or do you just plan on separarting the logs on different drives no matter the demand for the database?
John
February 1, 2006 at 5:17 am
Well, like I said it is just a matter of considering the cost versus the value. The best practice is to have the log file for a database on a separate drive with no other files incl. other log files there. It does not matter if the database is small or large or what the number of users is. It is always the best way.
Placing all the log files on a drive together might be better than having them with the data files, but it will not give the effect that this best practice aims for.
March 14, 2006 at 6:10 am
there are also a few things I recommend
- creating X datafiles for tempdb, X being the number of processors available
- one single logfile for each database, on different drives (write is sequential)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply