Setting All Tempdb Data Files to the Same Size

Question

Post reply

Setting All Tempdb Data Files to the Same Size

Aldo Gonzalez

SSC Enthusiast

Points: 149
More actions
July 17, 2010 at 12:07 pm

#224866

Comments posted to this topic are about the item Setting All Tempdb Data Files to the Same Size

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Joe Fuller SSC-Addicted Points: 408 More actions · Answer 1

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

Joe Fuller SSC-Addicted Points: 408 More actions · Answer 2

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

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[/url]

jts2013 Hall of Fame Points: 3226 More actions · Answer 4

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!

Keith Edwards Default port Points: 1446 More actions · Answer 5

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.

roman.pokrovskij SSC Enthusiast Points: 110 More actions · Answer 6

Hello all.

Are those objections true for virtual servers? May be then situation it is more complex since IO is "virtual"?

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 7

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?

o-3463522 SSC Enthusiast Points: 111 More actions · Answer 8

What is the point of having multiple files ON THE SAME DRIVE c:?

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 9

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
Channel: https://www.youtube.com/@Tales-from-the-Field

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for sharing the article.

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

Tim-153783 Default port Points: 1407 More actions · Answer 11

I would encourage everyone following this thread to heed what Paul Randal posted in his blog give above. Tempdb should have no more than 8 datafiles. You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 12

Tim-153783 (7/19/2010)
You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.

Remember this is for NON-TDE databases, SQL instances that use TDE cannot use instant initialization for the Tempdb as it is encrypted. As well any database on the instance that is using TDE cannot use Instant initialization as well.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 13

I agree. The article only addresses the sizing of each datafile with respect to the other datafiles on the tempdb filegroup.

Thanks.

Aldo Gonzalez SSC Enthusiast Points: 149 More actions · Answer 14

Thanks you for the comment. It is clear that there are two schools of thought when it comes to the right number of datafiles in tempdb. Microsoft recommends the datafile per core setting, and I have read articles that report major improvements when one datafile per core is used over the single datafile configuration. The purpose of the article is not to argue in favor of such practice, but to provide the steps to implement it.

I will read the article you point to. A great experiment would be to set up both configurations and compare.

Thanks again.