I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you.
What is TempDB?
TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn’t fit in memory. It is always database_id number two in sys.databases.
It’s non-durable, meaning that the database is recreated every time SQL Service restarts. A new set of data and log files are recreated each time. So, what does this mean to you? First of all, you shouldn’t put any objects in the TempDB database that you need to be persisted. Don’t use TempDB as a development database (except for maybe a quick throwaway test), because once services restart, you will lose your work.
TempDB works in a round-robin fashion known as proportional fill; it writes data in all files based on free space in each file and alternates between files to find the best place to do its work. I’ll talk more about this later in the article…..
Read the full article here at Red-Gate’s Simple Talk
