次の方法で共有

Managing TempDB in SQL Server

  • [アーティクル]

Over past few of years, I have given TempDB presentation in many conferences and the feedback that I have received on TempDB has been very useful. Most of you understand that TempDB is a critical database that needs to be configured carefully otherwise the performance of your application may suffer. I am not suggesting that the current implementation of TempDB addresses the challenges of every application but SQL Server has made significant improvements starting with SQL Server 2005. Given there is wide level of interest in TempDB, I decided that I will publish a series of blogs to cover TempDB basics, its monitoring and troubleshooting. I encourage you to refer to a popular white paper https://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx that I co-authored with owners from development and test teams.

TempDB Basics:

 

TempDB is like any other user database in that it is created using the specifications in Model Database and can be used to create/query tables but unlike its user database counterparts, it is created every time the SQL Server starts. This is significant difference with following implications

·         It does not need to be recovered. So there is no reason for TempDB to be running under FULL or BULK LOGGED recovery models. In fact, the TempDB is configured to be under SIMPLE recovery model and it cannot be changed.

·         Though the TempDB does not need to be recovered, it does not mean that operations on objects in TempDB need no logging. Now here comes the tricky part. Each operation in SQL Server is done inside an explicit or implicit (each statement is encapsulated by a transaction) and that includes operations on objects in TempDB. Let us say update a row in a global temporary table and now you rollback the transaction, there will be no way to undo this change unless we have logged the ‘before value’ of the update. However, there is no need to log the ‘after value’ of the update because this value is only needed when the transaction needs to be ‘redone’ which happens during database recovery. As I indicated earlier, the TempDB is not recovered, there is no reason to LOG the ‘after value’ of the update. As another example, let us consider Insert operation. When you insert a row in say a global temporary table, the actual ‘row value’ is not logged because SQL Server does not need the ‘row’ to undo except it needs to set the offsets within the page appropriately or if this insert had caused a new page to be allocate, de-allocate the page. So key point is, we only log ‘undo’ information but not ‘redo’.   This change on logging is available starting with SQL Server 2005 but at this time, these are supported only on a HEAP. To make the matter further complicated, not all objects in TempDB are subject to logging. I will explain this in more detail later. Let us consider one example to illustrate the point that I just madetempdb-basic-1

Now, just to prove that logging is different in user database, I will use the same example as above but with user table, here the log records that I get tempdb-basic-2

Thanks
Sunil Agarwal

Comments