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.
SELECTOBJECT_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
FROMsys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
WHEREpage_count > 100
ANDavg_page_space_used_in_percent < 50
ANDindex_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]