Data Page Allocations
-
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
This was removed by the editor as SPAM
-
L' Eomot Inversé (12/26/2012)
Here's where I get confused: Can Hugo's ";)" be a misprint for something else?
Nope!
When I started typing my first reaction in this topic, I was wondering whether there was some background cleanup process. During the time I typed that post, I already started doubting, but I was still unsure so posted anyway. Then, Sean's reply removed my final doubt - especially this bit: "This is why when using heaps you have to go back in and do some cleanup periodically".
I also have to admit to not reading the entire page linked to in the question's explanation.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question.
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?
[font="Verdana"]Regards,
Rals[/font]. -
rals (12/27/2012)
Nice question.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, it won't.
As far as I know, shrinking will move the contents of pages markes as "not free" to pages marked as "free". It will not check whether there really is actual data left on those pages.
(*) And as a special bonus, shrinking a database that has a heap can be even more costly than shrinking already is, because every time a page containing heap data is moved, all forwarding pointers and all nonclustered indexes that point to rows on that page have to be updated. See http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ccfd6304-a43b-4a9e-b441-f38e46dc2975 (and especially the replies by Paul Randal)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
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.
gkganeshbe (12/26/2012)
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.
dineshbabus (12/26/2012)
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?
Thanks,
Dinesh.
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,
avg_page_space_used_in_percent,
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'
rals (12/27/2012)
Nice question.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.
-
Sean Pearce (12/28/2012)
gkganeshbe (12/26/2012)
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.
OK, there is something one can do when deleting to ensure that empty pages are deallocated. However the options (if one isn't deleting every row) are rather unpalatable. Is there some way, after deleting rows in a heap without using tablock, to deallocate empty pages - it's easy enough to discover whether there are any empty pages, but is there any way to deallocate them other than creating (and then dropping) a clustered index? If not, I am even more confirmed in my dislike of heaps!
Tom
-
Awesome. (this is really very interesting thing) thanks for the question.:-)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
L' Eomot Inversé (12/28/2012)
Sean Pearce (12/28/2012)
gkganeshbe (12/26/2012)
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.
OK, there is something one can do when deleting to ensure that empty pages are deallocated. However the options (if one isn't deleting every row) are rather unpalatable. Is there some way, after deleting rows in a heap without using tablock, to deallocate empty pages - it's easy enough to discover whether there are any empty pages, but is there any way to deallocate them other than creating (and then dropping) a clustered index? If not, I am even more confirmed in my dislike of heaps!
Personally, I will create a clustered index. I just skip the "and then dropping" bit 😉
-
Thanks!
_______________________________________________________________
"The best way to prepare for programming is to write programs, and
to study great programs that other people have written." – Bill Gates -
Sean Pearce (12/28/2012)
L' Eomot Inversé (12/28/2012)
OK, there is something one can do when deleting to ensure that empty pages are deallocated. However the options (if one isn't deleting every row) are rather unpalatable. Is there some way, after deleting rows in a heap without using tablock, to deallocate empty pages - it's easy enough to discover whether there are any empty pages, but is there any way to deallocate them other than creating (and then dropping) a clustered index? If not, I am even more confirmed in my dislike of heaps!Personally, I will create a clustered index. I just skip the "and then dropping" bit 😉
Oh! I must be getting dim in my old age. That of course is the perfect solution. 😀
Tom
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply