<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 Administration
»
tempdb files on SSD
27 posts, Page 2 of 3
««
1
2
3
»»
tempdb files on SSD
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Tuesday, January 22, 2013 8:43 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733,
Visits: 12,330
Tara-1044200 (1/22/2013)
I see 40 CPU's (2 nodes) when i check properties of sql server under "Processors". I think creating 40 tempdbdata files may be too much.
I do have 5 TB of SSD, i was told by our network admin and may they are combination of multiple drives in a LUN i assume.
Check the links I recommended. BP currently is to start at 8 files (with more than 8 procs) and then grow from there if there is tempdb contention.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1410076
Tara-1044200
Tara-1044200
Posted Tuesday, January 22, 2013 8:59 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:37 PM
Points: 983,
Visits: 2,924
but i am planning to put them on a single SSD drive which i asusme would be 8 files each of 100gb. so i think any file would be used out of 8 during a transaction untill it reaches 800gb right?
Post #1410090
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, January 22, 2013 9:22 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406,
Visits: 13,723
MyDoggieJessie (1/17/2013)
I don't think you can even buy 1TB SSD???
http://www.newegg.com/Product/Product.aspx?Item=N82E16820227724
A 2TB SSD was announced last week as well.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410104
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, January 22, 2013 9:24 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406,
Visits: 13,723
Tara-1044200 (1/22/2013)
but i am planning to put them on a single SSD drive which i asusme would be 8 files each of 100gb. so i think any file would be used out of 8 during a transaction untill it reaches 800gb right?
A single drive is a bad idea just in case of failure. At the very least, spread out across 2.
The way files are filled is that they are used in a proportional manner, based on free space. So a query could potentially put objects in multiple tempdb files. That's why recommendations are that you size all the files the same, to get even spreading.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410106
Tara-1044200
Tara-1044200
Posted Tuesday, January 22, 2013 9:30 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:37 PM
Points: 983,
Visits: 2,924
when you say proportional manner do i have to restrict each file to certain size and then when total size uses more than max of all files then the query would fail, correct.
also if 1 drive fails which has 4 tempdb files out of 8 does the sql server continue to run with the rest of the 4 files located on the second drive?
Post #1410110
MyDoggieJessie
MyDoggieJessie
Posted Tuesday, January 22, 2013 9:31 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 2,035,
Visits: 3,759
It's a good rule of thumb to place it on multiple drives (especially in RAID configuration). Have you taken steps to monitor your tempdb usage and do you have an idea of how large it needs to be (will be?) 1TB seems very large...
@Steve - 2TB? What's that like a $3,000 harddrive???
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1410111
SQLRNNR
SQLRNNR
Posted Tuesday, January 22, 2013 9:32 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733,
Visits: 12,330
Tara-1044200 (1/22/2013)
when you say proportional manner do i have to restrict each file to certain size and then when total size uses more than max of all files then the query would fail, correct.
You don't need to restrict each file on max size to get the proportional fill. But the restriction is a recommendation in many cases.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1410113
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, January 22, 2013 9:36 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406,
Visits: 13,723
MyDoggieJessie (1/22/2013)
It's a good rule of thumb to place it on multiple drives (especially in RAID configuration). Have you taken steps to monitor your tempdb usage and do you have an idea of how large it needs to be (will be?) 1TB seems very large...
@Steve - 2TB? What's that like a $3,000 harddrive???
No pricing yet:
http://thessdreview.com/daily-news/latest-buzz/solidata-to-unveil-2tb-lsi-sandforce-driven-2-5-sata-2-ssd-first-pictures/
The 1.2TB one I linked is around $3200.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410115
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, January 22, 2013 9:37 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406,
Visits: 13,723
If you run out of space, then yes, the query fails. You don't have to restrict sizes, though as Jason mentioned, this is recommended in some cases. Not sure how many. Ultimately, there is a restriction: the size of the drive.
I would size these high, make them the same size, and make sure all autogrow settings are the same.
If you grow them manually (monitor to see your % of use over time), grow all files by the same amount at the same time.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410118
Tara-1044200
Tara-1044200
Posted Tuesday, January 22, 2013 9:59 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:37 PM
Points: 983,
Visits: 2,924
I just confirmed that its a RAMSAN drives and though the 5TB i have is memory not disk.
Post #1410139
« Prev Topic
|
Next Topic »
27 posts, Page 2 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.
