July 25, 2006 at 2:10 pm
We are preparing to install sql2005 OLTP on new server. I was wondering where to put both tempdb files on one partition or split the log to the log drive. The tempdb is heavily used and can reach 4 GB.
partitions
RAID1 OS/apps
RAID1 logs
RAID1 tempdb
RAID10 data
RAID0 Backups
I assume it would be best to move the tempdb log to the log drive and so the tempdb data and log are not on same drive?
Thanks in advance
July 25, 2006 at 2:39 pm
I would not go as far as that. Make sure the recovery Model is Simple for TempDB. There are some other recommendations by Microsoft books:
- You may want to put TempDB on RAID 0 for better performance. TempDB is re-created each time SQL Server is restarted.
- They recommend to put this RAID 0 on its own disk controller.
Regards,Yelena Varsha
July 26, 2006 at 10:13 am
The performance of your server will be greatly affected by the performance of tempdb, so putting it on separate drives (and a separate controller if you have the luxury) is a good idea. RAID0 is often recommended for tempdb for maximum performance at the expense of redundancy because there is no permanent data in tempdb, however if any of the tempdb drives fail it will bring down your server. When you restart the server all databases should recover and rollback/roll forward to a consistent state, but if you're trying to achieve 24/7 OLTP operation you may want to reconsider.
All log files have different I/O characteristics than data files...different block sizes, cache latency, sequential vs random. This is why log files (including tempdb log) should not be on the same physical drives as data files. If you have another drive for tempdb log that's great, otherwise I would suggest putting it on the same drive with the other logs rather than on the tempdb data drive. (Assuming it fits.)
PS: You can't change the recovery mode of tempdb. Both SQL 2000 and 2005 will give you the error "Option 'RECOVERY' cannot be set in database 'TEMPDB'."
July 26, 2006 at 11:19 am
Thanks for both the comments.
Scott I agree with your comments on problems with RAID0 and will stay with RAID1 or RAID10 for now. I will move tempdb log to the log drive to group similar operations on different drives.
July 26, 2006 at 3:34 pm
maybe you want to split it into two files ( or more files) of the equal size to reduce the allocation resource contention for tempdb that is experiencing heavy usage....And might want to load balance it...
July 27, 2006 at 7:06 am
There are a couple of points on seperating tempdb which I find are often overlooked .. this is only really relevent where tempdb is very heavily used.
First if you don't allocate enough spindles to tempdb you actually impose a bottleneck on your server. Lets say your mdf files are on 6 available spindles - moving tempdb to a seperate array with less than 6 spindles could actually slow things down - the available i/o is relative to the number of spindles. And of course don't use raid 5. With regard to the raid 0 - yeah ok but .. you could lose transactional data bound up within tempdb, and as mentioned if a disk fails your server goes down.
Second, Yes moving the tran log from tempdb to a dedicated raid 1 ( nothing else on it ) might also give performance boosts .. it really depends upon the usage of tempdb how far you split it out.
Third if you have a fixed number of available spindles for your server then placing tempdb and even filegroups on seperate arrays may actually degrade performance ( it's easier to show this on a whiteboard ) so be careful.
Finally on a large multiproc box creating one file per physical processor/core may prove beneficial , note this is files NOT filegroups. Sizing tempdb to avoid auto growth will give performance benefits - autogrow can be a real performance bottleneck.
As always when taking about seperate drives, these should be dedicated physical disks, carving a number of disks into drives to achieve this setup will not give a performance boost. If you can arrange each array on a seperate channel / card and balance the controller cards across the server buses .. this will all help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 2, 2006 at 1:38 am
Colin's points are very pertinent, especially concerning the number of spindles available. We have seen major differences in performance, by having the luxury of being able to test multiple subsystem senarios with the SQLIO test tool. This helped us to find the most optimal setup for the subsystem, based on I/O throughput characteristics we are expecting for our databases - and this was mostly down to the number of spindles for each array and the channels we could separate our potential 'hot-spots' out to.
Also, though, if you are to see the benefits of 'sequential' I/Os for the log files, then the log files need to reside on their own arrays (with their own dedicated spindle-sets, i.e. not partitioned or shared in any way). As soon as you put multiple log files on the same array the characteristics will revert back to 'random' I/Os. So, if tempdb is likely to be the main bottlenect for your system, and you have extra disks available, then putting the log file on its own array may help, as you will be nearly doubling the I/O throughput by having it 'sequential.'
Hope this helps
Ian (aka SQLBod)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply