How many files must a TempDB have before it’s allowed to be fast? How many table variables must a server walk down before it’s considered a table? The answer, my friend, is blowin’ in the wind, and Bob Dylan will share it with you in this 25-minute video – hopefully you can understand him.
Hi! I’m Brent Ozar.
I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.
Want to advertise here and reach my savvy readers?
8 Comments. Leave new
This was great, Brent. Very well done and great advice.
Good stuff as always. I have a quetion on this topic.
I have one tempdb data file on its dedicated drive (1.2TB) and seeing too many ioread and iowrtite stalls, is it a good idea to split one into four equal fixed size data files as recommended by microsoft. The server has 40 cores. Our SAN guy says adding additonal files will not make any diffrence and it may deteiorate performance as the number of spindles is same. I am not seeing any PAGELATCH errors other than CXPACKET erroros. I have MAX DOP = 8
Any suggestions
Thanks
Sekhar – custom advice for one specific server is a little beyond what I can tackle here in a blog comment. If you’d like advice on your server’s setup, please feel free to click Contact at the top of the page and we’d be glad to take a look at the server as part of our Critical Care sessions. Thanks!
Hi Brent, thanks for this presentation. I’m surprised to hear about running out of disk space causing database corruption. This seems like a bug in that it violates atomicity.
Good info, as always, Brent. I’m seriously impressed you made it that long doing the voice. However, I feel you should have went with the title “Here comes the story of the TempDB”.
When you get asked questions about tempdb, point them to this video! This was great! I just referred a couple of friends to watch it just for the humor!
Seriously though, auto-grow? On my production boxes, I’m beginning to set all them with no autogrowth. The reason is I go back a couple of weeks later and they’re not growing all at the same size. I have to reset the sizes and this becomes a cat-and-mouse game. I’d rather just turn the autogrow feature off and figure out who or what is causing the growth. My other developer DBA coworker said he burned through 20GB of tempdb but he knew he was going to do this…and it was production. However, I replied that its a one-time issue and probably could have used staging tables rather than tempdb.
Are you saying that tempdb is going to grow evenly if I set the autogrowth feature on?
I would rather size correctly and no autogrowth for me. Or at least 4 – 1024MB files…I don’t have the luxury of having tempdb on a different drive, but I’ll never run out of space either.
One other point you made in the video is VMs. I was thinking 4 tempdb files on VMs? Then it made sense though if I could have a “dedicated” space for tempdb (although its all shared anyway right?)
Your thoughts?
Andy
Andy – sorry about the delay on this. For questions, head on over to a question & answer site like https://dba.stackexchange.com. (Sometimes it’s tough for bloggers to catch every question – sorry about that!)
[…] when it comes time to allocate pages in tempdb. Paul Randal has provided a solid explanation. “Bob Dylan” has provided a more entertaining […]