My two cents

How atomic is a SELECT INTO?

leo.pasta — Tue, 22 May 2012 22:47:00 GMT

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

leo.pasta — Tue, 22 May 2012 21:30:54 GMT

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

leo.pasta — Mon, 30 Mar 2009 15:47:00 GMT

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...(read more)

Reducing Transaction Log fragmentation

leo.pasta — Tue, 02 Sep 2008 22:09:25 GMT

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...(read more)

Which queries are missing indexes?

leo.pasta — Thu, 08 May 2008 20:48:28 GMT

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...(read more)

SQLBits

leo.pasta — Sun, 02 Mar 2008 21:47:24 GMT

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...(read more)

Data purity

leo.pasta — Mon, 26 Nov 2007 22:39:41 GMT

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...(read more)

Losing your Transaction log

leo.pasta — Tue, 20 Nov 2007 00:17:50 GMT

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...(read more)

The sound of silence

leo.pasta — Thu, 06 Sep 2007 22:14:01 GMT

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...(read more)

Page splits

leo.pasta — Mon, 13 Aug 2007 20:36:07 GMT

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...(read more)