Data Page Allocations

  • Sean Pearce

    SSCoach

    Points: 15750

    Comments posted to this topic are about the item Data Page Allocations

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Lokesh Vij

    SSChampion

    Points: 10836

    Nice question Sean. Thanks for reminding the basics!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Ganesh Kamal

    SSChasing Mays

    Points: 602

    How to deallocate the unused pages?

  • Dineshbabu

    Hall of Fame

    Points: 3220

    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..

  • Sean Lange

    SSC Guru

    Points: 286536

    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/

  • Sean Lange

    SSC Guru

    Points: 286536

    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/

  • sestell1

    SSChampion

    Points: 10230

    Nice question. 🙂

  • Revenant

    SSC-Forever

    Points: 42467

    A good question! Thanks!

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Matt Marston

    SSCrazy

    Points: 2413

    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.

  • SanDroid

    SSChampion

    Points: 10068

    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.

    😎

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Great QotD..

    Thanks for asking..:cool:

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply