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 KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        ( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        ( id, i )
VALUES  ( 1, 0 ) ;
 

Benchmarking

The following two stored procedures run the same modifications 10K times. The first one does not use an explicit transaction:

 CREATE PROCEDURE dbo.Toggle10kTimesX3WithoutTransaction
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 10000
            BEGIN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                SET @i = @i + 1 ;
            END ;
    END ;

The second procedure does use an explicit transaction:

CREATE PROCEDURE dbo.Toggle10kTimesX3WithXactAbortOff
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 10000
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;

Let's run the following script a few times:

EXEC dbo.Toggle10kTimesX3WithoutTransaction ;
GO
EXEC dbo.Toggle10kTimesX3WithXactAbortOff ;
 

Typical CPU and duration is as follows:

 dbo.Toggle10kTimesX3WithoutTransaction

CPU: 450-530 Duration:1310

dbo.Toggle10kTimesX3WithXactAbortOff

CPU: 110-140 Duration: 255

Conclusion

As we have seen, wrapping related changes in as transaction may improve performance.By related, I mean changes that should all succeed or all fail if we are ensuring atomicity.

However, we typically need to keep transactions short, so we should not bundle unrelated changes in transactions - that may hurt concurrency.

Also I am being very careful and not making any blanket statements here. I did not research this effect with due diligence. I noticed the effect in one case and came up with a repro that works in one particular case on one server, this is all.

I would really appreciate if you run the repro, tweak it as you see needed, and post the results here. TIA!

In general, we should not assume that larger transactions always mean better performance - the opposite is true in many cases. We should always benchmark the specific case at hand and decide on case by case basis.

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 likely, this does not slow us down much. We stress test how it works.
  
• If concurrent updates become frequent, we solve the problem outside the database. Our application servers accumulate incoming changes and save them in bulk. Of course, these bulk saves still have to acquire some exclusive lock before updating. We stress test how it works.

I still have to see how it works out long term, but short term we save considerable effort on not troubleshooting deadlocks, and not having to implement retry logic. So far I think that being deadlock-free is worth it, but only if we can avoid high concurrency, when lots of modifications touch a single row each. If we cannot avoid high concurrency, then it might be easier to just let it deadlock from time to time.

What do you think? Are you doing anything similar?

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', 'Truck'))
);
GO

CREATE TABLE dbo.Cars(ID INT NOT NULL,
    [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED,
    OtherData VARCHAR(10) NULL,
    CONSTRAINT Cars_PK PRIMARY KEY(ID),
    CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type])
        REFERENCES dbo.Vehicles(ID, [Type])
);
GO
-- adding parent rows
INSERT INTO dbo.Vehicles(ID, [Type])
VALUES(1, 'Car'),
(2, 'Truck');

The following INSERT succeeds:

INSERT INTO dbo.Cars(ID, OtherData)
VALUES(1, 'Some Data');

DELETE FROM dbo.Cars;

MERGE should be able to add the same row, but it fails:

  MERGE dbo.Cars AS TargetTable
    USING
        ( SELECT    1 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);

Msg 547, LEVEL 16, State 0, Line 1
The MERGE statement conflicted WITH the FOREIGN KEY CONSTRAINT "Cars_FK_Vehicles" The conflict occurred IN DATABASE "Test" TABLE "dbo.Vehicles"
The statement has been terminated.


False Positives

Martin Smith has posted this repro on stackoverflow, in response to my scenario described above. This script successfully adds an orphan row into dbo.Cars table:

ALTER TABLE dbo.Vehicles
   DROP CONSTRAINT Vehicles_CHK_ValidTypes ;
GO

INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, '');
GO

MERGE dbo.Cars AS TargetTable
    USING
        ( SELECT    3 AS ID ,
                    'Some Data' AS OtherData
        ) AS SourceData
    ON  SourceData.ID = TargetTable.ID
    WHEN NOT MATCHED
        THEN INSERT (ID, OtherData)
        VALUES(SourceData.ID, SourceData.OtherData);
GO

-- the FK constraint is enabled and trusted
SELECT is_disabled,is_not_trusted
   FROM sys.foreign_keys
   WHERE name = 'Cars_FK_Vehicles';
-- yet we have an orphan row
SELECT * FROM dbo.Cars
SELECT * FROM dbo.Vehicles
GO

is_disabled is_not_trusted
----------- --------------
0           0

(1 row(s) affected)

ID          TYPE  OtherData
----------- ----- ----------
3           Car   SOME Data

(1 row(s) affected)

ID          TYPE
----------- -----
1           Car
2           Truck
3          


Conclusion 

In Ch 7 of my book "Defensive Database Programming", I wrote the following statement.

"As long as a constraint is trusted, then we know for sure that all our data complies with the rules that it dictates."

At the time I was writing that, I believed that was true. Unfortunately, as we have just seen, I was wrong - we can bypass trusted foreign key constraints in SQL 2008 R2.

P.S.

I have just learned that there is another scenario to bypass a foreign key. Google up this: "Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations".

Another very important thing that I have just learned that this Connect item did not get urgent priority. Quoting directly from Connect: "Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue".

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 that invokes the UDF(s).

Prerequisites

Before we run the repro scripts, we need the following setup:

 ALTER DATABASE MyTest SET READ_COMMITTED_SNAPSHOT ON ;
GO

USE MyTest ;
GO

CREATE TABLE dbo.Messages
    (
      MessageID INT NOT NULL
                    CONSTRAINT PK_Messages PRIMARY KEY ,
      MessageBody VARCHAR(50) NOT NULL
    ) ;
GO

INSERT  INTO dbo.Messages
        ( MessageID, MessageBody )
VALUES  ( 1, 'Hello world' ) ;
GO


Getting inconsistent results from a multi-statement UDF

First, we need to create this UDF:

CREATE FUNCTION dbo.AllMessagesTwice ( )
RETURNS @ret TABLE
    (
      MessageBody VARCHAR(50) NOT NULL ,
      SelectedAt DATETIME2(5) NOT NULL
    )
AS
    BEGIN ;
        INSERT  @ret
                ( MessageBody ,
                  SelectedAt
                )
                SELECT  MessageBody ,
                        GETDATE()
                FROM    dbo.Messages ;
        DECLARE @d DATETIME2(5), @i INT ;
        SET @d = DATEADD(SECOND, 30, GETDATE()) ;
        WHILE GETDATE() < @d
            BEGIN ;
               SET @i = 1;
            END ;
        INSERT  @ret
                ( MessageBody ,
                  SelectedAt
                )
                SELECT  MessageBody ,
                        GETDATE()
                FROM    dbo.Messages ;
        RETURN ;
    END ;
GO
 

Next, let us open a tab and cut and paste the following script, but not run it yet:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
SELECT * FROM dbo.AllMessagesTwice ( )

DELETE FROM dbo.Messages WHERE MessageId = 2 ;
 

In another tab, cut and paste the following script, but not run it either:

INSERT  INTO dbo.Messages
        ( MessageID, MessageBody )
VALUES  ( 2, 'Hi there' ) ;


Run the select script in the first tab - it takes 30 seconds to complete. Immediately return to the second tab and insert the 'Hi there' message.

When the select in the first tab completes, we shall clearly see the 'Hi there' message, which definitle was inserted after the select began, as well as the timestamp that shows when the row was selected, 30 seconds after the beginning of the statement:

Hello world 2011-08-02 20:56:44.70000
Hello world 2011-08-02 20:57:14.74000
Hi there    2011-08-02 20:57:14.74000

As we have seen, a multi-statement UDF can return data that was committed after the time when the select using the UDF began.

Getting inconsistent results from a scalar UDF

Here is the scalar UDF we shall be using:

 CREATE FUNCTION dbo.MessageWithLastId ( )
RETURNS VARCHAR(50)
AS
    BEGIN ;
        DECLARE @d DATETIME2(5) ,
            @i INT ;
        SET @d = DATEADD(SECOND, 30, GETDATE()) ;
        WHILE GETDATE() < @d
            BEGIN ;
                SET @i = 1 ;
            END ;
        RETURN (SELECT TOP(1) MessageBody FROM dbo.Messages
        ORDER BY MessageID DESC) ;
    END ;
GO


When we have created the scalar UDF, let us cut, paste, and run the following script:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

DELETE FROM dbo.Messages WHERE MessageId = 2 ;
PRINT 'Data as of the beginning of the dbo.MessageWithLastId ( ) below'
SELECT MessageBody FROM dbo.Messages
        ORDER BY MessageID ;

PRINT 'Data returned by dbo.MessageWithLastId ( )'
        
SELECT dbo.MessageWithLastId ( ) ;

DELETE FROM dbo.Messages WHERE MessageId = 2 ;

In the second tab we need to immediately run the following, we have 30 seconds to do so:

INSERT  INTO dbo.Messages
        ( MessageID, MessageBody )
VALUES  ( 2, 'Hi there' ) ;

When the select in the first tab completes, we shall see that the scalar UDF returned the value that was not in the table when the select has started:

Data AS OF the beginning OF the dbo.MessageWithLastId ( ) below
MessageBody
--------------------------------------------------
Hello world

(1 row(s) affected)

Data returned BY dbo.MessageWithLastId ( )

--------------------------------------------------
Hi there
 

As we have seen, a scalar UDF can read data that was committed after the time when the select using the UDF began.

Snapshot isolation guarantees consistency

We can rerun both previous examples under snapshot isolation. All we need to do is add the following line immediately before the select that invokes our UDF:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;

In both cases we shall see that under snapshot isolation our selects return consistent data as of the time the select started.

Conclusion

As we have seen, if we are using scalar or multi-statement UDFs, and we need statement level consistency, we need to use snapshot isolation - READ_COMMITTED_SNAPSHOT may fail to provide the consistency we need.

Naturally, inline UDFs are esentially macroes - they are flattened into the select they are invoked from. As such, READ_COMMITTED_SNAPSHOT does provide statement level consistency as long as we are only using inline UDFs, and do not use scalar and multi-statement ones.

I would like to thank Erland Sommarskog, whose feedback inspired this post - I am posting this content with his permission.

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 UDF or running INSERT ... EXEC may be several times higher than the cost of running the DML inside the UDF or stored procedure.

We shall only run benchmarks and estimate real execution costs. Whenever we choose whether to cut-and-paste code or to reuse it, whether via INSERT...EXEC, or in a multi-statement UDF, knowing these numbers may be very useful.

For an in-depth analysis of what contributes to the overhead, make sure that you have read the following great post by Adam Machanic:
The Hidden Costs of INSERT EXEC

Also when we use INSERT...EXEC a lot, we increase the workload on tempdb, but this is beyond the scope of this post.

Determining the cost of INSERT ... EXEC into a table variable.

The following stored procedure is very fast, so that the cost of its execution should be quite low. Also it does not contain any DML and as such it does not perform any reads when it runs.

CREATE PROCEDURE dbo.GetProduct @int1 INT, @int2 INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SELECT  @int1 * @int2 AS Product ;
        RETURN @@ERROR ;
    END ;
GO

Suppose also that we need the product of two integers in an output parameter, not in a result set. To accomplish that, we certainly can capture its output via INSERT ... EXEC, as follows:

CREATE PROCEDURE dbo.ReuseGetProduct
    @int1 INT ,
    @int2 INT ,
    @product INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        DECLARE @res TABLE ( product INT ) ;
        INSERT  @res
                ( Product
                )
                EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        SELECT  @product = Product
        FROM    @res ;
    END ;
GO

Alternatively, we can cut and paste some code and get the product of two integers directly into an output parameter:

CREATE PROCEDURE dbo.GetProductDirectly
    @int1 INT ,
    @int2 INT ,
    @product INT OUT
AS
    BEGIN ;
        SET @product = @int1 * @int2 ;
    END ;
GO

To benchmark these two approaches, let us create and execute the following two procedures:

CREATE PROCEDURE dbo.LoopReuseGetProduct
AS
    BEGIN ;
        DECLARE @i INT ,
            @product INT ;
        SET @i = 1 ;
        WHILE @i <= 100000
            BEGIN ;
                EXEC dbo.ReuseGetProduct @int1 = 1, @int2 = 2,
                    @product = @product OUT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO
DROP PROCEDURE dbo.LoopGetProductDirectly
CREATE PROCEDURE dbo.LoopGetProductDirectly
AS
    BEGIN ;
        DECLARE @i INT ,
            @product INT ;
        SET @i = 1 ;
        WHILE @i <= 100000
            BEGIN ;
                EXEC dbo.GetProductDirectly @int1 = 1, @int2 = 2,
                    @product = @product OUT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

EXEC dbo.LoopReuseGetProduct ;
GO
EXEC dbo.LoopGetProductDirectly ;


The difference in performance is dramatic: while LoopGetProductDirectly uses 1094 ms CPU and no reads, LoopReuseGetProduct uses 21000 ms CPU and performs 1.9M reads.

So the overhead of every INSERT...EXEC into a table variable in this particular case is approximately 19 reads and 0.2 ms of CPU.

Note: if you are wondering why our benchmarks are wrapped as stored procedures, read an excellent post by Linchi Shea entitled "Performance impact: stored procedures, SQL batches, and CPU usage"

 Comparing INSERT ... EXEC into a table variable vs. INSERT ... EXEC into a temporary table

As we have seen, INSERT ... EXEC into a table variable is not cheap. Let us replace the table variable with a temporary table and see if it runs faster:

ALTER PROCEDURE dbo.ReuseGetProduct
    @int1 INT ,
    @int2 INT ,
    @product INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        CREATE TABLE #res( product INT ) ;
        INSERT  #res
                ( Product
                )
                EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        SELECT  @product = Product
        FROM    #res ;
    END ;
GO   

When we rerun LoopReuseGetProduct, the performance is even worse: it uses 41600 ms CPU and performs 4.6M reads.

So the overhead of every INSERT...EXEC into a temporary table in this particular case is approximately 46 reads and 0.4 ms CPU.

Comparing INSERT ... EXEC into a table variable vs. invoking a UDF

To reuse our code, we also can wrap it in a multi-statement UDF, or in an inline one. As before, we shall wrap our benchmarks in stored procedures:

CREATE FUNCTION dbo.GetProduct_MultiStatement ( @int1 INT, @int2 INT )
RETURNS @ret TABLE ( Product INT )
AS
    BEGIN ;
        INSERT  @ret
                ( Product )
                SELECT  @int1 * @int2 ;
        RETURN ;                
    END ;
GO

CREATE PROCEDURE dbo.LoopGetProduct_MultiStatement
AS
    BEGIN ;
        DECLARE @i INT ,
            @product INT ;
        SET @i = 1 ;
        WHILE @i <= 100000
            BEGIN ;
                SELECT  @product = product
                FROM    dbo.GetProduct_MultiStatement(1, 1) ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

CREATE FUNCTION dbo.GetProduct_Inline ( @int1 INT, @int2 INT )
RETURNS TABLE
AS
RETURN
    ( SELECT    @int1 * @int2 AS Product
    ) ;
GO

CREATE PROCEDURE dbo.LoopGetProduct_Inline
AS
    BEGIN ;
        DECLARE @i INT ,
            @product INT ;
        SET @i = 1 ;
        WHILE @i <= 100000
            BEGIN ;
                SELECT  @product = product
                FROM    dbo.GetProduct_Inline(1, 1) ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO   

Here are the results of our benchmarking:

• dbo.LoopGetProduct_Inline   uses 563 ms of CPU and no reads;
  
• dbo.LoopGetProduct_MultiStatement uses 10266 ms of CPU and performs exactly 1M reads.

Clearly the inline UDF performs much faster.

In this scenario, the overhead of invoking a multi-statement UDF is 10 reads and 0.1 ms of CPU.

Is this overhead high as compared to the cost of executing DML?

If our DML is fast, it may take less resources to execute our DML than to invoke a multi-statement UDF or to run an INSERT...EXEC. To demonstrate that, let us create a small table with just 10K rows:

CREATE TABLE dbo.Products
    (
      Multiplier1 INT NOT NULL ,
      Multiplier2 INT NOT NULL ,
      Product INT NOT NULL ,
      CONSTRAINT PK_Products PRIMARY KEY(Multiplier1, Multiplier2 )
    ) ;
GO

INSERT  INTO dbo.Products
        ( Multiplier1 ,
          Multiplier2 ,
          Product
        )
        SELECT  n1.n ,
                n2.n ,
                n1.n * n2.n
        FROM    dbo.Numbers AS n1
                CROSS JOIN dbo.Numbers AS n2
        WHERE   n1.n <= 100
                AND n2.n <= 100 ;
GO   

Also let us alter our procedures and UDFs to read from this table:

ALTER PROCEDURE dbo.GetProduct @int1 INT, @int2 INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SELECT  Product
        FROM    dbo.Products
        WHERE   Multiplier1 = @int1
                AND Multiplier2 = @int2 ;
        RETURN @@ERROR ;
    END ;
GO

ALTER FUNCTION dbo.GetProduct_MultiStatement ( @int1 INT, @int2 INT )
RETURNS @ret TABLE ( Product INT )
AS
    BEGIN ;
        INSERT  @ret
                ( Product
                )
                SELECT  Product
                FROM    dbo.Products
                WHERE   Multiplier1 = @int1
                        AND Multiplier2 = @int2 ;
        RETURN ;                
    END ;
GO

ALTER FUNCTION dbo.GetProduct_Inline ( @int1 INT, @int2 INT )
RETURNS TABLE
AS
RETURN
    ( SELECT    Product
      FROM      dbo.Products
      WHERE     Multiplier1 = @int1
                AND Multiplier2 = @int2
    ) ;
GO   

Also let us revert the INSERT...EXEC back to its original version, which uses a table variable and is two times faster than the one with a temporary table:

ALTER PROCEDURE dbo.ReuseGetProduct
    @int1 INT ,
    @int2 INT ,
    @product INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        DECLARE @res TABLE ( product INT ) ;
        INSERT  @res
                ( Product
                )
                EXEC dbo.GetProduct @int1 = @int1, @int2 = @int2 ;
        SELECT  @product = Product
        FROM    @res ;
    END ;
GO   

Let us rerun our benchmarks:

EXEC dbo.LoopReuseGetProduct ;
-- EXEC dbo.LoopReuseGetProduct ; cpu 21906 reads 2100115

GO
EXEC dbo.LoopGetProduct_MultiStatement ;
-- EXEC dbo.LoopGetProduct_MultiStatement ; cpu 11109 reads 1200010

GO
EXEC dbo.LoopGetProduct_Inline ;
--EXEC dbo.LoopGetProduct_Inline ; cpu 1079 reads 200000

Our table Products is small; it takes just two reads two perform a simple select ad look up the value in it. Because we look up the value 100K times, we use 200K reads to do the actual useful work.

In this scenario, less than 20% of CPU and reads are spent on executing the DML. The rest is the overhead of invoking a multi-statement UDF or of running an INSERT...EXEC.

Of course, if we are running more complex and/or slower DML, the ratio of useful work vs the overhead will be higher. As such, we might be more willing to reuse our code via INSERT...EXEC or via a multi-statement UDF, even though the overhead is quite substantial.

Conclusion

As we have seen, neither INSERT...EXEC nor the multi-statement UDF are cheap. Whenever we are deciding whether to use them, we need to consider their impact on performance, preferably by running benchmarks.

One more thing: I am running my benchmarks on SQL 2008 R2, on my laptop. The results I am getting on my servers are similar. However, you might get different results in other environments, on other versions of SQL Server. I encourage you to rerun my scripts, and post here whether you are getting similar results, and what is your environment.

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

 
Reuse Your Code with Table-Valued UDFs

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 NOT NULL ,
      [weight] INT NOT NULL ,
      SpaceFiller CHAR(30) NOT NULL,
      CONSTRAINT PK_Packages PRIMARY KEY ( ID )
    ) ;
GO

INSERT  INTO dbo.Packages
        ( [length] ,
          width ,
          [weight],
          SpaceFiller
        )
        SELECT  n1.n ,
                n2.n ,
                1 ,
                '*'
        FROM    ( SELECT    n
                  FROM      dbo.Numbers
                  WHERE     n <= 2000
                ) AS n1
                CROSS JOIN ( SELECT n
                             FROM   dbo.Numbers
                             WHERE  n <= 2000
                           ) AS n2 ;

GO

CREATE INDEX Packages_length_width
   ON dbo.Packages([length], width) INCLUDE ( [weight], SpaceFiller ) ;
GO

CREATE INDEX Packages_width_length
   ON dbo.Packages(width, [length]) INCLUDE ( [weight], SpaceFiller ) ;
GO

When replacing a generic query with two specific ones boosts performance

The following generic query performs very poorly - it does not utilize any indexes. Instead, it scans the whole table and sorts it:

CREATE PROCEDURE dbo.GetTop1Weight
    @OrderBy VARCHAR(30) ,
    @topWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT TOP ( 1 )
                @topWeight = [weight]
        FROM    dbo.Packages
        ORDER BY CASE WHEN @OrderBy = 'Length,Width' THEN [length]
                      ELSE [width]
                 END ,
                CASE WHEN @OrderBy = 'Length,Width' THEN [width]
                     ELSE [length]
                END ;
    END ;
GO   

You can run it or at least see the execution plan, and see for yourself - the query needs about 25K logical reads to complete.

Let us replace this generic query with two specific ones:

ALTER PROCEDURE dbo.GetTop1Weight
    @OrderBy VARCHAR(30) ,
    @topWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        IF @OrderBy = 'Length,Width'
            BEGIN ;
                SELECT TOP ( 1 )
                        @topWeight = [weight]
                FROM    dbo.Packages
                ORDER BY [length] ,
                        [width] ;
            END ;
        ELSE
            BEGIN ;
                SELECT TOP ( 1 )
                        @topWeight = [weight]
                FROM    dbo.Packages
                ORDER BY [width] ,
                        [length] ;
            END ;
    END ;
GO   

As a result the query speeds up dramatically - for both sort orders it executes as one index seek:

DECLARE @topWeight INT ;
EXEC dbo.GetTop1Weight @OrderBy = 'Length,Width',
    @topWeight = @topWeight OUT ;
EXEC dbo.GetTop1Weight @OrderBy = 'Width,Length',
    @topWeight = @topWeight OUT ;

Both queries need just 3 logical reads to complete, which is a huge improvement.

As we have seen, sometimes replacing a generic, one-size-fits-all query with several more specific ones dramatically improves performance. However, in many other cases more specific queries do not perform any better.

When replacing a generic query with two specific ones does not improve performance at all

The following one-size-fits-all query performs very poorly:

CREATE PROCEDURE dbo.GetTotalWeight
    @length INT = NULL ,
    @width INT = NULL ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        IF ( ( @length IS NULL
               AND @width IS NULL
             )
             OR ( @length IS NOT NULL
                  AND @width IS NOT NULL
                )
           )
            BEGIN ;
                RAISERROR('Must provide exactly one of the following parameters: @width and @length', 16, 1) ;
                RETURN 1 ;
            END ;
        SELECT  @totalWeight = SUM([weight])
        FROM    dbo.Packages
        WHERE   [length] = COALESCE(@length, [length])
                AND [width] = COALESCE(@width, [width]) ;
    END ;
GO
 

Note: for simplicity, let us assume that we must always provide exactly one parameter, either length or width, but never both. In all the subsequent versions we shall skip the verification of parameters, the first IF statement, just to keep our examples shorter.

We can run the following script and see that every time our stored procedure scans the whole table and performs 25K logical reads:

DECLARE @totalWeight INT ;
EXEC dbo.GetTotalWeight @length = 1,
    @totalWeight = @totalWeight OUT ;
EXEC dbo.GetTotalWeight @width = 1,
    @totalWeight = @totalWeight OUT ;

Speeding up this query is very easy. The following query is index friendly. For brevity, we have omitted the first step, the verification that either length or width, but never both, is provided:

ALTER PROCEDURE dbo.GetTotalWeight
    @length INT = NULL ,
    @width INT = NULL ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT  @totalWeight = SUM([weight])
        FROM    dbo.Packages
        WHERE   [length] = @length
                OR [width] = @width ;
    END ;
GO

Every time it uses the right index and needs just 16 logical reads:

DECLARE @totalWeight INT ;
EXEC dbo.GetTotalWeight @length = 1,
    @totalWeight = @totalWeight OUT ;
EXEC dbo.GetTotalWeight @width = 1,
    @totalWeight = @totalWeight OUT ; 

In this case more specific queries do not run any faster. The following queries show several approaches which we can try out. We can keep all the code in one procedure, as shown in the following scripts:

ALTER PROCEDURE dbo.GetTotalWeight
    @length INT = NULL ,
    @width INT = NULL ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT  @totalWeight = SUM([weight])
        FROM    ( SELECT    [weight]
                  FROM      dbo.Packages
                  WHERE     [length] = @length
                  UNION ALL
                  SELECT    [weight]
                  FROM      dbo.Packages
                  WHERE     [width] = @width
                ) AS T ;
    END ;
GO


ALTER PROCEDURE dbo.GetTotalWeight
    @length INT = NULL ,
    @width INT = NULL ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        IF @length IS NOT NULL
            BEGIN ;
                SELECT  @totalWeight = SUM([weight])
                FROM    dbo.Packages
                WHERE   [length] = @length ;
            END
        ELSE
            BEGIN
                SELECT  @totalWeight = SUM([weight])
                FROM    dbo.Packages
                WHERE   [width] = @width ;
            END
    END ;
GO


Alternatively, we can try out two separate procedures:

CREATE PROCEDURE dbo.GetTotalWeight_ByWidth
    @width INT ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT  @totalWeight = SUM([weight])
        FROM    dbo.Packages
        WHERE   [width] = @width ;
    END ;
GO

CREATE PROCEDURE dbo.GetTotalWeight_ByLength
    @length INT ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT  @totalWeight = SUM([weight])
        FROM    dbo.Packages
        WHERE   [length] = @length ;
    END ;
GO

DECLARE @totalWeight INT ;
EXEC dbo.GetTotalWeight_ByLength @length = 1,
    @totalWeight = @totalWeight OUT ;
EXEC dbo.GetTotalWeight_ByWidth @width = 1,
    @totalWeight = @totalWeight OUT ;

Anyway, none of the approaches we have tried out performs any faster than the optimized generic one - they all complete in 16 logical reads and use approximately the same CPU.

So, in this case replacing a generic procedure with several specific ones does not make any sense at all - it increases complexity without improving performance.

We are better off with the original generic query:

ALTER PROCEDURE dbo.GetTotalWeight
    @length INT = NULL ,
    @width INT = NULL ,
    @totalWeight INT OUT
AS
    BEGIN ;
        SET NOCOUNT ON ;
        SELECT  @totalWeight = SUM([weight])
        FROM    dbo.Packages
        WHERE   [length] = @length
                OR [width] = @width ;
    END ;
GO

Conclusion

Although in many cases replacing a generic query with several specific ones does boost performance, we should not use this approach in all cases as a rule of thumb. Instead, we need to prove that splitting a generic query does improve performance before actually doing it.

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

 
Reuse Your Code with Table-Valued UDFs

Are attached to this short blog post.

Also I have promised to post the following link: