0

I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example of what the table architecture looks like:

myTable myTable_Q4_21 myTable_Q3_21 myTable2_Q4_21 myTable2_Q3_21

I am wondering if after dropping these tables and shrinking the database to reclaim the newly freed up space if I will need to either ALTER..REBUILD each remaining HEAP table or put a CX on each table and remove them afterwards.

Improve this question
0

3 Answers 3

Reset to default
6

Will you need to? No. But consider replacing them with clustered columnstore tables with archive compression. They will be defragmented, smaller, and much faster to scan.

Improve this answer
3

"Need" is pretty strong phrasing. Let us be more specific and then you can determine whether you want to do this:

A heap isn't a linked list. I.e., SQL Server when it accesses a heap can either find the rows through a B-tree index or for a table scan use the IAM pages (a map of which extents the heap is using).

Shrink moves pages from end-of-file towards beginning of file (without regard to the order of pages for a linked list). For a linked list (a B-tree index), than can cause SQL Server to "jump back and forth" in the database file when it read lots of pages (a scan or partial scan). With spinning disks, this can be bad. Less bad with modern disk subsystems (which is why we focus less on defrag nowadays). This is what we call external fragmentation.

Since there's no concept of "following the linked list" for a heap, the consequence of shrink is close to none for a heap. Sure, the pages for a heap can end up more spread-out in the file compared to earlier. But we don't have the "jump back and forth" aspect for a heap. There are also details like SQL Server using scatter-gather I/O to minimize the effect of "spread" of data.

There are other things, like your pages might not be full (internal fragmentation) and forwarded records (a row was updated, moved to some other location and on the old location there's a pointer to the new location). This is not changed by shrink. Sure, it might be beneficial to do a rebuild, but these aspect were not changed by you doing a shrink.

I.e., short answer is: a shrink doesn't make a heap less efficient.

And, as already mentioned by David, you might want to not have these as heaps in the first place...

Improve this answer
1

Either ALTER TABLE ... REBUILD or creating a clustered index will do the job done.

Just before shrink I would recommend to do the backup of the database.

Here is the nice article about maintaining of heaps:

https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/sql-server-heaps-and-their-fragmentation/

Improve this answer
2
  • Good article, but I was inquiring specifically about the SHRINK operation in regards to HEAPs specifically. From what I have read, fragmentation resulting from the SHRINK operation has a similar effect on HEAPs as it would a typical clustered table so I shall treat them the same.
    – Data Dill
    Commented Jan 19, 2022 at 15:55
  • Difference between heap and clustered is the clustered is ordered by some columns and heap is not and more or less that is all so you are right.
    – Marcin
    Commented Jan 19, 2022 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.