tempdb fileplacement

Question

tempdb fileplacement

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

stakes (2/28/2011)
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

I frequently put the tempdb log and data files on the same volume. Unless there is significant bottle neck, then this configuration works fine.

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

ScottPletcher SSC Guru Points: 101126 More actions · Answer 2

Again, if you've got unlimited funds, definitely go RAID10 (or SSD) throughout, but because of the better resiliency, even if pure reads are somewhat slower.

Presumably all of us have done some research and are not merely making things up.

http://www.kendalvandyke.com/2009/02/disk-performance-hands-on-part-5-raid.html

"

Generally speaking, it’s common knowledge that RAID 5 offers better read performance while RAID 10 offers better write performance.

"

I thought so too, but it's obviously NOT as commonly accepted a knowledge as we thought!

http://sqlblog.com/blogs/linchi_shea/archive/2007/02/07/is-raid-5-really-that-bad.aspx

[linchi suggests that even writes can do better on RAID5 -- I still would not suggest RAID5 for even fairly heavy write activity.]

But if you can't get better read performance from the same total drives in RAID5 vs RAID10, then you've got some other issue.

Historical data that is (almost) never modified should do better on RAID5.

This assumes that all log files go to RAID10 -- log files are almost 100% write, and so absolutely belong only on RAID10 whenever possible.

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".

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

SQLRNNR (12/26/2012)
stakes (2/28/2011)
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
I frequently put the tempdb log and data files on the same volume. Unless there is significant bottle neck, then this configuration works fine.

I agree that in general you can put tempdb log and data files on the same drive set. However, IF it will be difficult to get add a volume later, then you should probably go ahead and get two now. You can always find a use for another drive set in SQL Server, so it won't go to waste :-).

Further, you could spread tempdb's data files across the two drives as well, to smooth out any potential roughness in tempdb performance.

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".

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Beware basing SQL Server beliefs on 3 and 5 year old information on the internet...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service