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»»»

Setting All Tempdb Data Files to the Same Size Expand / Collapse
Author
Message
Posted Saturday, July 17, 2010 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 PM
Points: 8, Visits: 179
Comments posted to this topic are about the item Setting All Tempdb Data Files to the Same Size
Post #954415
Posted Sunday, July 18, 2010 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:25 PM
Points: 25, Visits: 239
Hi Aldo,
rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.
e.g. On 40GB drive set each file to 10GB

Thansk
Post #954578
Posted Sunday, July 18, 2010 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:25 PM
Points: 25, Visits: 239
Hi Aldo,
rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.
e.g. On 40GB drive set each file to 10GB

Thansk
Post #954579
Posted Monday, July 19, 2010 2:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Though im not going to argue with your analysis, the premise of the article

"Best practices call for tempdb to have one datafile per processor (core)"

is based on a myth

http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

"So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason"

My emphasis.




Clear Sky SQL
My Blog
Kent user group
Post #954641
Posted Monday, July 19, 2010 3:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
Although you may not need a one-to-one match for TempDB files and CPU cores, if you do think TempDB is causing a bottleneck, I'd certainly recommend increasing the number of TempDB files from 1. On a number of our servers I've set up 2 files for TempDB (some servers have 2 cores, some have many more) and even just moving to 2 files seems to improve performance.

I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!
Post #954669
Posted Monday, July 19, 2010 5:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:15 AM
Points: 198, Visits: 294
This one definitely depends on your situation. Most of the time I break the TempDB into only four or eight separate equally sized files with no auto grow. This has been a good general practice for my environment, but each server can be different depending on the type of data on them so testing is the best option. Also in the bulk of the tests I did a couple years ago I found no significant gains in splitting the TempDB files between different Luns/Spindles.


Post #954740
Posted Monday, July 19, 2010 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 14, Visits: 118
Hello all.

Are those objections true for virtual servers? May be then situation it is more complex since IO is "virtual"?
Post #954756
Posted Monday, July 19, 2010 7:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:17 AM
Points: 73, Visits: 401
jts_2003 (7/19/2010)
I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!


There's something funky about this logic. You're not increasing space in an emergency; you're artificially limiting space to begin with, and adding the overhead of an autogrow in an emergency. If you think a query might pass that 80% mark, wouldn't it be better to dedicate 100% of the disk to begin with?
Post #954790
Posted Monday, July 19, 2010 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 1, 2011 5:12 PM
Points: 5, Visits: 144
What is the point of having multiple files ON THE SAME DRIVE c:?

Post #954904
Posted Monday, July 19, 2010 10:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
obulay-657245 (7/19/2010)
What is the point of having multiple files ON THE SAME DRIVE c:?



This is straight from Paul Randal's blog that another user posted a link to abover

PS To address a comment that came in - no, the extra files don't *have* to be on separate storage. If all you're seeing it PAGELATCH contention, separate storage makes no difference as the contention is on in-memory pages. For PAGEIOLATCH waits, you most likely will need to use separate storage, but not necessarily - it may be that you need to move tempdb itself to different storage from other databases rather than just adding more tempdb data files. Analysis of what's stored where will be necessary to pick the correct path to take.


Good article, but great discussion. I just worked with some Microsoft folks on thier SCOM team for a deployment of thier software and they insisted on 1 TempDB per processor and for it to take up most of the drive 80%. It is good to now know what to look for to see if the recommendations were over kill or if they were valid.

It looks likethe answer is with most things SQL Server ....It Depends


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #954932
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse