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. 

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts