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 Thursday, January 17, 2013 1:34 PM
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 9, 2014 7:44 AM
Points: 988, Visits: 2,945
On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw your concerns here, appreciate it.

1. creating multiple tempdb data files (may be 10) on a single SSD drive.
2. pre allocating tempdb size to 1TB with restricted growth, each file 100gb.
3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?
Post #1408603
Posted Thursday, January 17, 2013 2:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:06 PM
Points: 19, Visits: 61

I have very successfully moved Log files to a SSD system (*) and received huge (2-5) times improvements in speed. I'd have thought moving the Log files would be more benefit than moving the TempDB.

SSD system, was just a couple of 128GB SSD's in RAID 1, quite cheap and the performance gain was fantastic.

Also looking at what you looking at doing, 10 tempdb files on one SSD, up to 1 TB, must be behind, is there a 1TB SSD on the market?
And do you really want to put any DB on a single drive, I'd want to RAID it somehow.
Post #1408621
Posted Thursday, January 17, 2013 2:49 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 21,329, Visits: 15,003
Tara-1044200 (1/17/2013)

3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?


That is how log files fill, not data files.

The data files can all be in use at the same time to fulfill different queries or the same query depending on the needs of the query(ies) being run.




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 #1408624
Posted Thursday, January 17, 2013 3:06 PM


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: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Tara-1044200 (1/17/2013)
On sql 2008 R2 which has 40 CPU's and one SSD drive I want to do the following, please throw your concerns here, appreciate it.

1. creating multiple tempdb data files (may be 10) on a single SSD drive.
2. pre allocating tempdb size to 1TB with restricted growth, each file 100gb.
3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?

Like most everything else pertaining to SQL Server, the "it depends" answer hold very true for TempDB. IMHO I wouldn't go beyond 1 data files per logical CPU (not to be confused with cores). I would also recommend you pre-size your datafiles to avoid any growth - period (exception being for the log file)...some general rules of thumb are to size total tempdb data files size to about 80-90% of the total drive space, making the single log file approximately double the size of a single data file. So if you you have 4 CPUs you could have:

Tempdb1.mdf @ 150GB
Tempdb2.mdf @ 150GB
Tempdb3.mdf @ 150GB
Tempdb4.mdf @ 150GB
Tempdb_log.ldf @ 300GB
(just an example of course)

Tempdb utilizes its data files in a round-robin manner and should always be of equal size to avoid SQL Server picking the largest data file to use first (SQL will continue to use the larger data file first, until the amount of free space is the same across all the other files...which will alleviate the point of having multiple files in the first place...which can lead to other issues - best to avoid this upfront)

Depending on your SSD drive model, SSD has about 10-20 times the amount of IOPS than regular SAS or SATA drives and are AWESOME/expensive hardware to have! As was previously mentioned, you may want to consider a RAID solution for your tempdb.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1408632
Posted Thursday, January 17, 2013 3:11 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 21,329, Visits: 15,003
Here's a good article on tempdb configuration
http://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/

There is a recommendations section you should check. Along with the recommendations, read the section on tracking tempdb contention. This should help with sizing and proper number of files for your environment.




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 #1408633
Posted Thursday, January 17, 2013 4:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
You realise that if the one SSD fails you're SQL Services will fall over. While all you'll need to do is replace the drive bring it online with the same drive letter and path for the files and SQL should start up ok. It's a situation that can be easily avoided with another SSD in raid 1.
Post #1408668
Posted Thursday, January 17, 2013 5:13 PM


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: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
I don't think you can even buy 1TB SSD???

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1408678
Posted Monday, January 21, 2013 4:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 2,837, Visits: 3,956
Tara-1044200 (1/17/2013)
3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?
NO , it oftens get used in parallell fashoin (multiple disk is being used) thats the reason many intermediate processes like sorting , aggregation , index rebuild/reorgainze temp table or table variable storage become FASTER while we have multiple disks


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1409468
Posted Tuesday, January 22, 2013 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 2,569, Visits: 3,806
Bhuvnesh (1/21/2013)
Tara-1044200 (1/17/2013)
3. If 1st file is filled does it uses the second file and so on untill it max out to 1TB or 10 files any query would not fail, correct?
NO , it oftens get used in parallell fashoin (multiple disk is being used) thats the reason many intermediate processes like sorting , aggregation , index rebuild/reorgainze temp table or table variable storage become FASTER while we have multiple disks


As the transaction log grows, the first log file fills, then the second, and so on, by using a fill-and-go strategy instead of a proportional fill strategy which happens in data file. Therefore, when a log file is added, it cannot be used by the transaction log until the other files have been filled first.
Post #1409986
Posted Tuesday, January 22, 2013 8:40 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 9, 2014 7:44 AM
Points: 988, Visits: 2,945
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.
Post #1410073
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse