SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tempdb files on SSD


tempdb files on SSD

Author
Message
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7440 Visits: 11793
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81344 Visits: 19205
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
My Blog: www.voiceofthedba.com
PretendDBA
PretendDBA
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 503
You might find this helpful.

http://www.brentozar.com/archive/2012/12/bob-dylan-explains-tempdb-video/
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7440 Visits: 11793
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"
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6816 Visits: 4076
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;-)
Nadrek
Nadrek
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2389 Visits: 2729
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.
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