SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tempdb fileplacement


tempdb fileplacement

Author
Message
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
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
fenwicc
fenwicc
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 406
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
MikaS
MikaS
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 706
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.

Mika Sutinen, Senior DBA
@SQLFinn on Twitter
Blogging at SQLStarters
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
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
steve_kirchner
steve_kirchner
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 129
no post just want to return to article
steve_kirchner
steve_kirchner
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 129
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?
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7886 Visits: 7149
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39299 Visits: 38529
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39299 Visits: 38529
Actually, just found one reference here: http://www.sqlservercentral.com/articles/RAID/88945/.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12429 Visits: 8548
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search