Home Dashboard Directory Help
Search

SQL SERVER 2008 DELETE, SPACE REUSE ISSUE by minsouk


Status: 

Closed
 as By Design Help for as By Design


4
0
Sign in
to vote
Type: Bug
ID: 522952
Opened: 1/3/2010 9:23:50 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

In SQL SERVER 2005 When you delete data from HEAP TABLE (paglock or rowlock), the space can be re-used
BUT, In SQL SERVER 2008 When you delete data from HEAP TABLE (paglock or rowlock), the space can not be re-used !!!
SQL SERVER 2005 DELETE and SQL SERVER 2008 BOL are same described but action is different
Is this SQL SERVER 2008 bug? or any other performance issue?

-- THIS IS REPRODUCE SCRIPT

SELECT @@VERSION
GO

USE [MASTER]
GO

IF EXISTS (SELECT * FROM master.dbo.SYSDATABASES WHERE name = 'TESTDB') BEGIN
    ALTER DATABASE TESTDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE TESTDB
END
GO

IF @@VERSION LIKE '%9.00%' BEGIN
CREATE DATABASE [TESTDB] ON PRIMARY ( NAME = N'TESTDB_Data', FILENAME = N'c:\TESTDB2005_Data.MDF' , SIZE = 7000 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )
LOG ON ( NAME = N'TESTDB_Log', FILENAME = N'c:\TESTDB2005_Log.LDF' , SIZE = 100 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )
END ELSE BEGIN
CREATE DATABASE [TESTDB] ON PRIMARY ( NAME = N'TESTDB_Data', FILENAME = N'c:\TESTDB2008_Data.MDF' , SIZE = 7000 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )
LOG ON ( NAME = N'TESTDB_Log', FILENAME = N'c:\TESTDB2008_Log.LDF' , SIZE = 100 , MAXSIZE = UNLIMITED, FILEGROWTH = 500 )
END

EXEC SP_HELPDB

ALTER DATABASE [TESTDB] SET RECOVERY SIMPLE
GO

USE TESTDB
GO

CREATE TABLE [DBO].[TA](
[COL1] [INT] NOT NULL,
[COL2] [SMALLINT] NOT NULL,
[COL3] [VARCHAR](20) NOT NULL,
[COL4] [VARCHAR](400) NOT NULL,
[COL5] [INT] NULL
) ON [PRIMARY]
GO

DECLARE @CNT_I INT, @CNT_J INT
SELECT @CNT_I = 0, @CNT_J = 0

WHILE (@CNT_I < 10) BEGIN
    SET @CNT_I = @CNT_I + 1
    
    INSERT INTO TA WITH(TABLOCK)
    SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) R
    ,     ROW_NUMBER() OVER(ORDER BY (SELECT 1)) % 32000 R
    ,     LEFT(ISNULL(A.NAME, 'AAA') , 20)
    ,     ISNULL(A.NAME, 'BBB')
    ,     ROW_NUMBER() OVER(ORDER BY (SELECT 1)) R
     FROM SYSINDEXES A
         , SYSINDEXES B
         , SYSINDEXES C
        
    SET @CNT_J = 0
    
    WHILE (@CNT_J < 100) BEGIN
        SET @CNT_J = @CNT_J + 1
        DELETE TOP (1000) FROM TA WITH(PAGLOCK)
    END
    
    EXEC SP_SPACEUSED TA
END

Details
Sign in to post a comment.
Posted by Microsoft on 3/6/2011 at 8:32 PM
This issue was investigated by the development team. It turns out that as part of Merge Performance improvement in 2007, inserts into a heap with no non-clustered indexes with Tablock hint are treated as bulk inserts in order to speed up the insert. Since bulk insert doesn’t use old pages we will end up allocating new pages for insert ops and DB size will only increase regardless of delete operations with time.

For this reason, INSERT with TABLOCK is not re-using the existing pages. This issue is being closed as 'by design'. Our recommendation is to remove TABLOCK hint for the INSERT.

thanks
Sunil


Posted by Microsoft on 2/4/2011 at 10:21 AM
This is indeed a regression from SQL2005. We are looking into it. As a workaround for now, if you don't use TABLOCK as a hint in your INSERT statement, you will be able to re-use the free space

Thanks
Sunil
Posted by Microsoft on 2/1/2011 at 3:30 PM
Sorry for the late response. I was able to repro your case. We will look into this. If I do the INSERT without the TABLCOK, the pages do get used but your repro shows that the behavior with TABLOCK has changed with SQL2008. One question, do you need to use TABLOCK? Can you use PAGLOCK instead. I realize that TABLOCK minimizes locks

thanks
Sunil

Posted by minsouk on 1/7/2010 at 6:25 PM
The WHILE loop in my repro script runs 10 times. I curious why the results of the script which runed 10 times are not same in SQL2005 and SQL2008. Could you run the entire WHILE loop and compare the result of 2005 and 2008?

This is my result as I posted eariler.

ten times delete, sql server 2005 result is below (9.00.4053.00)
------------------------------------------------------------------------
TA    0             6344 KB    6288 KB    8 KB    48 KB

ten times delete, sql server 2008 result is below (10.0.2746.0)
------------------------------------------------------------------------
TA    0             59664 KB    59392 KB    8 KB    264 KB

As you can see above table, sql server 2008 table size is 59MB but sql server 2005 table size is 6MB. I belive the table size should be same after deleting task. Please explain why the table size is different.
Posted by Microsoft on 1/7/2010 at 10:12 AM
Yes, I was not referring to lock escalation. In my testing, I find that deleted space is not being reclaimed in SQL2005 as well. I am not suggesting that it is a desired behavior but this has not regressed in SQL2008. The output of sp_spaceused that I sent shows that there are no rows but the space is not deallocatd. I am sure there is some diff in my testing comapred to yours, but not sure what it is..

thanks
Sunil
Posted by minsouk on 1/6/2010 at 8:49 PM
my reproduce script is prevent lock escalation to tablock
i knew tablock result are same but other lock state are different!

ten times delete, sql server 2005 result is below (9.00.4053.00)
------------------------------------------------------------------------
TA    0             6344 KB    6288 KB    8 KB    48 KB

ten times delete, sql server 2008 result is below (10.0.2746.0)
------------------------------------------------------------------------
TA    0             59664 KB    59392 KB    8 KB    264 KB

sql server 2008 table size is 59MB
sql server 2005 table size is 6MB

Posted by Microsoft on 1/6/2010 at 1:04 PM
I just tried this both on SQL2005 and SQL2008 and the behaviour is not any different. you will need to use TABLOCK hint instead of PAGLOCK to reclaim the space for large delete. Here is one ouput from SQL2005. Note, I deleted all the rows...it shows the row count to be 0. Can you please take another look at your repro. thanks

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.4025.00 (Intel X86)
    Oct 2 2008 00:19:39
    Copyright (c) 1988-2005 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


(1 row(s) affected)

name                                                                                                                             rows        reserved         data             index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
TA                                                                                                                             0         3784 KB            3744 KB            8 KB             32 KB


Sign in to post a workaround.