Alexander Kuznetsov

Avoiding nested transactions might not improve performance.

Alexander Kuznetsov — Wed, 08 Feb 2012 18:09:00 GMT

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern: BEGIN TRAN ; -- (snip) COMMIT ; or a more complex one: DECLARE @trancount INT...(read more)

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them,...(read more)

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment...(read more)

Yet another gotcha: variables' scopes do not end where they should.

Alexander Kuznetsov — Wed, 25 Jan 2012 22:09:00 GMT

Be careful: unlike most other languages, T-SQL does not limit variables' scope to the block where the variable has been defined. For example, the following snippet compiles and runs: -- @to is not in scope yet -- the line below would not compile --SET...(read more)

Running OLTPish system without deadlocks, and loving it.

Alexander Kuznetsov — Wed, 04 Jan 2012 22:31:00 GMT

Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it. I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might...(read more)

Refactoring large live OLTP tables without downtime

Alexander Kuznetsov — Tue, 03 Jan 2012 18:11:00 GMT

Refactoring tables does not have to be such a big deal. We do not have to shut modifications down, migrate all data into new structure, deploy modified modules, and do it all at once, while the system is down. Doing all migration at once may be risky,...(read more)

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save. False Negatives The following tables implement a very common type/subtype...(read more)

Preventing Bob from sales from selecting other people's messages

Alexander Kuznetsov — Thu, 18 Aug 2011 19:22:00 GMT

Suppose that Bob can retrieve all messages, as follows, and we are not happy with it: EXEC Exchange.ShowAllMessages ; Suppose that we don't want him to read anything related to messages, so our knee-jerk reaction is this: REVOKE EXECUTE ON Exchange.ShowAllMessages...(read more)

Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

Alexander Kuznetsov — Wed, 03 Aug 2011 01:33:00 GMT

When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts. At the time of this writing MSDN clearly states the following:...(read more)

Entering TRY blocks is not free, but still several times cheaper than catching exceptions.

Alexander Kuznetsov — Tue, 26 Jul 2011 15:15:00 GMT

The overhead of entering a TRY block is relatively small, but the overhead of catching an exception is more substantial. If we manage to use CASE or IF to prevent an exception, we can noticeably improve performance. We shall run benchmarks and see for...(read more)

Comparing the cost of INSERT ... EXEC and its alternatives

Alexander Kuznetsov — Tue, 26 Jul 2011 00:15:00 GMT

It is well known that INSERT ... EXEC is not free, but how expensive is it as compared to its alternatives? We shall run benchmarks and see that: Invoking a multi-statement UDF is approximately two times faster than capturing the output of a stored procedure...(read more)

A free book entitled "Version Control by Example" by Eric Sink is available for download

Alexander Kuznetsov — Mon, 25 Jul 2011 01:30:00 GMT

I have just downloaded and started reading a free PDF book about version control systems (VCS), entitled Version Control by Example , written by Eric Sink. So far I have read a really informative chapter about the advantages of third generation VCS, such...(read more)

More specific queries may run faster, but we should not overdo it.

Alexander Kuznetsov — Thu, 21 Jul 2011 03:01:00 GMT

It is well known that replacing a generic, one-size-fits-all query with several more specific ones may boost performance. However, in some cases these more specific queries do not perform any faster than the generic one. We shall consider several examples...(read more)

Will wrapping up code in a reusable stored procedure hurt performance?

Alexander Kuznetsov — Wed, 20 Jul 2011 18:03:00 GMT

In T-SQL programming, stored procedure calls are not free, and nested stored procedure calls cost even more. First, we shall prove it by running benchmarks. Next, we shall determine if the cost of calling stored procedures is high as compared to the rest...(read more)

Demos for my today's presentation at SQL Rally

Alexander Kuznetsov — Thu, 12 May 2011 14:35:00 GMT

Are attached to this short blog post. Also I have promised to post the following link: This is a benchmark that determines whether SET or SELECT is faster....(read more)