Alexander Kuznetsov

Inline UDFs do not prevent parallel execution plans

2013-02-20T14:23:00Z

Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our queries are wrapped in inline UDFs or copied and pasted into the main query. As usual, I will provide a repro script. Prerequisites Let us set up a table with 2 million rows - that should be enough to qualify for a parallel plan: CREATE TABLE dbo.Numbers...(read more)

Using constraints to enforce uniqueness of ordered sets of rows

2013-01-28T20:10:00Z

Even the simplest data integrity rules are not easy to implement in the database if instead of individual rows we need to deal with groups or subsets. For example, making sure that a column value is unique in a table is as trivial as creating a unique constraint or index. However, what are we supposed to do if we do not want to store a group of values twice? What if we store cooking recipes as sequences of steps (sets of values), and we do not want to store one and the same recipe (one set of values)...(read more)

The client code that handles timeouts

2013-01-10T21:05:00Z

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests. Implementation The following class extends SqlCommand and rolls back active transactions after timeouts: public static class SqlCommandExtentions { public static void ExecuteNonQueryWithErrorHandling ( this SqlCommand command ) { try { command.ExecuteNonQuery (); } catch ( SqlException e ) { HandleSqlException...(read more)

After the timeout

2013-01-09T18:23:00Z

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected. Let us debug through some client code and see what is happening in more detail. Prerequisites We shall need a few database objects, as follows: CREATE TABLE dbo.Log1 ( InTran VARCHAR ( 20 )); GO CREATE PROCEDURE dbo.TestTran AS BEGIN IF @@TRANCOUNT...(read more)

Book Review: Pro SQL Server 2008 Relational Database Design and Implementation

2012-09-24T16:56:00Z

Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design. Surely we can and sometimes do cut corners and save on database design to get things done faster. Unfortunately, such cutting corners frequently comes back and bites us: we may end up spending a lot of time solving issues caused by poor design. So, solid understanding of relational database design is...(read more)

Awesome videos on Agile

2012-06-18T20:45:00Z

The following videos are a must-see for anyone interested in Agile methodology: Decisions, Decisions Embracing Uncertainty Patterns of Effective Delivery Enjoy!...(read more)

Yet another use of OUTER APPLY in defensive programming

2012-06-06T21:58:00Z

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock function that imitates a subquery: CREATE FUNCTION dbo.BoxById ( @BoxId INT ) RETURNS TABLE AS RETURN ( SELECT CAST ( 1 AS INT ) AS [Length] , CAST ( 2 AS INT ) AS [Width] , CAST ( 3 AS INT ) AS [Height] WHERE @BoxId = 1 ) ; Let us assume that this...(read more)

Catching multiple exceptions on the client is robust and easy

2012-06-06T17:30:00Z

Maria Zakourdaev has just demonstrated that if our T-SQL throws multiple exceptions, ERROR_MESSAGE() in TRY..CATCH block will only expose one. When we handle errors in C#, we have a very easy access to all errors. The following procedure throws two exceptions: CREATE PROCEDURE dbo.ThrowsTwoExceptions AS BEGIN ; RAISERROR ( 'Error 1' , 16 , 1 ) ; RAISERROR ( 'Error 2' , 16 , 1 ) ; END ; GO EXEC dbo.ThrowsTwoExceptions ; Both exceptions are shown by SSMS: Msg 50000 , LEVEL 16 , State 1 , PROCEDURE...(read more)

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)