Data Page Allocations
-
Comments posted to this topic are about the item Data Page Allocations
-
Nice question Sean. Thanks for reminding the basics!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Basics? That's not how I would describe it! :w00t:
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?
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/ -
How to deallocate the unused pages?
-
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.
--
Dineshbabu
Desire to learn new things.. -
Great question. A bit hard to read through the formatting but is not the author's fault.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hugo Kornelis (12/26/2012)
Basics? That's not how I would describe it! :w00t: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?
Hugo, pages in a heap are not deallocated when deleting. This is why when using heaps you have to go back in and do some cleanup periodically.
The following is quoted from BOL. http://msdn.microsoft.com/en-us/library/ms189835%28v=sql.100%29.aspx
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. To delete rows in a heap and deallocate pages, use one of the following methods.
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take a shared lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).
Use TRUNCATE TABLE if all rows are to be deleted from the table.
Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Nice question. 🙂
-
A good question! Thanks!
-
Good question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
I answered correctly, but for the wrong reason. I'm well aware that when deleting rows SQL Server may internally mark the row as deleted and then come back and clean them up later. These rows that have been marked as deleted are called "ghost" records. I was wrong though, because I didn't realize that ghost records are not used by SQL Server for heaps (unless deleted when using SNAPSHOT isolation). I was fully unaware that deleting from a heap could result in records that would never automatically be deallocated by SQL Server.
For more information on ghost records see An in-depth look at Ghost Records in SQL Server, Inside the Storage Engine: Ghost cleanup in depth, and Ghost cleanup redux.
-
Nice QotD, thank you. I think that one method for making sure pages are deallocated on record deletes was not mentioned. Don't create HEAP tables for storing record level Transactional data in.
Not what a HEAP is for.
Why would you want a table with no Primary Key or Unique Constraint in a TSQL database that row level deletes will occur on? So you end up having errors and performance issues when trying to delete records? 😛
This is why there are always issues deleting or updating a row that of values that exists more than once if you have HEAPS and other table structures that allow rows with identical values to exist.
😎
-
Sean Lange (12/26/2012)
Hugo Kornelis (12/26/2012)
Basics? That's not how I would describe it! :w00t: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?
Hugo, pages in a heap are not deallocated when deleting. This is why when using heaps you have to go back in and do some cleanup periodically.
The following is quoted from BOL. http://msdn.microsoft.com/en-us/library/ms189835%28v=sql.100%29.aspx
(snip)
Thanks for refreshing my memory, Sean! 😉
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)
Sean Lange (12/26/2012)
Hugo Kornelis (12/26/2012)
Basics? That's not how I would describe it! :w00t: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?
Hugo, pages in a heap are not deallocated when deleting. This is why when using heaps you have to go back in and do some cleanup periodically.
The following is quoted from BOL. http://msdn.microsoft.com/en-us/library/ms189835%28v=sql.100%29.aspx
(snip)
Thanks for refreshing my memory, Sean! 😉
Here's where I get confused: Can Hugo's ";)" be a misprint for something else? I don't see how a verbatime quotation of some words from BoL which were alrady quoted verbatim in the explanation could be regarded as a memory-refresh. But this (heaps - tables with no clustered index) are something I don't really understand, so maybe I'm thoroughly missing something which is obvious to people who know such tables.
Anyway, I would very much like to see how these pages might eventually be deleted, once the records have been deleted the "wrong" way.
Anyway, I agree with Hugo that this isn't a "back to basics" question - it's far more abstruse than that. Or maybe I'm far too obtuse to understand why it counts as "back to basics".
Tom
-
Great QotD..
Thanks for asking..:cool:
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply