Solving Tempdb Issues

Question

Post reply

Solving Tempdb Issues

Steve Jones - SSC Editor

SSC Guru

Points: 734444
More actions
May 11, 2016 at 9:43 pm

#316453

Comments posted to this topic are about the item Solving Tempdb Issues

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

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

There should be tooling and warnings in some form.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Terje Hermanseter SSCarpal Tunnel Points: 4765 More actions · Answer 2

Are there any use cases at all for having differently sized tempdb files? I see only recommendations for using the exact same size for all the files.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

So what potentially could happen with them being different sizes?

Terje Hermanseter SSCarpal Tunnel Points: 4765 More actions · Answer 4

If one file is significantly larger (or more correctly have a higher write-free-perecent) than the others, that file will receive all the write operations. If that happens you have no benefit of having multiple files.

jay-h SSCoach Points: 18816 More actions · Answer 5

The basic issue was that tempdb files were sized unequally, discovered after "months of investigation."

Frequently when one sees an 'explanation' like this, there's a whole lot that's not being said.

...

-- FORTRAN manual for Xerox Computers --

kiwood Ten Centuries Points: 1074 More actions · Answer 6

Terje Hermanseter (5/12/2016)
If one file is significantly larger (or more correctly have a higher write-free-perecent) than the others, that file will receive all the write operations. If that happens you have no benefit of having multiple files.

If it is based on the percentage, then it would seem like a bad idea to allow this. If it were amount of space available, then I could imagine scenarios where an admin might setup a file to only be used when space on others became an issue.

All the same, it would seem that support could/should come up with a tool/script to check known best practice. Then they would know to at least consider the effects of any violation. Such a tool/script could also serve to help more inexperienced admins learn more, and keep those of us who aren't really admins from shooting ourselves in the foot.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 7

Terje Hermanseter (5/12/2016)
Are there any use cases at all for having differently sized tempdb files? I see only recommendations for using the exact same size for all the files.

Yes, run out of space in tempdb, run out of space on disk. Add new disk. May not be able to add an equivalent file.

There's also a recommendation by Brent Ozar to have one small file, which has alerting on it for autogrow. Since it's smaller, it doesn't get growth when other files do, but if they fill up, then this one grows and you get an alert to respond.

corsair1 Old Hand Points: 308 More actions · Answer 8

Look at SQL 2016's setup. This issue and enabling instant file initialization are now both handled.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 9

I find it amazing that still today that if you take the defaults the sizing and growth percentages for TEMP DB, model, msdb and master are so low. 10% growth of a 1mb file? Talk about fragmentation.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 10

Markus (5/13/2016)
I find it amazing that still today that if you take the defaults the sizing and growth percentages for TEMP DB, model, msdb and master are so low. 10% growth of a 1mb file? Talk about fragmentation.

I found an old Connect item about this, from 2008, closed as "Won't fix." I guess it won't happen.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 11

ThomasRushton (5/13/2016)
Markus (5/13/2016)
I find it amazing that still today that if you take the defaults the sizing and growth percentages for TEMP DB, model, msdb and master are so low. 10% growth of a 1mb file? Talk about fragmentation.
I found an old Connect item about this, from 2008, closed as "Won't fix." I guess it won't happen.

Amazing isn't it. All experts state don't use percentage for growth of mdf and ldf but MSOFT uses that. I have a script that resizes master, model, msdb, TEMP and their grown that I run right after I install.

Terje Hermanseter SSCarpal Tunnel Points: 4765 More actions · Answer 12

Steve Jones - SSC Editor (5/12/2016)
Terje Hermanseter (5/12/2016)
Are there any use cases at all for having differently sized tempdb files? I see only recommendations for using the exact same size for all the files.
Yes, run out of space in tempdb, run out of space on disk. Add new disk. May not be able to add an equivalent file.
There's also a recommendation by Brent Ozar to have one small file, which has alerting on it for autogrow. Since it's smaller, it doesn't get growth when other files do, but if they fill up, then this one grows and you get an alert to respond.

That's a nice example of clever thinking! I must remember this.

andrew gothard SSChampion Points: 12301 More actions · Answer 13

Markus (5/13/2016)
I find it amazing that still today that if you take the defaults the sizing and growth percentages for TEMP DB, model, msdb and master are so low. 10% growth of a 1mb file? Talk about fragmentation.

What I find just as astounding is the number of people who still don't appear to know about this. Or use of multiple files

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.