Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tempdb files on SSD


tempdb files on SSD

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 3059
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?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
My Blog: www.voiceofthedba.com
Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 3059
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?
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4905 Visits: 7365
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??? Hehe

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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??? Hehe


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
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
My Blog: www.voiceofthedba.com
Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 3059
I just confirmed that its a RAMSAN drives and though the 5TB i have is memory not disk.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search