Hugo Kornelis (12/26/2012)
Basics? That's not how I would describe it!
Thanks for a good question, Sean. I think I once read or heard that after deleting all rows from a page, a cleanup process will eventually free up the page. But I am not sure; in fact, the more I think about it, the more I start to think I am confusing things.
Anyway, can you shed some more light on this?
Haha, not sure I would call it basics either 🙂
You might be thinking of the ghost cleanup that removes rows marked as deleted from the page, but doesn’t include heap pages due to row or page locking.
How to deallocate the unused pages?
As mentioned in the reference URL you can either use the TABLOCK hint, use TRUNCATE TABLE or create a clustered index.
Thanks for such a nice qstn.
Is there any way to deallocate the unsed pages remains after delete?
As well as please let me know the ways to identify is there any unused pages for particular object?
I use the dmv to find heaps with unused space. I’m not worried about objects with less than 100 pages. I look at avg_page_space_used_in_percent and anything under 50% starts to raise alarm bells. Objects with less than 10% page space used are definite candidates.
SELECT OBJECT_NAME(object_id, DB_ID()) AS TableName,
page_count / 128. AS TableSize_MB,
page_count / 128. * (avg_page_space_used_in_percent / 100) AS UsedSize_MB,
(page_count / 128.) - page_count / 128. * (avg_page_space_used_in_percent / 100) AS WastedSpace_MB
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHERE page_count > 100
AND avg_page_space_used_in_percent < 50
AND index_type_desc = 'HEAP'
From this we know that deleting records from the heap will not deallocate pages. Shriking will help to deallocate the pages from the heap ?
I know shrinking is not the best practice to do; just wanted to know shrinking will be of any help here?
No, we don't shrink databases for maintenance. That's another topic on it's own.
I'm glad so many people enjoyed my question, thanks. I have also blogged about this behaviour at http://eohmicrosoft.blogspot.com/2012/12/heaps-of-trouble.html if you want any more info.
The SQL Guy @ blogspot[/url]