Interesting SqlException

Working with a ReST API that uses Linq to SQL for a DAL.

We have a method that calls a stored procedure that does various SQL statements and does it in a transaction that it either commits or rolls back.

If we called this method 5 times in a row, it would “hang” the api for subsequent calls that did multiple queries. Any call that traversed a relationship and queried the child table would throw this SQL exception:

The server failed to resume the transaction. Desc:#########

The proc that was called returns a table of errors (if any). Our API was never consuming this table.

Thanks to this blog entry, I was enlightened into the need to consume that return table.

Once I consumed the return table, the connections stopped being “stuck” with a transaction context.

Dedicated Administrator Connection on SQL Server Express 2008

Putting this here for future reference.
There are a number of things required to make a successful DAC connection on SQL Server express 2008:

1) add the trace flag(-T7806) to the startup parameters and restart the service.

2) Use “new database engine query” instead of “new query” or “connect”

3) Make sure the browser service is running

4) Stick “admin:” in front of the servername\instance when you connect.

References:

http://stackoverflow.com/questions/7670636/how-to-decrypt-stored-procedure-in-sql-server-2008

http://zarez.net/?p=883

http://chopeen.blogspot.com/2006/08/dedicated-administrator-connections-are.html

Static SQL With Dynamic Where Clause

I’ve been using this sort of syntax for a couple of years now to write dynamic where clauses:

SELECT
Col1
FROM
dbo.Table
WHERE
@param IS NULL OR Col1 = @param

Recent application timeouts at my company have us investigating SQL queries for performance issues. One of the things I have discovered is that queries like that one are prone to performance problems. That query example on a table that has 6800 rows in it caused 3869 reads when a the column being compared has an index, and the parameter was not null (query returned 1 row in 253 ms).

For whatever reason, rewriting the query like this:
SELECT
Col1
FROM
dbo.Table
WHERE
(Col1 = @param AND @param IS NOT NULL) OR (@param IS NULL)

The query only causes 106 reads and returns the same single row in 33 ms. I don’t pretend to understand why the query that looks worse and has a more complicated execution plan performs better, but it does. I got the idea to try it from this article

The degree to which the syntax difference helps depends on how the table is indexed, and the column involved. Sometimes that syntax change makes no difference at all. The same article also showed me another trick that works even better for performance, but only works for non-nullable columns and is more complicated because you have to provide appropriate @min and @max values, which may be difficult depending on the data type and data itself:

SELECT
Col1
FROM
dbo.Table
WHERE
Col BETWEEN COALESCE(@param, @min) AND COALESCE(@param, @max)