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 10:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
Tara-1044200 (1/22/2013)
I just confirmed that its a RAMSAN drives and though the 5TB i have is memory not disk.


Now you are just making us jealous.




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 #1410147
Posted Tuesday, January 22, 2013 10:45 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 @ 1:10 AM
Points: 3,105, Visits: 11,498
Generally speaking, you will not see much improvement from having multiple tempdb files, except under specific conditions that are fairly rare.

When you are allocating an extremely high number of temp tables, there can sometimes be contention on certain internal structures that have to be updated for each temp table. Since these tables are on a per file basis, you can reduce contention by having multiple files. For a more complete explanation of this, you should read Paul S. Randal’s explanation of this:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

You might want to have multiple tempdb data files if you are going to put them on multiple volumes, but that does not sound like what you are planning.

The performance improvement that you get from having tempdb on SSDs will depend a lot on your application. If the application currently exists on another server, you might get some idea of the expected improvement by looking at the amount of IO and PAGELATCH_XX waits on tempdb that you have currently.

As others have suggested, you might get more performance improvement from using SSDs for application database data and log files.

Post #1410162
Posted Tuesday, January 22, 2013 10:49 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 9:42 PM
Points: 31,036, Visits: 15,466
Michael Valentine Jones (1/22/2013)
Generally speaking, you will not see much improvement from having multiple tempdb files, except under specific conditions that are fairly rare.



This is good info, but slightly old. The current recommendation from Bob Ward, of MS and CSS, is one file per core for < 8 cores. Above that, use 8 files and monitor: http://social.msdn.microsoft.com/Forums/br/sqldatabaseengine/thread/bb1ddba4-253d-478c-ac58-0abb23775000







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410166
Posted Tuesday, January 22, 2013 2:57 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 might find this helpful.

http://www.brentozar.com/archive/2012/12/bob-dylan-explains-tempdb-video/
Post #1410272
Posted Tuesday, January 22, 2013 4:07 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: Today @ 1:10 AM
Points: 3,105, Visits: 11,498
Steve Jones - SSC Editor (1/22/2013)
Michael Valentine Jones (1/22/2013)
Generally speaking, you will not see much improvement from having multiple tempdb files, except under specific conditions that are fairly rare.



This is good info, but slightly old. The current recommendation from Bob Ward, of MS and CSS, is one file per core for < 8 cores. Above that, use 8 files and monitor: http://social.msdn.microsoft.com/Forums/br/sqldatabaseengine/thread/bb1ddba4-253d-478c-ac58-0abb23775000


On that thread, Jonathan Kehayias says the following, which is just the point I was making:
"If you don't have tempdb contention on a PFS, GAM, or SGAM page, changing file count won't matter a single bit for the performance of the server, so it would be best to monitor for the contention first and then base your configuration on whether you have contention or not"



Post #1410291
Posted Wednesday, January 23, 2013 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Ratheesh.K.Nair (1/22/2013)
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.
my bad , i missd to mentioned here that i have explained here data file not the log file growth manner.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410371
Posted Wednesday, January 23, 2013 12:52 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: Yesterday @ 11:42 PM
Points: 870, Visits: 2,402
Steve Jones - SSC Editor (1/22/2013)
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.


+1
I have had very good results by putting tempdb data + log on a single raidgroup of local SSD's, both RAID1 pairs and RAID5 sets. One drive failure, and it still works. SSD's do, indeed, fail from time to time.
Post #1410760
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse