none
Rebuidling heap tables RRS feed

  • Question

  • Hi All,

    We are seeing heavy fragmentation 99% with page count more than 10000 pages.
    So,  rebuilding those HEAP Tables using ALTER table tname REBUILD command.

    My question is, if I have 100 such tables which of the below 2 would perform better in terms of performance (time, transaction log management etc..)
    Database is in FULL recovery model. SQL Server 2012 Enterprise Edition.

    ALTER TABLE T1 REBUILD;
    ALTER TABLE T2 REBUILD;
    ALTER TABLE T3 REBUILD;
    ALTER TABLE T4 REBUILD;
    :
    ALTER TABLE T100 REBUILD;


    ------------- [OR]-----------------

    ALTER TABLE T1 REBUILD;
    GO
    ALTER TABLE T2 REBUILD;
    GO
    ALTER TABLE T3 REBUILD;
    GO
    ALTER TABLE T4 REBUILD;
    GO
    :
    ALTER TABLE T100 REBUILD;
    GO

    Thanks,
    Sam
    Friday, September 20, 2019 12:18 PM

Answers

  • Rebuilding a heap does not do anything. 

    It removes fragmentation. And heaps are veryu prone to fragmentation if you insert and delete rows all the time. You may end up with pages that are very sparsely used. That wastes a lot of space in the buffer pool.

    Also, do not reinvent the wheel.  I highly suggest you use Ola's scripts for index maintenance rather than rolling your own.

    According to another post here, Ola's script does not handle heaps. I don't use them myself (since I mainly work as a developer), so I don't know for sure.

    As for the original question, execution time will be the same if you have one single batch or if you have one batch per statement. However, if there is an error with one of the statements, and you have a single batch, the rest may not be executed. Better chances if you have one batch per statement.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:27 PM

All replies

  • Heap table means NO clustered index on it. Technically their is no meaning of fragmentation in heap as they are un-orderly pages.

    Please go through below articles it may helps you on it.

    https://social.technet.microsoft.com/wiki/contents/articles/34421.sql-server-what-does-fragmentation-means-for-a-heap-table.aspx


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Friday, September 20, 2019 12:28 PM
  • Rebuilding a heap does not do anything.  In addition, if you have tables with 10,000 pages and no clustered index, you likely need to add a clustered index to those tables.  Every query to those table is scanning all pages.

    Also, do not reinvent the wheel.  I highly suggest you use Ola's scripts for index maintenance rather than rolling your own.

    https://ola.hallengren.com/

    Friday, September 20, 2019 12:38 PM
    Moderator
  • Thank you All. Got whatever you are trying to say. completely valid.

    But these tables are created by 3rd party tool and my question is which of the 2 implementations works better than the other. Is it a single txn or multiple txns or batches in that case. Need clarity on that.

    Friday, September 20, 2019 7:15 PM
  • Hi All,

    Thanks for the responses. All your suggestions are perfectly valid.
    However, my question is different. These tables are created from 3rd party tool.
    Question is, for rebuilding HEAPS which of the above 2 methods is efficient.
    How many number of transaction will be in 1st implementation and how many in 2nd implementation.
    Which one is better and why ?

    Thanks,
    Sam
    Friday, September 20, 2019 7:18 PM
  • Rebuilding a heap does not do anything. 

    It removes fragmentation. And heaps are veryu prone to fragmentation if you insert and delete rows all the time. You may end up with pages that are very sparsely used. That wastes a lot of space in the buffer pool.

    Also, do not reinvent the wheel.  I highly suggest you use Ola's scripts for index maintenance rather than rolling your own.

    According to another post here, Ola's script does not handle heaps. I don't use them myself (since I mainly work as a developer), so I don't know for sure.

    As for the original question, execution time will be the same if you have one single batch or if you have one batch per statement. However, if there is an error with one of the statements, and you have a single batch, the rest may not be executed. Better chances if you have one batch per statement.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:27 PM
  • It can produce transaction-log bloat which hits the over all performance. Think about the process the scan the log or perhaps Log Shipping job that needs to move the log file to the remote server. All this affects performance.

    So, to answer the question ,we need to create a clustered index on that table..That is simple answer to the question.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, September 22, 2019 4:43 AM
    Answerer
  • Hi

    If you really face a scenario where heap table is fragmented and slowing queries it would be better creating a clustered index on table than rebuilding it. The reason is when you rebuild heap all underlying Non Clustered indexes are also rebuilt causing the rebuild process to take much longer time, utilizing lot of resources and bloating transaction log.

    If there is no way of managing them directly you can rebuild them altogether rather having other thoughts.

    Hope this is helpful!!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 6:29 AM
  • Thank you All. Got whatever you are trying to say. completely valid.

    But these tables are created by 3rd party tool and my question is which of the 2 implementations works better than the other. Is it a single txn or multiple txns or batches in that case. Need clarity on that.

    You have only 2 options like others said

    1. Create clustered index 

    2. if you are using SQL Server 2008 and above rebuild the heap. Again if there are NCI's you should be ccareful as they would also be rebuild.

    If you really want to rebuild do one at a time, that gives you more control


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, September 23, 2019 7:07 AM
    Moderator