One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences...

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In previous blogs, I discussed how to identify and troubleshoot IO and allocation bottleneck in TempDB. In this blog, I will describe how to indentify DDL bottleneck in TempDB and to troubleshoot it....

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In the blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx , we discussed how to identify and troubleshoot IO bottleneck in TempDB...

I hope my earlier blogs on TempDB (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx ) have given you a good background on the objects contained in TempDB and its configuration and have deployed your workload in production and it runs for ever without any glitches. Oh, really...

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows...

Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as follows · PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores...

In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-logical-structure.aspx , I described the logical/physical structure of a row in version store. A natural question that comes up is how these pages are grouped...

Now that we know a few things about Version Store, let us now look into its structure to understand how it stores rows from different tables/indexes with different schema. Interestingly, you don’t need to look far and the answer is available when you examine the DMV sys.dm_tran_version_store. ...

In order to understand the version store, let me start with an example. I will use a database that has RCSI (read committed snapshot) and SI (Snapshot Isolation) enabled as it provides more controlled environment to manage versions. When a database is enabled for RCSI/SI, any update of a row will...

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) in...

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....

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...

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...