Failure to insert in Table costs Space Loss and Decrease Performance

Regarding the space problem of SQL server, I have collected some fact that you might be interested. It's again one things related to performance issue.

SQL Server has an issue when an INSERT fails that includes LOB data, SQL Server allocates space for the insert but does not reclaim the space on the failure therefore taking up more space than necessary.  In this tip I walk through an example of this issue and ways to resolve it.

For the test, let's create a sample test table in my TEST database.

CREATE TABLE MYSQLTEST

       (MYPKKEY   CHAR(10),

        TESTVALUE NVARCHAR(MAX)

        CONSTRAINT [PK_MYPKKEY] PRIMARY KEY CLUSTERED

                  (

                       [MYPKKEY] ASC

                  )

)

GO

 

Before we do any inserts, let's check the space used by the table by issuing the following command:

sp_spaceused 'MYSQLTEST', true

name          rows         reserved      data         index_size       unused

MYSQLTEST  0              0 KB            0 KB        0 KB                0 KB

Let's insert the first record.

DECLARE @TMP NVARCHAR(MAX)

SET @TMP=REPLICATE('THIS IS A TEST',4000)

INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)

And then check the space used again.  Here we can see that reserved is 40KB, data is 32KB for 1 row.

 

sp_spaceused 'MYSQLTEST', true

name          rows         reserved      data         index_size       unused

MYSQLTEST  1              40 KB          32 KB      8 KB                0 KB

 

 

Now, let's try to insert the same row again which should fail because of the PK constraint.

DECLARE @TMP NVARCHAR(MAX)

SET @TMP=REPLICATE('THIS IS A TEST',4000)

INSERT MYSQLTEST(MYPKKEY,TESTVALUE) VALUES ('KEY1',@TMP)

 

As expected, the insert fails because we tried to insert a duplicate primary key value. Let's check the space used again.  We can now see that reserved is 56KB, data is 48KB for 1 row.

 

sp_spaceused 'MYSQLTEST', true

name          rows         reserved      data         index_size       unused

MYSQLTEST  1              56 KB          48 KB      8 KB                0 KB

Now, let's run the same insert statement 100 times.

Lets check the space used again.  Now we can see reserved is 6232KB, data is 848KB for 1 row

sp_spaceused 'MYSQLTEST', true

name          rows         reserved      data         index_size       unused

MYSQLTEST  1              6232 KB      848 KB    8 KB                5376 KB

 

If we want to reclaim the allocated space we can run the following CLEANTABLE command where TEST is the name of my database and MYSQLTEST is my table.

DBCC CLEANTABLE(TEST,MYSQLTEST,0)

 

Please refer to the Microsoft Article

http://support.microsoft.com/kb/2295878.

To Fix the Issue

 

Posted By: MR. JOYDEEP DAS