Transaction Log behavior

Recently I've been asked to investigate the migration possibilities of our Reporting Services environment to SQL Server 2005. Up until now everything has been really smooth to migrate and no real issues have come out of the process.

Yesterday I noticed that our BULK INSERT tasks were taking a lot longer as opposed to the SQL Server 2000 runtime. I am testing this on the same server with the exact same import files and database settings so there has to be a reason why it is taking twice as long as before. Off to investigate!

While checking all possible bottlenecks I came across a big difference and this was the Transaction Log size. While it is sized at 2GB and remains that size on the 2000 instance (Simple Recovery) it grows to 12GB on the 2005 instance.

Adding an explicit TABLOCK to the BULK INSERT statement seems to help. I admit we should have included this in the first place :-) Minimally logged operations do have some prerequisites and this is one of them. Others are that the table is not being replicated, simple or bulk logged recovery model and there are some index restrictions you can check in the books online.

However, this does not explain the difference between the two versions as those rules apply to both versions.

More to follow!

Non-unique clustered index rebuild in SQL Server 2005

One of the big disadvantages of SQL Server 2000 non-unique clustered indexes was the fact that SQL Server generated a new uniqueifier whenever you would rebuild your index. Although the general guideline remains to create narrow, unique and ever-increasing clustered indexes SQL Server 2005 has a nice improvement over SQL Server 2000.

SQL Server 2005 no longer changes the uniqueifier when you rebuild it which is great news! You now have more control over when you want to rebuild your non-clustered indexes if your table has a non-unique clustered index. This is true for ALTER INDEX, DBCC DBREINDEX as well as CREATE INDEX WITH DROP_EXISTING.

Here is a little script to show this behavior.
I check the STATS_DATE because whenever your indexes are being rebuilt your statistics will be updated with a fullscan.

CREATE TABLE myUniqueifierTest

(myID int,

myChar char(200)

)

GO

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('X', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Y', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Z', 200))

GO

CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID)

GO

CREATE NONCLUSTERED INDEX myNCI ON myUniqueifierTest (myChar)

GO

WAITFOR DELAY '00:00:03'

ALTER INDEX myCI ON myUniqueifierTest

REBUILD

GO

--DBCC DBREINDEX(myUniqueifierTest, myCI)

--CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID) WITH DROP_EXISTING

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 1 )

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 2 )

GO

DROP TABLE myUniqueifierTest