TempDb File Sizing

Question

TempDb File Sizing

HappyGeek

SSCoach

Points: 18729
More actions
November 29, 2016 at 7:47 am

#332472

I have inherited an infrastructure in which the tempdb is set up with multiple files, 8 all told (mdf and ndf), so far so good. But they are set up with mdf initial size 8mb with 10 percent growth while the others are 50GB initial size with no growth; max size 50GB.
This seems wrong, I understood all files should be the same size with the same growth, but cannot find anything to support my argument. What is the recommendation on sizing individual files and what is the implication of changing them if necessary and when would that be best?
SQL 2012 Enterprise on 2012 servers.
...

Viewing 14 posts - 1 through 13 (of 13 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

When you say the others do you mean other databases, or the TempDB files?

TempDB generally starts with several small files (generally 1 per core, up to 8). These all have the same initial size setting, and then all grow at the same speed, due to the use of Trace Flags 1118 and 1117 being enabled.

If this isn''t the case, and the files within tempdb have different initials and grow rates, then no, this isn't right. If you mean that the other databases have different grow rates, that's completely fine.

Having different size tempdb files will result in poor utilisation of the files, with SQL Server mainly choosing the largest of the files to do its work load and leaving the others be.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Definitely best practice to set all tempdb data files same size and same growth. Unbalanced scenarios can happen otherwise, leading to poor IO usage patterns.

You can grow the main file that is not 50GB immediately to be the same size and no growth as the others. Assuming you have instant file initialization on this should be quick.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

joeroshan SSChampion Points: 10377 More actions · Answer 3

Yes. The data files need to be of same size if you need proportional fill algorithm to work. You can validate the file usage using following query

Select Name,type_desc,physical_name,size*8.0/1024 SizeMB,(size-FILEPROPERTY(name,'Spaceused')) *8.0/1024.00 FreeSpaceMB from sys.database_files DF;

I found a blog from Paul Randal for datafile.

http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Quick questions, what is the IO subsystem setup? Are those tempdb files set up on different IO paths? What are the top tempdb waits (sys.dm_exec_request / sys.dm_os_waiting_task)? Are there more than 8 tempdb data files?

😎

First of all, try to figure out if there are any problems, multiple tempdb files aren't always necessary or needed. Secondly if there is a problem and the problem is GAM and SGAM congestion related, then check out Recommendations to reduce allocation contention in SQL Server tempdb database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

HappyGeek (11/29/2016)
What is the recommendation on sizing individual files.

All the same size, all the same growth, otherwise the proportional fill won't fill the files evenly, which defeats the entire point of multiple tempDB files

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HappyGeek SSCoach Points: 18729 More actions · Answer 6

Many thanks for the prompt responses, but just to clarify:

I am looking at tempdb, it has 8 data files plus the log. the mdf file has initial size of 8MB with a growth of 10%, the ndf files have initial size of 50GB restricted to max 50GB the log is 5GB with 10% growth, the maximum wait times are in respect of CXPACKET, MAXDOP is set to 4 with a DOP set to 20.

The files ae on three separate drive.

...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

HappyGeek (11/29/2016)
Many thanks for the prompt responses, but just to clarify:
I am looking at tempdb, it has 8 data files plus the log. the mdf file has initial size of 8MB with a growth of 10%, the ndf files have initial size of 50GB restricted to max 50GB the log is 5GB with 10% growth, the maximum wait times are in respect of CXPACKET, MAXDOP is set to 4 with a DOP set to 20.
The files ae on three separate drive.

What is the output of this code

DBCC TRACESTATUS

😎

The 10% growth is naive at the best, suggest you change that to a fixed number in megabytes to a handful of megabytes, resize the files to the maximum predicted size minus few times the growth size and then monitor the tempdb file growth events.

HappyGeek SSCoach Points: 18729 More actions · Answer 8

HappyGeek

SSCoach

Points: 18729

November 30, 2016 at 12:15 am

#1915098

DBCC TRACESTAUS runs without error.

...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

HappyGeek (11/30/2016)
DBCC TRACESTAUS runs without error.

And the output?

😎

HappyGeek SSCoach Points: 18729 More actions · Answer 10

Eirikur Eiriksson (11/30/2016)
HappyGeek (11/30/2016)
DBCC TRACESTAUS runs without error.
And the output?
😎

Sorry, yes there are no active traces

...

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 11

HappyGeek (11/30/2016)
Eirikur Eiriksson (11/30/2016)
HappyGeek (11/30/2016)
DBCC TRACESTAUS runs without error.
And the output?
😎
Sorry, yes there are no active traces

The DBCC TRACESTATUS lists out active trace flags, has nothing to do with traces. In order to have any benefits from multiple tempdb files, the trace flag 1117 must be enabled which forces all files in a file group to grow at each file growth event. This means that if the initial size for the files is the same and the growth is the same, the sizes of all files within the filegroup will stay the same, essential for multi-file tempdb configuration.

😎

I also recommend setting the trace flag 1118 which forces full extent allocations, no mixed extents will be allocated, reduces allocation contention especially in the tempdb.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 12

There's no way in hell that I'd enable TF 1117. It doesn't just affect TempDB and I don't want the nearly 90 temporal files/filegroups for a 600GB table to be all the same size because it would waste a huge amount of disk space.

For TempDB, I've setup 8 files (we have 32 processors), have set them all to an initial size of 2GB each with 500MB growth each (they've never grown) and the log file to 2GB and, of course, have TF 1118 in play.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

Jeff Moden (12/1/2016)
There's no way in hell that I'd enable TF 1117. It doesn't just affect TempDB and I don't want the nearly 90 temporal files/filegroups for a 600GB table to be all the same size because it would waste a huge amount of disk space.
For TempDB, I've setup 8 files (we have 32 processors), have set them all to an initial size of 2GB each with 500MB growth each (they've never grown) and the log file to 2GB and, of course, have TF 1118 in play.

Thankfully this has been fixed in SQL 2016! Now tempdb ALWAYS has BOTH enabled. And for other databases they are moved to ALTER DATABASE settings.

https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service