Connectivity problems with SQL Server 2005?

Check out this great series of posts

DELETE WITH TABLOCKX - Follow up

After some playing around with DBCC PAGE, DBCC EXTENTINFO I came to the conclusion that it is indeed the deallocation that causes the process to take page locks (and extent locks when a complete extent is released).

From Inside SQL Server 2000

When the last row is deleted from a data page, the entire page is deallocated. (If the page is the only one remaining in the table, it isn't deallocated. A table always contains at least one page, even if it's empty.) This also results in the deletion of the row in the index page that pointed to the old data page. Index pages are deallocated if an index row is deleted (which, again, might occur as part of a delete/insert update strategy), leaving only one entry in the index page. That entry is moved to its neighboring page, and then the empty page is deallocated.

So I suppose the spid that does the deletes also deallocates the pages and extents and not the asynchronous process that scans for ghosted records

SQL Server 2005 system tables diagram

I read on SQL Server Code about this great system tables diagram.

Download the system tables diagram for SQL Server 2005

DELETE WITH TABLOCKX

Recently I was playing around with one of our procedures that we use for our reporting solution. One of the requirements was the ability to regenerate data for a certain date. In order to guarantee this we delete the records for a specific day before filling the denormalized tables with data. Because there are a lot of records for 1 day for some reports and we only fill them during the night when there is no activity I thought it would be a good idea to use WITH TABLOCKX because the finer the granularity the more work SQL Server has with managing the locks.

I accidentally came across some strange behavior (imho) when checking the actual locks taken by the process. It seems SQL Server is switching to row and page locks and escalating to table locks at some point even though I hardcoded WITH (TABLOCKX) in the DELETE statement.

Here you can find a little reproduction script:

The funny thing is that the switch to page and row locks is only happening as of a certain number of records (possibly because of the time it takes so some process finishes?)

SET NOCOUNT ON

IF NOT OBJECT_ID('tbltablockTest') IS NULL
DROP TABLE tblTablockTest
GO

CREATE TABLE tblTablockTest
(ID int,
myDate smalldatetime,
myText varchar(1000)
)

GO

DECLARE @i int

SET @i = 1

WHILE @i < 1025
BEGIN
INSERT INTO tblTablockTest
VALUES (@i, getdate(), REPLICATE(CAST(@i as varchar), 1000 / LEN(@i)))

SET @i = @i + 1
END

GO

CREATE CLUSTERED INDEX ixID ON tblTablockTest (ID)
GO


DBCC TRACEON (3604, 1200) --1200 shows detailed information about locking
BEGIN TRAN

DELETE FROM tblTablockTest WITH (TABLOCKX)
WHERE ID BETWEEN 1 AND 15 -- increase the 15 if the behaviour is not showing

ROLLBACK
DBCC TRACEOFF (3604, 1200)


I haven't figured out why this is happening yet but I'm on a mission ;-) One of my guesses is that it has something to do with page deallocation but I haven't got a good explanation yet.

SQL Trivia

Got this nice little pop quiz at work (which you are obviously bound to answer wrong).

Given the next SQL Script:

What would the output of the SELECT be?
Now my first reaction was obviously 3, 2, 1 and this would be correct on SQL Server 2005 but unfortunately SQL Server 2000 gives you 1, 2, 3.

Quite frankly I prefer the SQL Server 2005 method anyway ;-)

I lost :-(

Overwrite PDF with Reporting Services through SOAP

I had this really strange issue with one of my reports in development. Obviously developing a report is not something you do in one run. Because of this nice bug in Reporting Services I am only able to check the output by generating the PDF through the Reporting Services webservice.

I was getting this funny behavior of PDF files being correct in size and even page numbers but all the pages were empty. When you eventually scrolled through the PDF it would crash saying that the document is invalid.

Apparently the problem is being caused by overwriting the PDF from the .NET application. Deleting the file before creating it seems to solve this issue.

*EDIT*
Aha, found the nasty bugger :-(

FileStream fs = new FileStream(path, FileMode.OpenOrCreate);
instead of
FileStream fs = new FileStream(path, FileMode.Create);

Developers *sigh* :-D

SQL Server Management Studio - Faster Start

Check out this post for more information about the command line parameters for SSMS.

Adding the nosplash parameter really speeds up starting Management Studio. Just add -nosplash to your SSMS shortcut.

How's that for an easy gain!