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 (https://blog.sqlauthority.com)
10 Comments. Leave new
How to reduce the size of table after deleting the records?
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
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
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
Hi Hrvoje,
You are absolutely correct and based on your feedback, I have modified this blog post.
Many thanks again for taking time and providing accurate feedback.
Kind Regards,
Pinal
Hi,
Can u please help me,how to deallocate the space used by the tavles which had been deleted.
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.
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
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
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