June 18, 2009 at 5:19 pm
I have a OLTP database thats around 300 GB and is fairly busy. I need to install it on a server that only has 2 drives, a drive C that is mirrored at 68 GB and a drive D that is RAID 5 at 800 GB. The drive configuration is cast is stone and cannot be changed.
My question is where is the best place to put the TEMPDB data files, the TEMPDB log file and the OLTP log file?
June 18, 2009 at 6:16 pm
With this being a highly busy OLTP configuration and drive C should be for system files - safest bet is Drive D for all files.
Better option would be to purchase an external raid controller, get an additional disk array and put the tempdb data and logs there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2009 at 9:02 pm
Tim (6/18/2009)
I have a OLTP database thats around 300 GB and is fairly busy. I need to install it on a server that only has 2 drives, a drive C that is mirrored at 68 GB and a drive D that is RAID 5 at 800 GB. The drive configuration is cast is stone and cannot be changed.My question is where is the best place to put the TEMPDB data files, the TEMPDB log file and the OLTP log file?
You do not have a lot of choices.... so, giving the case, put everything on the RAID5 and I'll explain you why. Write performance on a RAID1 is horrible! An OLTP database uses Tlog a lots...and Logs writes sequentially so your performance will be affected. RAID5 is not very fast but provides a good compromise between reads and writes, RAID1+0 is better. JUst be sure you will run Tape Backups and install SQL binaries on "C" drive.
June 18, 2009 at 9:25 pm
as per MS best practices :
-> put data files on a separate physical Raid 1+0 drive
-> Put Log files on a separate physical Raid 1+0 drive
-> Put tempdb files on separate physical Raid 1+0 drives.Since your system is fairly busy initially start off with distributing tempdb files on the physical disks equal to your CPUs . so if I have 4 CPUs I will create 4 tempdb files on 4 different physical disks with raid 1+0.All the files should be if equal size so that they follow the proportional fill algorithm.
One shoudl never place the data file , be it of any kind on the C drive .Jst keep it for binaries .
So in your case you have no good choice and for this you have been already given the choices above.
You should convince your management for a better configuration .Else they will be facing the results ..
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 18, 2009 at 9:45 pm
hi_abhay78 (6/18/2009)
as per MS best practices :-> put data files on a separate physical Raid 1+0 drive
-> Put Log files on a separate physical Raid 1+0 drive
-> Put tempdb files on separate physical Raid 1+0 drives.Since your system is fairly busy initially start off with distributing tempdb files on the physical disks equal to your CPUs . so if I have 4 CPUs I will create 4 tempdb files on 4 different physical disks with raid 1+0.All the files should be if equal size so that they follow the proportional fill algorithm.
One shoudl never place the data file , be it of any kind on the C drive .Jst keep it for binaries .
So in your case you have no good choice and for this you have been already given the choices above.
You should convince your management for a better configuration .Else they will be facing the results ..
Regards
Abhay
IT is true that data and log should be on different RAID volumes, but please do not believe everything Microsoft says about best practices, in real life, that's not true all the time. For instance, do not wast space and hardware for "tempdb" using a persistent store; tempdb is flushed every time you restart your instance. You can safely put that on same database drive and better if that one is RAID5; if the drives where tempdb resides becomes unusable, the entire instance goes down. Using RAID5 you'll gain fault tolerance plus "ok" performance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply