You may have heard that it’s a best practice to use multiple files in SQL Server’s TempDB database– but do you understand why this is the case? Do you know how to tell when you have enough TempDB data files, and when you should add files to improve performance? In this free 30 minute webcast, Microsoft Certified Master Kendra Little will explain common causes of slow performance in TempDB and steps you can take to speed up your SQL Server. If you have one year of experience with database administration, you’ll leave the webcast armed with a new understanding of TempDB.
Want a rundown on the links in the video? Scroll down to the bottom of the post.
Helpful Links on TempDB
- Check out what our sp_Blitz script advises if it finds one tempdb data file.
- Want to monitor TempDB? Watch Jeremiah Peschka’s webcast: “How to Tell When TempDB is a Problem“
- Read Linchi Shea’s blog posts on Trace Flag 1118
- Learn how to use Adam Machanic‘s troubleshooting procedure, sp_whoisactive
Ivan Trejos August 23, 2012 | 1:05 pm
Hello I wanted to ask a question regarding your presentation of tempdb if I have a cluster with two instances with 16 logical processors the recommendation says that should create 8 data files for each instance? Is this correct
thanks excellent presentation.
Kendra Little August 23, 2012 | 1:13 pm
Hi Ivan,
Thanks for your comment! Yes, that would be a good starting point for each instance. I would definitely allocate enough storage (or make it easy to allocate) that if you need to add more files it isn’t a big deal.
Pingback: Something for the Weekend - SQL Server Links 24/08/12
Derek December 7, 2012 | 2:25 pm
How about if you have a virtualized SQL server, with 1-2 CPUs. Is there a minimum number of TempDB files you would recommend? Still stick with one per vCPU, maybe with a minimum of 2 for a 1vCPU VM?
Brent Ozar December 7, 2012 | 5:32 pm
Derek – typically I start with 4 because if you go back and up the number of vCPUs, you rarely think about changing the number of TempDB files at the same time.
Derek Seaman December 7, 2012 | 10:03 pm
Thanks! Your TechEd session and web site rock…great resources for the community!!
sapyam May 17, 2013 | 10:16 am
Hello Brent Ozar Team,
Why does TempDB need a log file. Generally, we need log file for crash recovery and point in time restoration(with backups) but none of these happen for TempDB. So, I was wondering why does TempDB need a Log File. I would apprecipate if you guys can shred some light on this. Thank you.
Kendra Little May 19, 2013 | 3:20 pm
Normally when people ask a question like this I wonder “What problem are you trying to solve?” In this case, I hope the answer isn’t that you’re trying to get rid of your tempdb’s log file.
Tempdb is used for LOTS of things, so this answer isn’t comprehensive, but I think it illustrates why the log file might be needed.
Let’s say I create a temp table with 10 rows in it:
create table #t1 (
i int identity primary key,
j char(10) default ‘foo’
)
GO
insert #t1 default values;
GO 10
Not very elegant code, I know. Now what happens if I update those rows– but then the transaction is rolled back?
BEGIN TRAN
update #t1
set j =’bar’
SELECT * FROM #t1
ROLLBACK
SELECT * FROM #t1
Will the rollback work? What values will I see there? If you run this example code against a dev environment I think you’ll get a sense of why there’s logging in tempdb and how things could get very weird if there wasn’t.
That being said, tempdb does try to minimize logging as much as possible, so it is special in some ways, but there is still logging (and for good reason).
Hope this helps!
sapyam May 19, 2013 | 4:01 pm
Ahh..I see your point…No, I am not trying to get rid of them, I was just wondering why we need them for the reasons stated above but I can clearly see what I missed.. Thank you Kendra again for pointing me the obvious..
Kendra Little May 19, 2013 | 5:26 pm
Oh, it’s not too obvious at all!
And I admit, I kind of cheated in my example. If you do the same example with a table variable (with edits to run it all in one batch), you’ll see that it behaves very differently! Rollback is handled differently for table variables (and it is pretty weird). There is still some associated logging, but it’s different with table variables and you won’t see rollbacks working the same way.
Realized after I posted my first response that I shouldn’t imply that rollbacks always work the same way in tempdb. It’s a strange world in there!
sapyam May 21, 2013 | 1:32 pm
Thank you Kendra for taking time and clarifying on this further. I was actually thinking about whether to place TempDB log file on the same drive as the TempDB data or put it on a different drive similar to how we *generally* do for User Database files.As I was thinking on this,I figured we do not use TempDB log file for either backups or crash recovery.so, whats the actual need of it??
Clearly,I did not think about Transactions logging(Commit/RollBack) and Google did not come up with any articles on why TempDB needs a log file. When Something like that happens, I know where to find the answer
..
Thank you once again.