tempdb sizing

Question

tempdb sizing

vsamantha35

SSChampion

Points: 11551
More actions
October 18, 2023 at 5:32 am

#4310184

Hi All,
How to size the tempdb drives? Currently tempdata drive is 2TB and but now a days its getting filled up very quickly leaving 6mb free. This happens when certain application queries runs. Eventually, we had to kill those queries. They are reading 76 TB worth data based on joins and so some ORDER BY clauses which are sorting the results. My question is, assuming even after tuning queries, how do we size the tempdb if we are building a new sql server VM or existing SQL server. What things to be monitored and what things to be trended and what is calculation goes on in sizing? How much we need to keep the buffer?
Also, if the database is growing unevenly, lets say, every 3-6 months, then how to do the sizing?
Looking for some insights on what goes through sizing a database?
Thanks,
Sam

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

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

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

fix the queries and the indexes - based on your latest threads that is where your issue is and the sooner you do it the better for everyone.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

I'll second that.

But for your tempdb sizing, whatever you choose, I would recommend statically setting the file sizes and not allowing them to autogrow. Jeff I believe has different thoughts on this, but for me it has always ended up being preferable that individual processes fail because they ran out of tempDB space, over the risk of the disruption a tempdb filling up the drive can cause. If the tempdb does fill up for a valid reason, expand the drive to prevent it from happening next time.

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

Thumbs up to the previous 2 replies. The code is in serious need of some real help and the Developers need to be taught how to write good code. I'm not saying that to be mean... I'm simply stating a fact.

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

vsamantha35 SSChampion Points: 11551 More actions · Answer 4

frederico_fonseca wrote:

fix the queries and the indexes - based on your latest threads that is where your issue is and the sooner you do it the better for everyone.

Thanks sir. The database size is ~5TB,. Agree with fixing those bad queries which are eating up tempdb.

What I want to know is , considering if the queries are well tuned and if it is a stable sql server what is the process involved or what factors needs to be taken into consideration or monitored for sizing tempdb?

Thanks.

Sam