Year: 2008

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)

by Sunil Agarwal // 3 Comments

  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 together and how does SQL Server garbage collect these rows. You may recall that if you need to DELETE a large number of rows from… Read more

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

by Sunil Agarwal // 2 Comments

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. This DMV shows the full logical structure of… Read more

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

by Sunil Agarwal // 6 Comments

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 create a row… Read more

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

by Sunil Agarwal // 2 Comments

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:TempDB Basics (cont..)

by Sunil Agarwal // 2 Comments

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 Server is started, it is tempting let it grow starting with the size initially set in the Model Database. While this works, it… Read more

Managing TempDB in SQL Server

by Sunil Agarwal // 2 Comments

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

DML operations on a HEAP and compression

by Sunil Agarwal // 3 Comments

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 implications of DML operations on a heap. ROW compression: No issues. The updated/inserted data stays ROW compressed though out its life time. PAGE Compression: There… Read more

Enabling compression on a HEAP

by Sunil Agarwal // 6 Comments

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 operation because ·         Creating a clustered index… Read more

Does data compression lead to more fragmentation?

by Sunil Agarwal // 1 Comments

Lately I have been asked how data compression impacts fragmentation (i.e. does it cause more or less fragmentation?).  I believe this question is best answered by looking at how does fragmentation occur in the first place and then analyze each of these cases for compressed and uncompressed data. Let us start off with create clustered index on a table… Read more