SQL Tempdb Sizing

This Technet link states:

"When planning for the size of the Temp database for a primary site, plan for a size that is 25% to 30% of the site database .mdf file. The actual size can be significantly smaller, or larger, and depends on the performance of the site server and the volume of incoming data over both short and long periods of time."

I'm looking for community feedback on how big the partition holding Tempdb should be? The Tempdb is for a SQL instance that hosts both the PSS site database, as well as SUSDB.

Initially reading the above Technet paragraph, I was thinking 50% of the estimated maximum size of the PSS site database (.mdf file).

However, the phrasing about 'the actual size can be significantly larger' is leaving me uncertain.

It's also worth noting that the environment in question consists of a CAS (yes, >100K clients) with several PSSs, so database replication, and its potential (if any?) impacts on PSS tempdb sizings should also be considered...

Any feedback/experiences appreciated!

January 24th, 2014 2:59am

There's no one-fits-all answer. Start with 50% and monitor the performance of the server / SQL. You can adjust the size
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 4:03am

Thanks for the response Torsten. 

The initial partition setup can impact our ability to later increase the size, so we were looking for a more definitive answer. Additionally, the disk is expensive SSD so we're trying to accurately predict what we'll need.

In your opinion, is the uncertainty relating to what client agent settings/features are enabled/disabled and frequency etc?

January 24th, 2014 4:22am

Also you should do the following when configuring TempDB:- create multiple data files (4-8 data files per CPU Core) for TempDB
- keep TempDB on completely separate drive
- consider pre-sizing files to utilize the entire disk on which the TempDB is saved, and turn off the autogrowth option. 

Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 4:22am

In your opinion, is the uncertainty relating to what client agent settings/features are enabled/disabled and frequenc

January 24th, 2014 4:56am

Thanks dekac99, we will ensure these configurations. 
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 5:08am

This is why DBAs get paid the big bucks (LOL). There simply is no way to know 100%, it's not an exact science and there are simply far too many variables involved.

Note that 4-8 files per core is overkill and will actually degrade perf though: http://social.technet.microsoft.com/Forums/sqlserver/en-US/83486801-c1b7-45b5-9957-51deb909b3cf/what-is-the-optimal-number-of-tempdb-files-on-a-8-quadcore-cpus-machine-?forum=sqlsetupandupgrade

January 24th, 2014 10:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics