My two cents

How atomic is a SELECT INTO?

2012-05-22T22:47:00Z

Last week I got an interesting situation that prompted me to challenge a long standing assumption. I always thought that a SELECT INTO was an atomic statement, i.e. it would either complete successfully or the table would not be created.

So I got very surprised when, after a “select into” query was chosen as a deadlock victim, the next execution (as the app would handle the deadlock and retry) would fail with:

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#test' in the database.

The only hypothesis we could come up was that the “create table” part of the statement was committed independently from the actual “insert”. We can confirm that by capturing the “Transaction Log” event on Profiler (filtering by SPID0). The result is that when we run:

SELECT * INTO #results FROM master.sys.objects

we get the following output on Profiler:

It is easy to see the two independent transactions. Although this behaviour was a surprise to me, it is very easy to workaround it if you feel the need (as we did in this case). You can either change it into independent “CREATE TABLE / INSERT SELECT” or you can enclose the SELECT INTO in an explicit transaction:

SET XACT_ABORT ON
BEGIN TRANSACTION
SELECT * INTO #results FROM master.sys.objects
COMMIT

Coming back from (blog) retirement

2012-05-22T21:30:54Z

So, it has been more than 3 years without a single blog post. I wished I could have a decent excuse for it, but in the end, I guess it boils down to laziness and procrastination. :-)

Even though I learned a lot in that period (and added a feel tricks to my bag), I couldn’t find the will to sit down and write. I hope all my readers (yes mom and dad, I’m talking to you) have not been disappointed.

I will try really hard not to let routine take over. I don’t expect I will be the most active blogger in the community, but hopefully a couple of posts per month is a good target to aim.

Getting all the Clustered keys in the database

2009-03-30T15:47:00Z

Jeremiah Peschka (http://twitter.com/peschkaj) asked on twitter if anyone had a script to list all clustered keys in the database. This is my take on it, I hope it helps someone: SELECT i.name , '(' + STUFF ( ( SELECT ',' + c.name + CASE WHEN is_descending_key = 0 THEN ' ASC ' ELSE ' DESC ' END FROM sys.index_columns ic JOIN sys.columns c ON ic. OBJECT_ID = c. OBJECT_ID AND ic.column_id = c.column_id WHERE i. OBJECT_ID = ic. OBJECT_ID AND i.index_id = ic.index_id AND...(read more)

Reducing Transaction Log fragmentation

2008-09-02T22:09:25Z

I am taking Kimberly Tripp and Paul Randal's course on SQL Server Performance & Availability this week in Hatfield and we had an interesting discussion on Transaction Log internal fragmentation. Kimberly summarized the issue, together with other Transaction Log best practices, here (check item 8) . Based on that discussion, and as I was bitten by this issue before, I thought it might be interesting to write a procedure to help verify and fix it. The procedure is called sp_DBA_DefragLog and...(read more)

Which queries are missing indexes?

2008-05-08T20:48:28Z

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several SQL 2000 and some SQL 2005) is the sys.dm_db_missing_index* DMVs. As the query processor evaluates queries, it detects if that specific query could benefit from an index and how much it expect that index would reduce the cost (in terms of IO), exposing these information as views that we can query. I won't delve in the structure of it, but you can use my procedure sp_dba_missingindex as an...(read more)

SQLBits

2008-03-02T21:47:24Z

Yesterday I attended the SQL Server focused conference called SQLBits , which this year took place in Birmingham. First of all, many thanks to Tony Rogerson , Simon Sabin and all organisers. You did a hell of a good job!!! As a suggestion for the next ones, I would find useful to have an indication if it is a session intended for beginners or if it will be more in-depth sessions. I got a couple of sessions "wrong" because I misjudge it from the Session title and description. In this post...(read more)

Data purity

2007-11-26T22:39:41Z

Reviewing DBCC CHECKDB syntax for SQL Server 2005, I found a [DATA_PURITY] option added to its syntax. This option enable the following checks on each column value of those datatypes: Unicode character - The data length should be a multiple of 2. Datetime - The days field should be between Jan 1 1753 and Dec 31 9999. The time field must be earlier than '11:59:59:999PM' . Real and Float - Check for existence of invalid floating point values like SNAN, QNAN, NINF, ND, PD, PINF. For a database created...(read more)

Losing your Transaction log

2007-11-20T00:17:50Z

Technorati Tags: SQL Server , Recovery Some days ago, my boss got on my desk asking if I could take a look at one database that was not opening. That DB was in a virtual machine used for our sales presentations. For some reason, several files got corrupted in that VM, and between those was our beloved transaction log file. It was a perfect opportunity to sharpen my database recovery skills, there was quite some time that I didn’t played with those tools and to be honest I had forgot almost everything...(read more)

The sound of silence

2007-09-06T22:14:01Z

This one isn't related to SQL Server. But do you know when sometimes you get stuck in an almost infinite loop of dialog boxes and for some reason you don't want to kill the application? "Sorry <beep> guys, this <beep> should <beep> end in <beep> any mome<beep>nt now <beep>." Well it happened to me these days and I decided that enough was enough and I would request the help of Saint Google! It turns out that at least on XP and Vista there is a nice registry key...(read more)

Page splits

2007-08-13T20:36:07Z

These days, investigating a fairly simple insert which was taking longer than what I would consider reasonable for that particular piece of code, I explored several possible causes. One thing that raised my attention was that the "Pages split/sec" perfmon counter was constantly above 0, so I began to wonder which index was causing most of it. I ended up reading an interesting blog entry from Greg Linwood which inspired me to write a small stored procedure to monitor it for a longer period: USE master...(read more)