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.

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 (https://blog.sqlauthority.com)