Insert Statement Comparison - Interesting Test

Performance  of Inserts in Database
I just written an articel about how we can get benefit of Delayed Durability to handle large amount log activity and related  locking issues
which leads to I/O condention.
You can take a look for article at here
For this we use Insert example and showed how Delayed Durability helps for improvidng perfromance.



Here I want to show some interesting fact about same Insert and how the same operation behaves differently in different scenario
and how the performance changes due to different type of handling of locks and log operation
I have use SQL Server 2014 here for testing.

CREATE TABLE T

(ID INT,

DATA VARCHAR(100))

Here are 3 scenarios
1. Normal Insert

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

TRUNCATE TABLE T

SELECT * INTO #BEFORE_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('test'), NULL);

SELECT @DATE1 =  GETDATE()

SET NOCOUNT ON

DECLARE @I INT =1

WHILE @I <= 100000

       BEGIN

              INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))

              SET @I = @I+1

       END

SET NOCOUNT OFF

SELECT * INTO #AFTER_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('test'), NULL);

SELECT @DATE2 =  GETDATE()

SELECT DATEDIFF (SS, @DATE1,@DATE2)

SELECT 

                B.DATABASE_ID, B.[FILE_ID],

                           CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,

                A.NUM_OF_READS - B.NUM_OF_READS AS NUM_OF_READS,

                A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ AS NUM_OF_BYTES_READ,

                A.NUM_OF_WRITES - B.NUM_OF_WRITES AS NUM_OF_WRITES,

                (A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024.0*1024) AS NUM_OF_BYTES_WRITTEN_MB

            FROM

               #BEFORE_TEST AS B

               INNER JOIN #AFTER_TEST A ON B.DATABASE_ID = A.DATABASE_ID AND B.[FILE_ID] = A.[FILE_ID]

 

Number of writes and Size is highest
Its due to write each value in log and locking maintenance for   Insert






2. Insert in transaction

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

TRUNCATE TABLE T

SELECT * INTO #BEFORE_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('test'), NULL);

SELECT @DATE1 =  GETDATE()

SET NOCOUNT ON

BEGIN TRAN

DECLARE @I INT =1

WHILE @I <= 100000

       BEGIN

              INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))

              SET @I = @I+1

       END

SET NOCOUNT OFF

COMMIT TRAN

SELECT * INTO #AFTER_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('test'), NULL);

SELECT @DATE2 =  GETDATE()

SELECT DATEDIFF (SS, @DATE1,@DATE2)

SELECT 

                B.DATABASE_ID, B.[FILE_ID],

                           CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,

                A.NUM_OF_READS - B.NUM_OF_READS AS NUM_OF_READS,

                A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ AS NUM_OF_BYTES_READ,

                A.NUM_OF_WRITES - B.NUM_OF_WRITES AS NUM_OF_WRITES,

                (A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024.0*1024) AS NUM_OF_BYTES_WRITTEN_MB

            FROM

               #BEFORE_TEST AS B

               INNER JOIN #AFTER_TEST A ON B.DATABASE_ID = A.DATABASE_ID AND B.[FILE_ID] = A.[FILE_ID]

Number of writes and its size very low compare to Normal

Its due to locks are not released till transaction completes.So locking management takes lots of time and resource.

3. Insert in Tempdb

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

TRUNCATE TABLE T

SELECT * INTO #BEFORE_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('tempdb'), NULL);

SELECT @DATE1 =  GETDATE()

SET NOCOUNT ON

DECLARE @I INT =1

WHILE @I <= 100000

       BEGIN

              INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))

              SET @I = @I+1

       END

SET NOCOUNT OFF

SELECT * INTO #AFTER_TEST FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DB_ID('tempdb'), NULL);

SELECT @DATE2 =  GETDATE()

SELECT DATEDIFF (SS, @DATE1,@DATE2)

SELECT 

                B.DATABASE_ID, B.[FILE_ID],

                           CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,

                A.NUM_OF_READS - B.NUM_OF_READS AS NUM_OF_READS,

                A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ AS NUM_OF_BYTES_READ,

                A.NUM_OF_WRITES - B.NUM_OF_WRITES AS NUM_OF_WRITES,

                (A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024.0*1024) AS NUM_OF_BYTES_WRITTEN_MB

            FROM

               #BEFORE_TEST AS B

               INNER JOIN #AFTER_TEST A ON B.DATABASE_ID = A.DATABASE_ID AND B.[FILE_ID] = A.[FILE_ID]

Number of writes and its size is  more than transcation but very less compare to Normal
Its because TempDB does not have to write to transaction log and manage its related activities.