December 2008 - Posts

• Managing TempDB in SQL Server: TempDB Basics (Version Store: logical structure)

  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 Read More... Posted Wednesday, December 31, 2008 7:03 AM by Sunil Agarwal | 1 Comments Filed under: TempDB

• Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)

  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 Read More... Posted Wednesday, December 31, 2008 2:18 AM by Sunil Agarwal | 4 Comments Filed under: TempDB

• 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 Read More... Posted Monday, December 22, 2008 3:33 AM by Sunil Agarwal | 1 Comments Filed under: TempDB

• Managing TempDB in SQL Server:TempDB Basics (cont..)

  In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx I started the discussion on the TempDB basics and this blog continues it. TempDB Basics: · As I indicated, the TempDB is created every time the SQL Read More... Posted Monday, December 22, 2008 12:21 AM by Sunil Agarwal | 2 Comments

• 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 Read More... Posted Sunday, December 21, 2008 11:06 PM by Sunil Agarwal | 2 Comments Filed under: TempDB

• DML operations on a HEAP and compression

  In my previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/20/enabling-compression-on-a-heap.aspx , I described how to enable compression on a HEAP. You can enable either ROW or PAGE compression on the heap. Let me now describe the Read More... Posted Sunday, December 21, 2008 9:16 PM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• Enabling compression on a HEAP

  I often get a question how to do enable compression on a table that is a heap (i.e. it does not have a clustered index). Clearly, one could create the clustered index with compression option set to PAGE and then drop the clustered index. This is an expensive Read More... Posted Saturday, December 20, 2008 4:44 AM by Sunil Agarwal | 1 Comments Filed under: Data Compression