tempdb sizing

Question

tempdb sizing

Sam 1,476

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

Anonymous

2023-10-19T06:59:53.7433333+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Answer accepted by question author

Erland Sommarskog 134.7K MVP Volunteer Moderator

If the database is in the 80 TB range, I can't say that 2 TB tempdb sounds excessive.

But as Olaf said, it all depends on your workload. But given the queries we have seen from your site, 2 PB seems closer to the mark than 2 TB. :-)

A little more seriously, it's about balances. Really bad queries should be fixed. But it's not good if you have constraints like "we can only have 2 TB of tempdb" when there are legit needs for more, and developers has to go through hoops.

Sam 1,476 Reputation points

2023-10-19T08:24:26.88+00:00

Thanks sir. The database size is ~5TB,. Agree with fixing those bad queries which are eating up tempb. Just 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
Christoph Muthmann 181 Reputation points

2023-10-19T12:48:27.1633333+00:00

Hi Samantha,
maybe this link can give you an idea of using extended events for monitoring:

https://www.scarydba.com/2023/02/13/queries-impacting-tempdb/
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-10-19T21:03:12.03+00:00

Just 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?

And we are back where we started: It depends on the workload. Even if queries are well-written they may still do different things.

Beside the query load, you need to consider the version store for databases that has any form of snapshot enabled (unless they also have Accelerated Database Recovery, since in that case, the version store is in the database itself).

You will have to make some assumptions, and if those assumptions are proven to be wrong, you need to see your local hardware dealer to get more disk.

1 additional answer

Your answer

Anonymous

2023-10-19T06:59:53.7433333+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.
Sam 1,476 Reputation points

2023-10-19T08:24:26.88+00:00

Thanks sir. The database size is ~5TB,. Agree with fixing those bad queries which are eating up tempb. Just 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
Christoph Muthmann 181 Reputation points

2023-10-19T12:48:27.1633333+00:00

Hi Samantha,
maybe this link can give you an idea of using extended events for monitoring:

https://www.scarydba.com/2023/02/13/queries-impacting-tempdb/
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-10-19T21:03:12.03+00:00

Just 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?

And we are back where we started: It depends on the workload. Even if queries are well-written they may still do different things.

Beside the query load, you need to consider the version store for databases that has any form of snapshot enabled (unless they also have Accelerated Database Recovery, since in that case, the version store is in the database itself).

You will have to make some assumptions, and if those assumptions are proven to be wrong, you need to see your local hardware dealer to get more disk.

Answer 1

Olaf Helper 47,621

The required size for the TempDB heavily depends on your workload, and somehow on database / query design, which can cause heavy sort opertions in TempDB, isolation level and so on.

See tempdb database and Recommendations to reduce allocation contention in SQL Server tempdb database

Sam 1,476 Reputation points

2023-10-19T08:21:27.23+00:00

Thanks Olaf.

Share via

tempdb sizing

1 additional answer

Your answer