Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

tempdb fileplacement Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 7:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 202, Visits: 387
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

Post #1070519
Posted Monday, February 28, 2011 7:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 8:24 AM
Points: 41, Visits: 390
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
Post #1070530
Posted Monday, February 28, 2011 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 13, Visits: 418
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.



Post #1070648
Posted Tuesday, March 01, 2011 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 202, Visits: 387
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
Post #1071063
Posted Sunday, December 23, 2012 11:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:24 AM
Points: 12, Visits: 119
no post just want to return to article
Post #1399814
Posted Sunday, December 23, 2012 11:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:24 AM
Points: 12, Visits: 119
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?
Post #1399817
Posted Monday, December 24, 2012 9:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 1,746, Visits: 2,552
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)
I'm not fat, I'm gravity challenged.
Post #1399927
Posted Monday, December 24, 2012 12:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 22,492, Visits: 30,192
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.



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)
Post #1399964
Posted Monday, December 24, 2012 12:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 22,492, Visits: 30,192
Actually, just found one reference here: http://www.sqlservercentral.com/articles/RAID/88945/.



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)
Post #1399980
Posted Tuesday, December 25, 2012 8:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:43 PM
Points: 4,128, Visits: 5,836
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 at GMail
Post #1400074
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse