SQL SERVER – Index Levels and Delete Operations – Page Level Observation

I wrote an article before on SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats. In that article, I promised that I would give a follow up post with a few more interesting details. I suggest that you go over the earlier article first to understand the details on B-Tree and Index Level. Today we will see one of the fascinating aspects of Delete Operations.

Update: This blog post contained few factual errors and they were clearly pointed out by Hrvoje Piasevoli over here. Based on his comment, I have modified this blog post. I will include the comment at the bottom of the blog post for additional clarification. I thank Hrvoje for taking time to correct the details.

There are a few questions I often encounter during my training sessions. Let me try to answer two of them today.

Q: When I delete any data from a table, does SQL Server reduce the size of that table?

A: When data are deleted from any table, the SQL Server does not reduce the size of the table right away, but marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime background process de-allocates the pages and finally reducing the page size.  Follow the example below.

Q: When I delete any data from a table, does SQL Server reduce the size of the B-Tree or change the level of the B-Tree since there are lesser data?

A: No. It does very different behavior. Follow the example.

USE tempdb
GO
-- Create Table FragTable
CREATE TABLE FragTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert one Million Records
INSERT INTO FragTable (ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the spaces
sp_spaceused 'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Delete all from table
DELETE
FROM
FragTable
GO
-- Check the spaces
sp_spaceused 'FragTable'
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE FragTable
GO

Now let us check the details. Here, we check the index details before and after the delete statement.

Click on Image to See Larger Image

It is very clear from the example that after deleting data,  the size of the table is not reduced; same goes with the levels of the indexes- they are not resized by SQL Server. What really changes is the leaf level pages where data are stored.

However if you wait for some time, and run once again fragmentation script you will noticed that levels are still the same but the page_count has been reduced to 1. This is clearly explained by Hrvoje in his comment correcting the blog post over here. The part of technical explanation is reproduced here.

“The behavior differs for Heaps and tables with a clustered index. (The following applies to SQL Server 2008 SP1 and it might differ on other versions)

Heaps:
BOL: “When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.”
While it may appear that the pages are not deallocated due to you example set size, try increasing the number of inserted rows and you will see that the table size gets reduced to around 25MB. The number of index pages reduces accordingly. (Levels do not apply to heaps)
If on the other hand you put a TABLOCK hint in the delete statement all pages will get deallocated.

Tables with a Clustered Index:
Deleting from a table with a clustered index will deallocate deleted pages regardless of the TABLOCK hint. The difference is that without the hint it will happen by a background clean up process and therefore might not be visible immediately after the delete. Try waiting for a few seconds and check space used. TABLOCK will make this operation synchronous.
Number index levels are kept the same, BUT the size of ALL levels gets reduced and not just the leaf levels.

Reducing the size of the heap or the levels of indexes in the B-Tree can be achieved in several ways: if the table is empty (and other constraints satisfied) TRUNCATE TABLE will do the work. Other methods are rebuilding the table/clustered index.” Read original comment here.

Do you find this information useful? What is your opinion about this matter?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Index Levels and Delete Operations – Page Level Observation

    • WHen you use delete the rows actually not deleted but marked as GHOST records, which will finally deleted by dedicated process named GHOST CLEANUP which works relatively slow in background

      To force deletion of ghost records run:

      DBCC FORCEGHOSTCLEANUP

      Like

  1. I’m sorry but this is just wrong. Both the conclusion and the example. The behavior differs for Heaps and tables with a clustered index. (The following applies to SQL Server 2008 SP1 and it might differ on other versions)

    Heaps:
    BOL: “When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.”
    While it may appear that the pages are not deallocated due to you example set size, try increasing the number of inserted rows and you will see that the table size gets reduced to around 25MB. The number of index pages reduces accordingly. (Levels do not apply to heaps)
    If on the other hand you put a TABLOCK hint in the delete statement all pages will get deallocated.

    Tables with a Clustered Index:
    Deleting from a table with a clustered index will deallocate deleted pages regardless of the TABLOCK hint. The difference is that without the hint it will happen by a background clean up process and therefore might not be visible immediately after the delete. Try waiting for a few seconds and check space used. TABLOCK will make this operation synchronous.
    Number index levels are kept the same, BUT the size of ALL levels gets reduced and not just the leaf levels.

    Reducing the size of the heap or the levels of indexes in the B-Tree can be achieved in several ways: if the table is empty (and other constraints satisfied) TRUNCATE TABLE will do the work. Other methods are rebuilding the table/clustered index.

    Regards,

    Hrvoje Piasevoli
    @HrvojePiasevoli

    Like

    • Hi,

      I have ran this example on SQL Server 2008 R2 and got the results.

      I will now run this again with some extensive wait time and will post the results.

      Thank you again for writing on this blog.

      If this is wrong I will correct it with proper credit to you.

      Kind Regards,
      Pinal

      Like

  2. I had deleted almost 200 tables,which was having huge data,
    But could not bale to see any difference in the Size.
    Can u please help me how can i reduce the size of the Database.

    Like

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  4. Thank you very much Pinal for this informative post.

    Perhaps you can explain / clarify a somewhat similar scenario regarding variable length columns.

    When changing / deleting a column with a LOB data type (text, ntext, xml, etc), the space used is not deallocated. It is not even deallocated over time. A DBCC CLEANTABLE(‘DBName’, ‘TableName’) will update the information displayed in sys.dm_db_index_physical_stats and now recognize that clustered index as having unused space, however this seems like a major flaw within SQL Server. I regard the developers and engineers whom build such an amazing technology as ‘brilliant’, so I must believe there is a good reason for this behavior.

    I may also be missing some important details on this, so any clarification on this would be appreciated!

    Thanks so much for all your hard work and great information you provide the community.

    Ryan

    Like

  5. Pingback: SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video « SQL Server Journey with SQL Authority

  6. Hello Pinal,

    How can i delete 3rd row of my table which is having only 1 column and all 5 records have same value?

    Ex. Table-T1

    Id
    ——-
    2
    2
    2
    2
    2

    Thanks

    Avani

    Like

    • Hi Avani,
      Why do you want to delete the 3rd row when you have only one column in the table(T1), there are no different between 1st,2nd,3rd,4th,5th rows, all are same….

      Are you trying to find the unique records out of the table.

      Thanks
      Saravana Kumar C

      Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s