<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
tempdb fileplacement
19 posts, Page 1 of 2
1
2
»»
tempdb fileplacement
Rate Topic
Display Mode
Topic Options
Author
Message
stakes
stakes
Posted Monday, February 28, 2011 7:35 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 139,
Visits: 330
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
fenwicc
fenwicc
Posted Monday, February 28, 2011 7:48 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:53 AM
Points: 41,
Visits: 378
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
mika.sutinen
mika.sutinen
Posted Monday, February 28, 2011 10:09 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, June 13, 2013 3:53 AM
Points: 13,
Visits: 329
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
stakes
stakes
Posted Tuesday, March 01, 2011 2:58 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 139,
Visits: 330
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
steve_kirchner
steve_kirchner
Posted Sunday, December 23, 2012 11:46 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:08 PM
Points: 12,
Visits: 118
no post just want to return to article
Post #1399814
steve_kirchner
steve_kirchner
Posted Sunday, December 23, 2012 11:48 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:08 PM
Points: 12,
Visits: 118
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
ScottPletcher
ScottPletcher
Posted Monday, December 24, 2012 9:31 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 1,333,
Visits: 1,803
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 file
s
and ldf file on the same raid set, be sure to get RAID10 and not RAID5.
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1399927
Lynn Pettis
Lynn Pettis
Posted Monday, December 24, 2012 12:21 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 21,832,
Visits: 27,855
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 file
s
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
Lynn Pettis
Lynn Pettis
Posted Monday, December 24, 2012 12:52 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 21,832,
Visits: 27,855
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
TheSQLGuru
TheSQLGuru
Posted Tuesday, December 25, 2012 8:39 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 3,674,
Visits: 5,174
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 »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
