Configuration: TempDB Files and Sizes
TempDB is a special system database that gets used a LOT behind the scenes, and it needs different configuration than other databases. You’ll learn why it’s a performance bottleneck, why 4-8 data files alleviate the issue, show how to reconfigure an existing SQL Server, and see the pros and cons of different storage options for it.
- Backups 1: 3 Common Strategies
- Backups 2: Restores
- Backups 3: Setting Up Maintenance Plans
- Backups 4: Setting Up Ola Hallengren’s Maintenance Scripts
- Backups: Reading from Databases During Restores
- Configuration: Anti-Virus
- Configuration: Instant File Initialization
- Configuration: Lock Pages in Memory (LPIM)
- Configuration: Prepare for Emergencies with the Remote DAC
- Configuration: Sending Emails with Database Mail
- Configuration: sp_configure Settings
- Corruption 1: How it Happens, and How to Detect It
- Corruption 2: DBCC CHECKDB for VLDBs
- Maintenance: Agent Jobs
- Maintenance: Patches: Which Ones to Apply, When, and How
- Maintenance: Shrinking Files
12 Comments. Leave new
Thank you for helping demystify the tempdb!
I noticed in the server setup documentation y’all wrote, it states to have 1 temp file per logical core. I have boxes that only have 1 logical but higher virtual cores; any advice for configuring these?
Also, is there a best practice for what size to grow your temp db’s to? I believe you had eight at 1028mb files if I’m not mistaken; where did you come up with 1028mb?
JLochbaum – I’m not sure what you mean about “1 logical but higher virtual cores” – can you elaborate? A logical processor is exactly the same as a virtual core, but I’m guessing something got lost in translation.
For initial sizes, I generally start with a total TempDB size at 10-25% of the size of data on the server. For example, if you have 1TB of total databases on the server, it’s not unusual to see 100GB-250GB total TempDB size.
Sure Brent,
I mean if I look at system info or use a reporting tool that lists processors, I get returns for physical,logical and total cpu processors. For exp: 1 logical, 1 physical, 4 total so it’s a quad core. I inherited several of these boxes that are dual and quad core but a lot of them don’t have multiple temp files.
Just wondering if I should leave them alone or, in my example, quarter off the temp file, one for each processor.
Ah, I think you might be mixing up sockets and cores. It sounds like you’ve got 1 physical quad-core CPU. That’s got 4 logical processors. Every core is a logical processor.
Erik also brings up a good point – you might have a misconfigured VM with 4 single-core processors, too. Best to post the question with details at https://dba.stackexchange.com including screenshots of your setup. (It’s hard to post screenshots here.)
Yeah sorry, I didn’t intend for it to turn into misconfigured VM/cores/sockets conversation 🙁
Cool, that answers question about temp files; I’ll post on stack exchange about the core debacle. I thought physical+logical would = total BUT I’m seeing 1+1 = 4.
Anyways, thanks again to both of you.
If we run the following command: DBCC CHECKDB (‘PrimaryDB’) WITH ESTIMATEONLY, should we size our Temp DB based on this result? I ran this against our primary DB (550 GB) and it told me we need 196 GB to run CHECKDB command. Does this indicate that we should have about 200 GB of Temp DB space? That means we would have Temp DB at about 36% of the database size. Thanks Brent!
No, I don’t recommend that. To learn my formula for TempDB sizing, check out my Fundamentals of TempDB class: https://training.brentozar.com/p/fundamentals-of-tempdb
I saw your video that gave a loose recommendation of 25%, although another example seemed to indicate a range between 10% and 25%. I may check out your video, but I’m actually trying to solve an issue. Our current DB is only sized with a 40 GB Temp DB (for a 550 GB User DB) and I think that is too small. We are using 8 separate files for the Temp DB, each sized at 5 GB. They are not set to auto-size, they are fixed at 5 GB. We get the out of space error on Temp DB on occasion.
OK, cool. If you’re trying to solve a production issue, blog post comments probably aren’t a great solution – that’s where consulting comes in. Hope that’s fair!
We had a consultant come in a while ago, but they were not able to identify the issue. We obviously did not have the right consultant…oh where oh where can we find the right consultant…? Perhaps in the land of awakened volcanoes…? 😉
😉