February 28, 2011 at 7:35 am
Hi all,
I have a 1T, high I/O database in a SAN environment on which I have planned the database files this way:
L: logfile (8k)
M: mdf file (64k)
N: ndf files (64k)
X: index filegroups (64k)
They are all in the same LUN. Of course I want each disk in a seperate LUN for best performance, but that's not possible at the moment. Now I'm planning to move the tempdb, which still resides on C:, to another location and that's where I need your help. To make it easy I created two options:
1) Ask for a new disk T: with a clustersize of 64k on which I move both the mdf and ldf;
2) Ask for two new disks T: (64k) and U: (8k) on which I move the tempdb mdf and ldf respectively.
With which option should I go? To me, option 2 seems obvious. But maybe it does not really matter for tempdb files to be on seperate disks like the databasefiles. Hopefully you guys can give me the answer. Thanks in advance!
Regards,
stakes
February 28, 2011 at 7:48 am
Stakes, I just asked some questions about files placement on a SAN in this post
here. It might be useful...or not.
I typically put temp log in with the other log files and drop tempdb(s) on a separate lun. If it tempdb is expected to get hammered then you might want to go with option 2.
If the disk groups in you SAN are dedicated, you can also adjust the queue depth to 64 or 128 on the HBA. I believe the default is 32. If the disk groups are shared disks, you may not want to go higher than 64, as this could affect performance for other servers using the same disk groups.
chris
February 28, 2011 at 10:09 am
Hello,
I'd start by monitoring disk I/O of the drive that holds TEMPDB files now to see if you get heavy usage to your TEMPDB files. We have some high usage OLTP systems and there's often surprisingly little going on the TEMPDB disks, especially from 2005 and onwards (thanks to improved caching and other improvements).
Personally I tend to go with dedicated disk and LUN for TEMPDB, then place both data- and log files on it. In SQL 2005 and newer there's some improvements made to TEMPDB that reduce the logging overhead and I haven't had any problems with data- and log files sharing the same disk.
March 1, 2011 at 2:58 am
Thanks for the replies! I will go with option 1: put the tempdb logfile and the logfile on a seperate disk. I can always move the logfile to another disk when needed.
To Chris: I've read your post last week. There are more replies now and it surely is helpful. Besides the fact that it is a good practice to seperate the tempdb, I also want to do rebuilds of indexes in the tempdb when needed. So it will get hammered someday. I've also talked to the SAN guy and unfortunately, he doesn't want to change the queue depth.
To Mika: In the near future it will be possible to split the disks to different LUN's, but not now. Looking forward to the future 🙂
stakes
December 23, 2012 at 11:46 pm
no post just want to return to article
December 23, 2012 at 11:48 pm
What tools are you using to monitor the tempDB, perfmon, trace, DMVs and what perf objects are looking at to determine is you should separate logs from data files?
December 24, 2012 at 9:31 am
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).
If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 24, 2012 at 12:21 pm
ScottPletcher (12/24/2012)
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
Actually, I'd put the mdf files on RAID 10 disks as well. I don't have the links readily available, but RAID 5 isn't the best for high transaction databases.
December 24, 2012 at 12:52 pm
Actually, just found one reference here: http://www.sqlservercentral.com/articles/RAID/88945/.
December 25, 2012 at 8:39 am
Unless your LUN has LOTS of spindles underlying it, I would avoid carving it up into too many different separate sections just to "isolate" activity. The more "disks" you provision on a 4 disk RAID 5 (or 10) for example, the more head movement you will incur because they blocks for each disk are physically separated by a distance that require further head movement on the disks than you would have if you simply had ONE large drive that you dropped everything onto. This is a VERY COMMON mistake by DBAs/SAN admins.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 25, 2012 at 1:38 pm
Lynn Pettis (12/24/2012)
ScottPletcher (12/24/2012)
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
Actually, I'd put the mdf files on RAID 10 disks as well. I don't have the links readily available, but RAID 5 isn't the best for high transaction databases.
No; for reads, with the same number of total drives, RAID5 should be faster, as more spindles are doing the actual reading.
Writes definitely are an issue with RAID5, but you may be able to adjust for that by:
altering the cache% for write vs read (depends on the controller);
putting more historical / mostly read-only data on RAID5;
etc..
If you have enough extra money, definitely buy many additional drives and use all RAID10. Or even SSDs where you can.
But don't automatically dismiss RAID5 without any thought.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 25, 2012 at 1:42 pm
ScottPletcher (12/25/2012)
Lynn Pettis (12/24/2012)
ScottPletcher (12/24/2012)
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
Actually, I'd put the mdf files on RAID 10 disks as well. I don't have the links readily available, but RAID 5 isn't the best for high transaction databases.
No; for reads, with the same number of total drives, RAID5 should be faster, as more spindles are doing the actual reading.
Writes definitely are an issue with RAID5, but you may be able to adjust for that by:
altering the cache% for write vs read (depends on the controller);
putting more historical / mostly read-only data on RAID5;
etc..
If you have enough extra money, definitely buy many additional drives and use all RAID10. Or even SSDs where you can.
But don't automatically dismiss RAID5 without any thought.
Oh, I wouldn't. I would consider what I can afford to purchase. If I am on a tight budget, RAID 5 maybe the better choice, but not when you are looking at performace for a highly trasactional OLTP system and you can afford to buy the disks for a RAID 10 setup.
December 25, 2012 at 1:47 pm
Lynn Pettis (12/25/2012)
ScottPletcher (12/25/2012)
Lynn Pettis (12/24/2012)
ScottPletcher (12/24/2012)
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
Actually, I'd put the mdf files on RAID 10 disks as well. I don't have the links readily available, but RAID 5 isn't the best for high transaction databases.
No; for reads, with the same number of total drives, RAID5 should be faster, as more spindles are doing the actual reading.
Writes definitely are an issue with RAID5, but you may be able to adjust for that by:
altering the cache% for write vs read (depends on the controller);
putting more historical / mostly read-only data on RAID5;
etc..
If you have enough extra money, definitely buy many additional drives and use all RAID10. Or even SSDs where you can.
But don't automatically dismiss RAID5 without any thought.
Oh, I wouldn't. I would consider what I can afford to purchase. If I am on a tight budget, RAID 5 maybe the better choice, but not when you are looking at performace for a highly trasactional OLTP system and you can afford to buy the disks for a RAID 10 setup.
Again, that's still not 100% always the case. RAID5 can indeed perform better for the same amount of money, since it will always have more spindles available than RAID10.
It's especially worth considering if you partition data so that historical (read-only) data is separate from updated data. And depending on the volume of modification activity you expect and the other factors stated.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 25, 2012 at 2:35 pm
ScottPletcher (12/25/2012)
Lynn Pettis (12/25/2012)
ScottPletcher (12/25/2012)
Lynn Pettis (12/24/2012)
ScottPletcher (12/24/2012)
You also need to verify that the log LUNs are RAID10 and the data LUNs are RAID5 (typically that is the best performance).If you're going to put both tempdb mdf files and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
Actually, I'd put the mdf files on RAID 10 disks as well. I don't have the links readily available, but RAID 5 isn't the best for high transaction databases.
No; for reads, with the same number of total drives, RAID5 should be faster, as more spindles are doing the actual reading.
Writes definitely are an issue with RAID5, but you may be able to adjust for that by:
altering the cache% for write vs read (depends on the controller);
putting more historical / mostly read-only data on RAID5;
etc..
If you have enough extra money, definitely buy many additional drives and use all RAID10. Or even SSDs where you can.
But don't automatically dismiss RAID5 without any thought.
Oh, I wouldn't. I would consider what I can afford to purchase. If I am on a tight budget, RAID 5 maybe the better choice, but not when you are looking at performace for a highly trasactional OLTP system and you can afford to buy the disks for a RAID 10 setup.
Again, that's still not 100% always the case. RAID5 can indeed perform better for the same amount of money, since it will always have more spindles available than RAID10.
It's especially worth considering if you partition data so that historical (read-only) data is separate from updated data. And depending on the volume of modification activity you expect and the other factors stated.
Okay, so what you want to hear from me is "I'm sorry, you are right and I am wrong." Sorry, not happening. I have done my research on this several years ago, and it is what I used to show a previous employer why I wanted to go RAID 10 instead of RAID 5 for my my production databases and the mdf files as well as the log files.
I don't have access to that research so I can't provide you with the links to the numerous articles and blog posts I read comparing the two RAID methodologies.
So at this point we just need to agree to disagree and stop trying to convinve each other that the other is wrong. All I am going to say at this point is that each person needs to do their due diligence and reseach to make the appropriate decision for their particular environment. DO NOT rely solely on what ANY of us say on the forums. Again, due your research. Support your decision with solid facts and be sure to take into account your environment, including the funds available to meet the goals of the system(s) in question.
December 26, 2012 at 12:10 pm
I believe most modern RAID controllers can do reads down both sides of a RAID10 set, which eliminates much if not all of the benefits of RAID 5 for reads. YMMV
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply