Alexander Kuznetsov

On filtered indexes and defensive coding

2010-02-05T15:16:00Z

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 Michelle Ufford, Filtered Indexes Work-Around. I will not repeat the whole post here, I encourage you to read it in full, but here are the relevant parts. There is a filtered index: CREATE NONCLUSTERED INDEX MyLatestData ON dbo.myTable ( myDate ) Include...(read more)

Multi-statement TVFs are essentially slowish nested loops.

2010-02-02T20:53:00Z

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 loops. As usual, inline UDFs shine as compared to multi-statement ones, at least in all the benchmarks in this post - let us run some tests and see for ourselves. Setting up data First of all, here is a table, rather large, 512K rows, and rather wide,...(read more)

Don't swap horses in midstream.

2010-02-01T19:07:00Z

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 ( 2 ) AS BEGIN ; SET TRANSACTION ISOLATION LEVEL SNAPSHOT ; SELECT CountrySymbol , Description FROM data.Countries WHERE CountrySymbol = @CountrySymbol ; END ; GO -- Smoke test: this call completes EXECUTE dbo.SelectCountry @CountrySymbol = 'US' ;...(read more)

Correlated subqueries do not execute once per row.

2010-01-20T23:08:00Z

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 course) Let me provide a script which compares the performance of a query with one correlated subquery vs. an equivalent out join. First, we need two tables for our example: CREATE TABLE dbo.Parent ( ID INT NOT NULL PRIMARY KEY , ParentNumber INT NOT...(read more)

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

2010-01-15T21:18:00Z

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 I cannot follow this advice. If I store an object in one parent table and one or more child ones, I cannot follow this advice at all. When inserting, I need to insert my parent row first. When deleting, I have to do it in the opposite order. If I use...(read more)

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

2010-01-12T14:38:00Z

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 and see for ourselves. Here is the table we shall be using: CREATE TABLE dbo.TwoINTs ( ID INT NOT NULL PRIMARY KEY , i1 INT NOT NULL , i2 INT NOT NULL , version ROWVERSION ) ; GO INSERT INTO dbo.TwoINTs ( ID , i1 , i2 ) VALUES ( 1 , 0 , 0 ) ; IF EXISTS(…)...(read more)

Retrying after deadlocks leads to lost updates

2010-01-08T15:15:00Z

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 cannot be involved in deadlocks, which means that all the parties involved in a deadlock are writers, modified or attempted to modify the same data. If we just catch the exception and automatically retry, we can overwrite someone else's changes. This...(read more)

To design or not to design?

2009-12-08T22:18:00Z

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 a technical person (including me) whom I would trust to know what is really the right thing to do in the long run. Too strong a strong vision can kill you - you'll walk right over the edge, firm in the knowledge of the path in front of you." here "The...(read more)

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

2009-12-08T16:06:00Z

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 the problem: -- yesterday it worked, or did it? SET LANGUAGE US_English ; SELECT CAST ( '11/12/2009' AS DATETIME ); SET LANGUAGE Norwegian ; SELECT CAST ( '11/12/2009' AS DATETIME ); Changed language setting to us_english. -----------------------...(read more)

PIVOting dense data may speed up your queires

2009-11-20T18:53:00Z

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 and transmit it over the network may be less. Here are my benchmarks. Let us create a helper table with 1M numbers: CREATE TABLE dbo.Numbers ( n INT NOT NULL PRIMARY KEY ); GO TRUNCATE TABLE dbo.Numbers ; INSERT INTO dbo.Numbers ( n ) VALUES ( 1 );...(read more)

Speaking in Chicago on Sep 10

2009-08-17T20:31:00Z

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...

2009-07-16T21:39:00Z

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 ) = 1 AND CAST ( VarcharColumn ) AS DATETIME ) = '20090707' It can blow up at any time, and the reason is simple: the conditions in your WHERE clause can evaluate in any order, and the order can change the next time your query executes. If an invalid...(read more)

Saving the whole team of players or nothing

2009-07-15T18:28:00Z

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 yet, but I wanted to share an interesting idea. Of course we typically use triggers or stored procedures to implement such rules, but you can do it with constraints too, and it is worth mentioning at least as a brainteaser. Detailed comparison...(read more)

Optimizing yet another query that involves highly correlated columns

2009-07-13T22:15:00Z

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 populates it is at the end of this post): CREATE TABLE dbo.Events ( EventID INT NOT NULL PRIMARY KEY , EventTime DATETIME NOT NULL, SomeMoreData CHAR ( 10 ) ); Suppose that your system inserts events one by one, that EventID keeps increasing as your...(read more)

Using CROSS APPLY to optimize joins on BETWEEN conditions

2009-07-08T00:47:00Z

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, it produced a better plan, and the query ran dramatically faster. The slow query The following tables store one-munite commercials for every minut for one year, and customer calls, one call per minute, for the same year. The scripts that populate tables...(read more)