<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 1 of 3
1
2
3
»
»»
tempdb files on SSD
Rate Topic
Display Mode
Topic Options
Author
Message
Tara-1044200
Tara-1044200
Posted Thursday, January 17, 2013 1:34 PM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:53 AM
Points: 984,
Visits: 2,934
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
ShineBoy
ShineBoy
Posted Thursday, January 17, 2013 2:38 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 10:21 PM
Points: 18,
Visits: 45
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
SQLRNNR
SQLRNNR
Posted Thursday, January 17, 2013 2:49 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 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 #1408624
MyDoggieJessie
MyDoggieJessie
Posted Thursday, January 17, 2013 3:06 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063,
Visits: 3,787
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
SQLRNNR
SQLRNNR
Posted Thursday, January 17, 2013 3:11 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 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 #1408633
davidwarner
davidwarner
Posted Thursday, January 17, 2013 4:38 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:06 PM
Points: 19,
Visits: 72
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
MyDoggieJessie
MyDoggieJessie
Posted Thursday, January 17, 2013 5:13 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063,
Visits: 3,787
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
Bhuvnesh
Bhuvnesh
Posted Monday, January 21, 2013 4:14 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562,
Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1409468
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Tuesday, January 22, 2013 6:13 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 2,425,
Visits: 3,401
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
Tara-1044200
Tara-1044200
Posted Tuesday, January 22, 2013 8:40 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:53 AM
Points: 984,
Visits: 2,934
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 »
27 posts, Page 1 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.
