January 22, 2013 at 9:31 am
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-2 - 2TB? What's that like a $3,000 harddrive??? :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 22, 2013 at 9:32 am
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[/url]
Learn Extended Events
January 22, 2013 at 9:36 am
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-2 - 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.
January 22, 2013 at 9:37 am
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.
January 22, 2013 at 9:59 am
I just confirmed that its a RAMSAN drives and though the 5TB i have is memory not disk.
January 22, 2013 at 10:09 am
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[/url]
Learn Extended Events
January 22, 2013 at 10:45 am
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:
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.
January 22, 2013 at 10:50 am
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
January 22, 2013 at 2:57 pm
You might find this helpful.
http://www.brentozar.com/archive/2012/12/bob-dylan-explains-tempdb-video/
January 22, 2013 at 4:07 pm
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"
January 23, 2013 at 12:28 am
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;-)
January 23, 2013 at 12:52 pm
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.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply