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
6 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.