<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
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Aldo Gonzalez
»
Setting All Tempdb Data Files to the Same...
22 posts, Page 1 of 3
1
2
3
»
»»
Setting All Tempdb Data Files to the Same Size
Rate Topic
Display Mode
Topic Options
Author
Message
Aldo Gonzalez
Aldo Gonzalez
Posted Saturday, July 17, 2010 12:07 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:59 PM
Points: 8,
Visits: 155
Comments posted to this topic are about the item
Setting All Tempdb Data Files to the Same Size
Post #954415
Joe Fuller
Joe Fuller
Posted Sunday, July 18, 2010 9:59 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:26 PM
Points: 20,
Visits: 218
Hi Aldo,
rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.
e.g. On 40GB drive set each file to 10GB
Thansk
Post #954578
Joe Fuller
Joe Fuller
Posted Sunday, July 18, 2010 9:59 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:26 PM
Points: 20,
Visits: 218
Hi Aldo,
rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.
e.g. On 40GB drive set each file to 10GB
Thansk
Post #954579
Dave Ballantyne
Dave Ballantyne
Posted Monday, July 19, 2010 2:08 AM
SSCommitted
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943,
Visits: 8,227
Though im not going to argue with your analysis, the premise of the article
"Best practices call for tempdb to have one datafile per processor (core)"
is based on a myth
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx
"So, why is one-to-one not always a good idea? Too many tempdb data files
can cause
performance problems for another reason"
My emphasis.
Clear Sky SQL
My Blog
Kent user group
Post #954641
jts_2003
jts_2003
Posted Monday, July 19, 2010 3:30 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 1,060,
Visits: 4,182
Although you may not need a one-to-one match for TempDB files and CPU cores, if you do think TempDB is causing a bottleneck, I'd certainly recommend increasing the number of TempDB files from 1. On a number of our servers I've set up 2 files for TempDB (some servers have 2 cores, some have many more) and even just moving to 2 files seems to improve performance.
I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!
Post #954669
Keith Edwards
Keith Edwards
Posted Monday, July 19, 2010 5:15 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 6:54 AM
Points: 183,
Visits: 237
This one definitely depends on your situation. Most of the time I break the TempDB into only four or eight separate equally sized files with no auto grow. This has been a good general practice for my environment, but each server can be different depending on the type of data on them so testing is the best option. Also in the bulk of the tests I did a couple years ago I found no significant gains in splitting the TempDB files between different Luns/Spindles.
Post #954740
roman.pokrovskij
roman.pokrovskij
Posted Monday, July 19, 2010 5:57 AM
Grasshopper
Group: General Forum Members
Last Login: Saturday, March 16, 2013 12:46 PM
Points: 14,
Visits: 41
Hello all.
Are those objections true for virtual servers? May be then situation it is more complex since IO is "virtual"?
Post #954756
BowlOfCereal
BowlOfCereal
Posted Monday, July 19, 2010 7:05 AM
Valued Member
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:50 PM
Points: 57,
Visits: 317
jts_2003 (7/19/2010)
I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!
There's something funky about this logic. You're not increasing space in an emergency; you're artificially limiting space to begin with, and adding the overhead of an autogrow in an emergency. If you think a query might pass that 80% mark, wouldn't it be better to dedicate 100% of the disk to begin with?
Post #954790
o-3463522
o-3463522
Posted Monday, July 19, 2010 9:43 AM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, May 01, 2011 5:12 PM
Points: 5,
Visits: 144
What is the point of having multiple files ON THE SAME DRIVE c:?
Post #954904
SQLBalls
SQLBalls
Posted Monday, July 19, 2010 10:13 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:34 PM
Points: 385,
Visits: 611
obulay-657245 (7/19/2010)
What is the point of having multiple files ON THE SAME DRIVE c:?
This is straight from Paul Randal's blog that another user posted a link to abover
PS To address a comment that came in - no, the extra files don't *have* to be on separate storage. If all you're seeing it PAGELATCH contention, separate storage makes no difference as the contention is on in-memory pages. For PAGEIOLATCH waits, you most likely will need to use separate storage, but not necessarily - it may be that you need to move tempdb itself to different storage from other databases rather than just adding more tempdb data files. Analysis of what's stored where will be necessary to pick the correct path to take.
Good article, but great discussion. I just worked with some Microsoft folks on thier SCOM team for a deployment of thier software and they insisted on 1 TempDB per processor and for it to take up most of the drive 80%. It is good to now know what to look for to see if the recommendations were over kill or if they were valid.
It looks likethe answer is with most things SQL Server ....It Depends
Twitter: @SQLBalls
Blog:
http://www.SQLBalls.com
Post #954932
« Prev Topic
|
Next Topic »
22 posts, Page 1 of 3
1
2
3
»
»»
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.
