Optimal size for a tempdb in production environment

Question

Optimal size for a tempdb in production environment

Pyman

SSChampion

Points: 13830
More actions
October 8, 2011 at 2:44 pm

#258652

Hi,
I have few databases in my production SQL Server. I am using SQL Server 2008 Standard Edition SP1 X64 bit. The size of all the databases together is 335GB. There are lot of sort operations and calculations that are being performed on the databases. The current size of the tempdb is 3131.06 MB and the currently allocated log size is 0.5 MB with available free space as -65.56 MB (-13112%).
what is the Optimal size for a tempdb in production environment?
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Sapen (10/8/2011)
Hi,
I have few databases in my production SQL Server. I am using SQL Server 2008 Standard Edition SP1 X64 bit. The size of all the databases together is 335GB. There are lot of sort operations and calculations that are being performed on the databases. The current size of the tempdb is 3131.06 MB and the currently allocated log size is 0.5 MB with available free space as -65.56 MB (-13112%).
what is the Optimal size for a tempdb in production environment?
Thanks

If 3.1 GB (3131.06 MB) is the size it's grown to as you say, then that's pretty much the optimal size. In your situation, I'd be tempted to set its initial size to 4GB with a growth of 200 MB. There are tricks you can play with multiple files across multiple spindles to optimize the performance a bit, but the size it's grown to is pretty much all that it needs. The extra 0.9 GB in my recommendation over the current size is to allow for future growth without having to worry about fragmentation.

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I agree with Jeff. There's no optimal size. There's the size that is needed for the workload that is running. Whatever concurrent operations are running that need temp space need to have space in tempdb to complete.

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

Gail Shaw and Paul Randall might disagree with both of us, Steve... something about the underlying allocation units and all that. But, I think they also might agree that an initial size of 4GB is much better than the default size and growth to 3.1 GB. 🙂

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Jeff Moden (10/9/2011)
Gail Shaw and Paul Randall might disagree with both of us, Steve... something about the underlying allocation units and all that. But, I think they also might agree that an initial size of 4GB is much better than the default size and growth to 3.1 GB. 🙂

I'm in agreement. Taking out of the picture the overhead needed to extend Tempdb is a no brainer and seams to be the sensible thing to do.

Having said that, this kind of overhead would most probably go unnoticed in a small to medium size/activity database. Usually, avoiding this kind of overhead helps on high end environments.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.