Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

tempdb files on SSD Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 8:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


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
Posted Tuesday, January 22, 2013 8:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 09, 2014 7:44 AM
Points: 988, Visits: 2,945
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
Posted Tuesday, January 22, 2013 9:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Posted Tuesday, January 22, 2013 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Posted Tuesday, January 22, 2013 9:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 09, 2014 7:44 AM
Points: 988, Visits: 2,945
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
Posted Tuesday, January 22, 2013 9:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 3,733, Visits: 7,072
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
Posted Tuesday, January 22, 2013 9:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


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
Posted Tuesday, January 22, 2013 9:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Posted Tuesday, January 22, 2013 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
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
Posted Tuesday, January 22, 2013 9:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 09, 2014 7:44 AM
Points: 988, Visits: 2,945
I just confirmed that its a RAMSAN drives and though the 5TB i have is memory not disk.
Post #1410139
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse