Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?)

Version store is a new entity in SQL Server 2005. It is used to store versions of data and index rows. A row version typically is an older copy of the data or an index row and is created to support existing (triggers) and new features (snapshot based isolation levels, MARS and ONLINE index build)… Read more

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… Read more

TempDB:: Table variable vs local temporary table

As you know the tempdb is used by user applications and SQL Server alike to store transient results needed to process the workload. The objects created by users and user applications are called ‘user objects’ while the objects created by SQL Server engine as part of executing/processing the workload are called ‘internal objects’. In this… Read more

CHECKSUM and Tempdb

You may recall that starting with SQL Server 2005, you have an option available to enable CHECKSUM on the user databases.  For details, please refer to http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx.  In fact, any new database created in SQL2005 has CHECKSUM enabled automatically but it does not happen to databases that are upgraded from previous versions of SQL Server. For… Read more