Alexander Kuznetsov

On filtered indexes and defensive coding

Alexander Kuznetsov — Fri, 05 Feb 2010 15:16:00 GMT

When one and the same constant is copied and pasted in more than one place, there is always a chance that we can change it in one place and fail to change in another, resulting in a discrepancy. For example, recently I read a very interesting post by...(read more)

Multi-statement TVFs are essentially slowish nested loops.

Alexander Kuznetsov — Tue, 02 Feb 2010 20:53:00 GMT

Whenever we are using multi-statement TVFs, we are essentially forcing nested loops logic on the database engine. Although multi-statement TVFs are smart enough and do not always execute once per row, when they do so, they may be much slower than nested...(read more)

Don't swap horses in midstream.

Alexander Kuznetsov — Mon, 01 Feb 2010 19:07:00 GMT

We can begin a transaction under snapshot isolation, but we cannot switch to it in the middle of an outstanding transaction. For example, the following procedure looks good and passes a smoke test: CREATE PROCEDURE dbo.SelectCountry @CountrySymbol CHAR...(read more)

Correlated subqueries do not execute once per row.

Alexander Kuznetsov — Wed, 20 Jan 2010 23:08:00 GMT

Correlated subqueries do not have to execute once per row - on the contrary, they are equivalent to outer joins, and they may have the same execution plans and the same real execution costs (if we retrieve only one column via a correlated subquery, of...(read more)

When acquiring locks in the same order is not possible or not feasible.

Alexander Kuznetsov — Fri, 15 Jan 2010 21:18:00 GMT

To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when...(read more)

T-SQL Tuesday #002: patterns that do not work as expected.

Alexander Kuznetsov — Tue, 12 Jan 2010 14:38:00 GMT

Neither UPDATE … IF (@@ROWCOUNT = 0) INSERT nor IF EXISTS(...) UPDATE ELSE INSERT patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing...(read more)

Retrying after deadlocks leads to lost updates

Alexander Kuznetsov — Fri, 08 Jan 2010 15:15:00 GMT

When we retry after deadlocks, we are very likely to overwrite other processes' changes. We need to be aware that very likely someone else modified the data we intended to modify. Especially if all the readers run under snapshot isolation, then readers...(read more)

To design or not to design?

Alexander Kuznetsov — Tue, 08 Dec 2009 22:18:00 GMT

Clearly Linux is one of the most successful products ever. Let me quote a little bit from Linus Torvalds and other brilliant people involved in it: "A strong vision and a sure hand sound like good things on paper. It's just that I have never _ever_ met...(read more)

T-SQL Tuesday #001: Yesterday it worked, today it's not working...

Alexander Kuznetsov — Tue, 08 Dec 2009 16:06:00 GMT

But did it actually work yesterday? If the day is Friday, Noverber 13th, or the 13th day of any other month, and your query blows up for the first time, one place to search for is character strings converted to datetime values. The following script illustrates...(read more)

PIVOting dense data may speed up your queires

Alexander Kuznetsov — Fri, 20 Nov 2009 18:53:00 GMT

Of course, PIVOting uses up some CPU. However, if the data is dense (all the cells in the pivoted result set are not NULL), then the size of the pivoted result set may be significantly less. As a result, the overall time to retrieve a pivoted result set...(read more)

Speaking in Chicago on Sep 10

Alexander Kuznetsov — Mon, 17 Aug 2009 20:31:00 GMT

I'll be speaking about Defensive Database Programming in Chicago on Sep 10 at 5:30PM. http://chicago.sqlpass.org/ The seating is limited to 50, You must RSVP to attend Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it...(read more)

When you add an index and your query blows up...

Alexander Kuznetsov — Thu, 16 Jul 2009 21:39:00 GMT

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order - making such assumptions leads to unsafe queries. For example, the following query is not safe: SELECT * FROM dbo.Messages WHERE ISDATE ( VarcharColumn...(read more)

Saving the whole team of players or nothing

Alexander Kuznetsov — Wed, 15 Jul 2009 18:28:00 GMT

Suppose that you need to store teams of players, and you must enforce the following business rule: each team must consist of exactly two players. I will demonstrate how you can use constraints to implement this rule. I haven't used this approach in production...(read more)

Optimizing yet another query that involves highly correlated columns

Alexander Kuznetsov — Mon, 13 Jul 2009 22:15:00 GMT

In some cases some of the columns involved in a query are highly correlated. If you manage to communicate to the optimizer that valuable information, it may come up with a more efficient plan. For example, consider the following table (the script that...(read more)

Using CROSS APPLY to optimize joins on BETWEEN conditions

Alexander Kuznetsov — Wed, 08 Jul 2009 00:47:00 GMT

Recently I encountered a case when I knew much more about the data than the optimizer. Originally the performance was horrible, this is why I had to have a look at the query in the first place. When I was able to share my knowledge with the optimizer,...(read more)