Shrinking TempDb files or adding more files to TempDB?

Question

Shrinking TempDb files or adding more files to TempDB?

VoldemarG

Hall of Fame

Points: 3883
More actions
August 10, 2022 at 2:42 pm

#4074865

We have 12 files in TempDb now (we added 4 a couple of months ago).
Most files have 96 to 99% space used (which is 500G or more).
My question is: Should I attempts to SHRINK all or some files? Via SHRINK FILE ?
OR
Should we add space to each?
OR
Add 4 or 8 more files to TempDb?
Thanking in advance for your advice.

Likes to play Chess

Viewing 6 posts - 1 through 5 (of 5 total)

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Are you seeing any evidence of contention (PAGELATCH waits in tempdb)? If not and it's just a space issue, you probably just want to increase the size of those 12 files (all the same size).

You must have quite a volume of large transactions hammering tempdb. Any tuning opportunities there?

Have you looked at what's in tempdb? Is anybody using tempdb directly and not cleaning up after themselves?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

The recommendation is to not have more than 8 tempdb files - unless you are specifically seeing contention. You can start here: https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

To be honest, 500GB of TempDB is terribly large for most servers. You'd probably do well to find out the cause.

Whatever happens, don't do a shrink. It's a waste of time because whatever grew it before will grow it again. You really need to find the cause of the growth before you attempt a shrink.

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

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 4

You have to investigate what is causing that much tempdb consumption.

#(#)temptb or @temptb
regular tables created in tempdb ?
version store (snapshot isolation level)
queries performing group by, sorts, calculation, materialized sets, cursors, ...

a good starter articles:

"Optimizing tempdb configuration with SQL Server 2012 Extended Events" by Jonathan Kehayias.
"SQLskills SQL101: Temporary table misuse" by Paul Randal
"The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention" by Paul Randal

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Johan Bijnens wrote:

You have to investigate what is causing that much tempdb consumption.
#(#)temptb or @temptb
regular tables created in tempdb ?
version store (snapshot isolation level)
queries performing group by, sorts, calculation, materialized sets, cursors, ...
a good starter articles:
"Optimizing tempdb configuration with SQL Server 2012 Extended Events" by Jonathan Kehayias.
"SQLskills SQL101: Temporary table misuse" by Paul Randal
"The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention" by Paul Randal

To be more explicit on your 4th item, the most serious and most likely potential problem is improperly written code resulting in many-to-many joins (accidental cross joins) resulting in huge temporary storage in the form of spools.

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