<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
tempdb files on SSD
27 posts, Page 3 of 3
««
«
1
2
3
tempdb files on SSD
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Tuesday, January 22, 2013 10:09 AM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858,
Visits: 12,443
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 2008
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
Michael Valentine Jones
Michael Valentine Jones
Posted Tuesday, January 22, 2013 10:45 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969,
Visits: 10,620
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, January 22, 2013 10:49 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:39 AM
Points: 31,529,
Visits: 13,866
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
davidwarner
davidwarner
Posted Tuesday, January 22, 2013 2:57 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:06 PM
Points: 19,
Visits: 72
You might find this helpful.
http://www.brentozar.com/archive/2012/12/bob-dylan-explains-tempdb-video/
Post #1410272
Michael Valentine Jones
Michael Valentine Jones
Posted Tuesday, January 22, 2013 4:07 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969,
Visits: 10,620
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
Bhuvnesh
Bhuvnesh
Posted Wednesday, January 23, 2013 12:28 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562,
Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1410371
Nadrek
Nadrek
Posted Wednesday, January 23, 2013 12:52 PM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, June 10, 2013 1:08 PM
Points: 679,
Visits: 2,038
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 »
27 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
