Alexander Kuznetsov

Avoiding nested transactions might not improve performance.

2012-02-08T18:09:00Z

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 ; SET @trancount = @@TRANCOUNT ; IF @trancount = 0 BEGIN ; BEGIN TRAN ; END ; --(snip) IF @trancount = 0 BEGIN ; COMMIT ; END ; the performance stays the same: I was not able to notice any difference. Here are my benchmarks. Prerequisites All we need...(read more)

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

2012-02-01T22:25:00Z

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, and post your findings. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data We are using the same test data as in my previous post: CREATE TABLE dbo.Toggle1 ( id INT...(read more)

Wrapping related changes in a transaction may use less CPU.

2012-02-01T22:02:00Z

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 I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data After applying Occum's razor, all we need is two tables with one row in each: CREATE TABLE dbo.Toggle1 ( id INT NOT NULL PRIMARY...(read more)

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

2012-01-25T22:09:00Z

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 @to = @to + 3 ; IF DATEPART ( weekday , GETDATE ()) = 3 BEGIN ; -- the scope of this DECLARE does not end when the block ends DECLARE @from INT , @to INT ; SELECT @from = 5 , @to = 7 ; SELECT Number FROM data.Numbers WHERE Number BETWEEN @from AND @to...(read more)

Running OLTPish system without deadlocks, and loving it.

2012-01-04T22:31:00Z

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 not be worth it. To accomplish deadlock-free state, we have taken the following steps: Snapshot isolation for all readers If concurrent updates are not frequent, we just acquire some exclusive lock before updating. As long as collisions are not...(read more)

Refactoring large live OLTP tables without downtime

2012-01-03T18:11:00Z

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, and if we make a mistake, there is no easy rollback to the old version. This is why whenever we need to refactor a large table, we are using an alternative, low-risk, no-downtime, incremental approach. The method I am going to describe has been used...(read more)

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

2011-10-17T17:15:00Z

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 pattern: CREATE TABLE dbo.Vehicles ( ID INT NOT NULL, [Type] VARCHAR ( 5 ) NOT NULL, CONSTRAINT Vehicles_PK PRIMARY KEY ( ID ), CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE ( ID , [Type] ), CONSTRAINT Vehicles_CHK_ValidTypes CHECK ( [Type] IN ( 'Car'...(read more)

Preventing Bob from sales from selecting other people's messages

2011-08-18T19:22:00Z

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 FROM Bob ; This does not work - Bob still can execute Exchange.ShowAllMessages , just as he could before. The reason is simple: everyone were granted the privilege to execute that procedure: GRANT EXECUTE ON Exchange.ShowAllMessages TO...(read more)

Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

2011-08-03T01:33:00Z

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 committed isolation using row versioning provides statement-level read consistency". However, if our select invokes scalar or multi-statement UDFs, this consistency is ensured per a statement in the body of the UDF, not per the whole statement...(read more)

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

2011-07-26T15:15:00Z

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 ourselves - i n our example, catching an exception will be approximately nine times more expensive than exiting the TRY block without it. Entering TRY blocks is not free In this section we shall wrap the body of a stored procedure in a TRY CATCH block,...(read more)

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

2011-07-26T00:15:00Z

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 in a table variable via INSERT ... EXEC; INSERT ... EXEC into a table variable is approximately two times faster than INSERT ... EXEC into a temporary table; Inline UDFs are much cheaper than multi-statement UDFs; The cost of invoking a multi-statement...(read more)

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

2011-07-25T01:30:00Z

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 as Git, over the second generation ones, such as Subversion and TFS. So far it is a very nice read. I hope you will like it too....(read more)

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

2011-07-21T03:01:00Z

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 which demonstrate both scenarios. Prerequisites The following script creates a table and populates it with 4M rows, not much but enough for our test cases. CREATE TABLE dbo.Packages ( ID INT NOT NULL IDENTITY , [length] INT NOT NULL , width INT...(read more)

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

2011-07-20T18:03:00Z

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 of workload. Proving that stored procedure calls are not free Let us begin with a very simple benchmark, using a trivial stored procedure that increments a variable: CREATE PROCEDURE dbo.IncreaseByOne @i INT OUT AS BEGIN ; SET @i = @i + 1 ; END ;...(read more)

Demos for my today's presentation at SQL Rally

2011-05-12T14:35:00Z

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)