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.

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?

    Reply
    • 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.

      Reply
  • 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.

    Reply
  • 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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu