October 24, 2007 at 9:58 am
We just got in a brand new HP server for our production OLTP database. It'll be W2k3, running SS2k5 SP2. It has 8gb ram, and 14 SAS drives (146gb ea):
- 2 drives RAID 1 on Controller 1 for the OS
- 2 drives RAID 1 on Controller 1 for the production log file
- 10 drives RAID 10 on Controller 2 for the databases
I'm looking at it and thinking, man, it'd be nice to move tempdb off the database RAID. But where? Definitely not the Log RAID, I want to maintain the sequential nature of that one. OS RAID? That screams caution to me. Have bad feelings about it. Should I just leave it on the db RAID?
..jeremy.
October 24, 2007 at 11:35 am
I would do some searching in this forum for performance tuning for more details. I personally and so do many others prefer to stick all the items on seperate drives if you can. If you cannot then data should be sepereate from the logs if you use full recovery model, if you dont then keep them seperate from the indexes. And tempdb should always be on a seperate drive when possible because it writes more than you would think. But, tempdb should never be on an OS drive, or a single drive unless its a standalone and used for nothign else if you dont have the $ to make it at least a raid 0, or 1.
October 24, 2007 at 12:03 pm
In a perfect world on a 15k drive formatted at 8K block size NTFS on a RAID 1. Given your hardware (above) and assuming you have the space to accomodate it, I would put your TempDB on the same volume that your logs are on. Format the volume at 8K even though logs write in 4K (just uses more space). You don't have this option on the OS volume (keep in mind, NTFS defaults to 4k block size).
Tommy
Follow @sqlscribeOctober 26, 2007 at 12:57 pm
While I'd love to put tempdb on its own RAID, I have to settle for sharing, at least for now. And I really don't like the idea of sharing with the log file. I want to keep that raid a purely sequential write. Putting tempdb in with it would destroy that.
Microsoft recommends 64k here. Any specific reason to not adhere to this best practice?
October 26, 2007 at 1:22 pm
In your case:
If there is only ONE .ldf on the log drive, I'll put tempdb log AND data file on the data drive.
If there is more than one .ldf on the log drive then you've already lost most of the benefit of the sequential nature of log files, in which case I'll put tempdb mdf on the data drive and templog on log drive.
October 26, 2007 at 1:34 pm
If you have the space, go for 64K. Most people don't 🙂
Tommy
Follow @sqlscribeOctober 26, 2007 at 2:25 pm
russell (10/26/2007)
In your case:If there is only ONE .ldf on the log drive, I'll put tempdb log AND data file on the data drive.
If there is more than one .ldf on the log drive then you've already lost most of the benefit of the sequential nature of log files, in which case I'll put tempdb mdf on the data drive and templog on log drive.
Yes, there will be only one .ldf. This server has the luxury of serving only one production database. 🙂
TommyB (10/26/2007)
If you have the space, go for 64K. Most people don't 🙂
😉
Ok, let me 'rephrase': Has 8k been found to be a good compromise between size and performance in most cases?
While we're on the topic, that article mentions Queue Depth on the HBA. Is this something I really need to look at, or is it worth the time and effort to investigate?
October 26, 2007 at 3:10 pm
SQL writes in 8K data pages thus 8K should be sufficent. As far as queue depth is concerned, most HBA drivers default to 32 per LUN, and 256 per port. Storage ports support either 256 or 512 queues per physical port. You'll have to check w/ your vendor fo the specifics. The idea is to use as many queues as you can without creating "queue full" conditions. You can change a driver's queue settings via the SanSurfer (assuming your using an Emulex HBA) and try increasing the depth per LUN to 64 or 128, and see what happens. Bottom line, the default is sufficent for most. I would toy with this in dev first 🙂
Tommy
Follow @sqlscribeNovember 8, 2007 at 9:58 am
Just wanted to say thanks for all the input! I settled on putting tempdb in with the mdfs on the RAID 10. The single production log will remain on it's own RAID 1 as planned, and I settled for 8kb block size on both.
As for Queue Depth, after three support calls no one at HP could tell me how to set it. Seems they're not sure what HBA they gave me. Lovely..
..jeremy.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply