Ok - so we did more partying than we thought so blog posts have been a little sparse this month, but here's one to end off the year.

There's a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. Fact or fiction? Let's find out.

First I'll create a test database, containing a small table with a clustered index and few rows:

CREATE DATABASE SItest;
GO

USE SItest;
GO

CREATE TABLE SmallTable (c1 INT, c2 INT);
CREATE CLUSTERED INDEX SmallTableCI ON SmallTable (c1);
GO

INSERT INTO SmallTable VALUES (1, 1);
INSERT INTO SmallTable VALUES (2,2);
GO

Next I'll turn on READ_COMMITTED_SNAPSHOT and rebuild the index to see if statement level versioning does the trick:

ALTER DATABASE SItest SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO

Now let's look at the data page holding the two rows to see if there's any versioning info (the output is snipped short a little for brevity):

DBCC IND (SItest, SmallTable, 1);
GO

DBCC TRACEON (3604); -- remember this makes the output go to the console
DBCC PAGE (SItest, 1, 153, 3);
GO

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
------- ----------- ------ ----------- ----------- -----------
1       154         NULL   NULL        2073058421  1
1       153         1      154         2073058421  1

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:153)

<SNIP SNIP SNIP>

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060

00000000:   10000c00 01000000 01000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0x6f Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F

00000000:   10000c00 02000000 02000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nope - both the rows look normal. Now for completeness let's try transaction level versioning and a rebuild:

ALTER DATABASE SItest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO

DBCC IND (SItest, SmallTable, 1);
GO

DBCC PAGE (SItest, 1, 143, 3); -- page changed when we rebuilt the index
GO

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
------- ----------- ------ ----------- ----------- -----------
1       152         NULL   NULL        2073058421  1
1       143         1      152         2073058421  1

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:143)

<SNIP SNIP SNIP>

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060

00000000:   10000c00 01000000 01000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0x6f Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F

00000000:   10000c00 02000000 02000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And in another window:

SELECT * FROM smalltable;
GO

c1          c2
----------- -----------
1           1
2           2

(2 row(s) affected)

Cool - so the original values are still there. Let's see the versioning info on the data page (output snipped again):

DBCC PAGE (SItest, 1, 143, 3);
GO

PAGE: (1:143)

<SNIP SNIP SNIP>

Slot 0 Offset 0x7e Length 29

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC07E

00000000:   5c000c00 01000000 01000000 0300f9e0 \...............
00000010:   00000001 00000057 03000000 00       .......W.....

Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 0)

UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0xb8 Length 29

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC0B8

00000000:   5c000c00 02000000 02000000 0300f9e0 \...............
00000010:   00000001 00010057 03000000 00       .......W.....

Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 1)

UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

Slot 2 Offset 0x9b Length 29

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC09B

00000000:   50000c00 04000000 01000000 0300f800 P...............
00000010:   00000000 00000057 03000000 00       .......W.....

Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null

UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

c1 = 4

Slot 2 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 3 Offset 0xd5 Length 37

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO

Memory Dump @0x61CEC0D5

00000000:   70000c00 04000000 02000000 0300f801 p...............
00000010:   00170001 00000000 00000000 00000057 ...............W
00000020:   03000000 00                         .....

Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null

Slot 3 Column 0 Offset 0x13 Length 4

UNIQUIFIER = 1

Slot 3 Column 1 Offset 0x4 Length 4

c1 = 4

Slot 3 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've marked the versioning parts in bold. Notice that the old records have been turned into ghost records too. The second record is now tagged as having variable-length columns too. This is because I updated both records to have the same clustering key value and so the second record now needs a uniquifier - which is stored as a variable-length column.

So, the original statement is a myth - the only time that rows get versioning info added to them is when it's needed to support a versioning operation.

In the mail today I received notice that my first software patent has been granted by the US Patent Office (after being filed 3.5 years ago while I was still writing DBCC CHECKDB code!) It's basically a way to run DBCC CHECKDB on a database stored in a backup without actually having to restore the whole backup. This is really cool for people with VVVVLDBs as it means you don't need to restore the whole backup to verify that the database stored within it is valid. Anyway - I'm pretty pleased!! I hope the SQL team gets around to implementing it at some point in the future.

If you're interested, you can read it here (warning: some of the legalese is pretty dry...)

Next - some people have been 'complaining' that we haven't been posting recently - we took a break last week to grab some winter sun and some total downtime but we'll be back into blogging in between parties over the holidays.

Finally - to all of you who've followed my blog (the old MS one and my new one here), attended any of our conference sessions or workshops, sent us interesting questions, and just generally been part of the SQL community I love - THANKS!! I hope you and your families have a great Festive Season (whatever you celebrate) and a prosperous New Year!

Best wishes - Paul.

While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I've tagged this with!) and have a bunch of laughs along the way - check it out here.

PS There's been a ton of interest in the slide deck idea I had so we'll be going ahead with that. Look for an announcement sometime in the first few months of next year about how to get them. Thanks to everyone that replied!

One of the cool features added in SQL Server 2005 for scaling-out a workload was peer-to-peer replication. The major drawback was that to change an existing peer-to-peer topology, the entire topology had to be quiesced. In SQL Servr 2008, the Configure Peer-To-Peer Topology Wizard in Management Studio has undergone a major face-lift and a peer-to-peer topology can be altered ONLINE - very cool!

To get to the wizard, you still need to go through the clunkiness of enabling peer-to-peer subscriptions in the Subscription Options pane of the Publication Properties of a new publication (by right-clicking the publication under the Replication->Local Publications folder in Object Explorer):

Once that's set to True, you can right-click the publication and you'll see a Configure Peer-To-Peer Topology option which will bring up the new wizard. Here's what you'll see with only a single node configured - I've hovered the mouse over the node to get the tool-tip to show up:

By right-clicking anywhere on the design surface you get a menu, from which you can select to Add a New Peer Node. Of course the node you select has to already have been setup for replication otherwise you'll get an error. As long as replication is already setup, you'll get the Add a New Peer Node wizard:

You need to set the Peer Originator ID to a number that isn't already being used by another node in the topology - it would be nice if the wizard would default to an ID it knows isn't being used instead of 1.  You then decide whether to have peer-to-peer connections with all the other nodes in the topology automatically setup. If you don't check that option, the node will appear on the topology viewer, but with no connections, like below (again I've brought up the tool-tip so you can see it's a different node than the first one - in this case a different instance inside a VPC):

If you don't check the option, you can create connections manually by simply right-clicking either node and selecting Add a New Peer Connection. You'll see a rubber-banding arrow that you pull to the node you want to connect to. I tested the automatic method of creating the connections and it works nicely too. Here's a three node topology in the viewer:

The rest of the wizard is as before - setting the Log Reader Agent and Distributor Agent security settings and how to initialize the new peer. At any point you can go back into the wizard and add or remove connections or peers. I'm pretty impressed!

Every time we're at a conference, we get asked whether we're going to write any books on the kinds of things we talk about. Well, at present the answer is no - we just don't have time unfortunately. However, I have a plan that I'd like to gauge interest in to see whether it's worthwhile.

There are two full-day workshops that Kimberly and I taught this year, Database Maintenance: From Planning to Practice to Post-Mortem and Disaster Recovery: From Planning to Practice to Post-Mortem. Each of these has 100+ slides in. My plan is to take each of these slide decks, add comprehensive notes for each slide and make printouts of the complete deck, one-slide plus notes per-page available for ordering for US $99.99 each workshop. There would also be a section on our website with relevant whitepaper links, KB article links, and blog post links ordered by slide number in the deck. Additionally, as we'd be shipping stuff to you anyway, we'll throw in a free copy of the AlwaysOn DVD and the Manageability DVD, with around 20 hours of self-paced labs on them. Shipping and handling would be included for domestic US orders, and US $10 extra for non-US orders.

If there's enough interest (50-100 people) then I'll go forward and make these available for ordering in January/February 2008. So - if you're interested in this (with no obligation) please drop me a mail.

Thanks!

This is a question I was sent a week or so ago - if a table is truncated inside a transaction, what protects the integrity of the table's pages in case the transaction rolls back? Let's find out.

First off I'll create a simple table to experiment with.

CREATE TABLE TruncateTest (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'A');
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 20)
BEGIN

INSERT INTO TruncateTest DEFAULT VALUES;
SELECT @a = @a + 1;

END;
GO

We can see what pages and extents are allocated to the table using the undocumented DBCC IND command:

DBCC IND (test, TruncateTest, 0);
GO

PageFID PagePID
------- ---------
1       193
1       192
1       194
1       195
1       196
1       197
1       198
1       199
1       200
1       224
1       225
1       226
1       227
1       228
1       229
1       230
1       231
1       232
1       233
1       234

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've curtailed the output to just the page IDs and we can see that there are 4 extents used by this table (starting on pages (1:192), (1:200), (1:224), and (1:232)). Now if we truncate the table in a transaction, what will DBCC IND show?

BEGIN TRAN;
GO

TRUNCATE TABLE TruncateTest;
GO

DBCC IND (test, TruncateTest, 0);
GO

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Looks like there are no pages allocated to the table. So where are they? Let's check what locks there are. Instead of using sp_lock, I'm going to use it's replacement DMV, sys.dm_tran_locks:

SELECT resource_type, resource_description, request_mode FROM sys.dm_tran_locks WHERE resource_type IN ('EXTENT', 'PAGE');
GO

resource_type   resource_description   request_mode
--------------- ---------------------- --------------
EXTENT          1:200                  X
PAGE            1:198                  X
PAGE            1:199                  X
PAGE            1:196                  X
PAGE            1:197                  X
PAGE            1:194                  X
PAGE            1:195                  X
PAGE            1:192                  X
PAGE            1:193                  X
EXTENT          1:192                  X
PAGE            1:200                  X
EXTENT          1:232                  X
EXTENT          1:224                  X

Ah - all the pages and extents are locked. The table doesn't show them as allocated any more but because they're exclusively locked, the allocation subsystem can't really deallocate them until the locks are dropped (when the transaction commits). That's the answer - they can't be reused until they're really deallocated. If a transaction rollback happens, the pages are just marked as allocated again.

A quickie today to get back into the swing of things.

In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table - see this previous post for a few more details). She recommends that the constraints are extended rather than dropped and recreated - which I totally agree with. I had a question about why this is a best practice, and is it more efficient than dropping and recreating the constraints?

Let's create a little example to illustrate all these points. A simple table called Sales with a couple of indexes and 100000 rows of data.

CREATE TABLE Sales (salesID INT IDENTITY, SalesDate DATETIME);
GO

CREATE CLUSTERED INDEX Sales_CL ON Sales (SalesID);
CREATE NONCLUSTERED INDEX Sales_NCL ON Sales (SalesDate);
GO

SET NOCOUNT ON;
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100000)
BEGIN

INSERT INTO Sales VALUES (GETDATE ());
SELECT @a = @a + 1;

END;
GO

Now I want to create two constraints - for the lower and upper bounds of the sales date. I could do this using a single constraint with both conditions or two constraints with a single condition each. For simplicity I'll use one constraint, but first I want to see how expensive the operation is, so I'm going to turn on STATISTICS IO - this is a very cool feature that gives the IO costs of a query after it's completed.

SET STATISTICS IO ON;
GO

ALTER TABLE Sales WITH CHECK ADD CONSTRAINT [CK_Sales_SalesDate] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2008);
GO

Table 'Sales'. Scan count 1, logical reads 399, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The 399 logical reads are for the table scan that's done to ensure that the constraint is valid for the data currently in the table.

So - the first question is why not drop/create the constraint to update it? Well, what if invalid data is entered into the table between dropping and recreating the constraint?

ALTER TABLE Sales DROP CONSTRAINT [CK_Sales_SalesDate];
GO

ALTER TABLE Sales WITH CHECK ADD CONSTRAINT [CK_Sales_SalesDate] CHECK (YEAR (SalesDate) >= 2005 AND YEAR (SalesDate) < 2009);
GO

Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the CHECK constraint "CK_Sales_SalesDate". The conflict occurred in database "ConstraintTest", table "dbo.Sales", column 'SalesDate'.

As many of you who follow our blogs (and we've met at conferences) may know, Kimberly's Dad has been very ill for some time. On November 28th he passed away peacefully at home with us after a long and brave fight against cancer. He'll be missed by many friends and family. Kimberly has a longer blog post from us here.

Thanks