June 17, 2008 at 3:52 pm
What are you opinion on this from a performance and best practice point of view, good? bad? no difference?
June 17, 2008 at 4:24 pm
Moving tempdb away from the sql binaries drive is always the good thing and must be done in any busy environment.
Also depending on how busy your server is, if your server has N number of processors then create N tempdb data files and place them on different drives(if possible)
June 18, 2008 at 8:15 am
assuming you have the arrays available 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 18, 2008 at 8:34 am
I'm not sure the SQL binaries come into play. It's separating the various spindles used in databases that give you performance boosts.
If tempdb is heavily used, then this can help if they are physically separate. There are times I've seen people produce two "drives", LUNS, arrays, whatever from the same physical set of drives. That doesn't help.
If you have a busy server, I'd look to separate out logs and tempdb from the data files. Which one I'd do first depends on which one is used more heavily. You can look in Perfmon to see the count of reads/writes and how often they occur and in SS2K5, use some DMVs to see if you have stalls or waits with different files. I might look to move those to separate drives.
June 18, 2008 at 8:51 am
Best practice is to put tempdb on a separate set of spindles from other data files if you can. If you can't then consider spreading it across multiple RAID 1 drives or a single file on a RAID 1+0 drive. Microsoft's recommendation is 1 data file per CPU. Also make sure to size out the data files ahead of time so you don't have to deal with growth issues.
We run mostly Dell PowerEdge 2850\2950 servers for SQL where I work. I create three RAID 1 drives; drive 1 has two partitions - 1 for OS\SQL binaries and 1 for transaction log files. Drives 2 and 3 get 1 tempdb file each. For machines without a drive array I stick the data files for my databases on drives 2 and 3 alongside tempdb's files. For machines with drive arrays attached I put all other data files on the array and only put other data on drives 2 and 3 when I have some metrics which show they've got capacity to handle more IOs.
Kendal Van Dyke
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply