6

I have a very large table with 500 mil rows and a Text column that I will be dropping. In my Dev environment, I have dropped the column and began the reclaim process, but im not sure what the batch size on the “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 100000)” statement actually does.

I have tried setting it to 5, expecting it to check the first 5 rows and end. “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 5)” and it took 28 hours. So I restored the db, set it to 100,000 and it took 4 hours

Actual Question: Does the batch size tell the dbcc cleantable how many rows to do at a time and continuously keep running 100K at a time till it goes thru all 500mil rows? Or once I run the 100,000 do I have to run it again till I do all 500 mil rows?

On my second test, (running the 100K once) I was able to reclaim 30GB. Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..

Improve this question

2 Answers 2

Reset to default
4

According to the Microsoft documentation the Batch Size tells the DBCC CleanTable the number of rows to process per transaction. This relates to the number of rows that the DBCC CleanTable processes internally as the DBCC CleanTable process runs.

By taking the example in the documentation and modifying to add a million rows and then running the sample script multiple times with varying values for batch size ( see below) it appears that specifying a small batch size increase the execution time as DBCC CleanTable is only operating on the number of rows specified in the batch size.

  • No Batch size specified
  • A batch size of 5
  • A batch size of 100,00
Improve this answer
1
  • So just to confirm, the process will go thru the entire 500Mil rows, just "exclusively locking" 100K at a time and also allow for backup logs to occur.
    – Tomasz
    Apr 5, 2019 at 15:03
8

In addition to the great answer by armitage you probably do not need to use DBCC CLEANTABLE in your scenario.

You state

Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..

The best practices in the Microsoft documents says:

DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.

It seems like time and space are your biggest goals. Generally rebuilding an index is quicker (but more resource intensive) than a reorg.

As you are working on a Development server.

Just rebuild your indexes and you will get the benefits of the index reorg and the DBCC CLEANTABLE at the same time, and probably much quicker.

Note Rebuild and Reorganize are not the same thing:

Improve this answer
4
  • i thought the same thing and ran the test in reverse. 1) dropped the column 2) defrag all indexes (only reclaimed 30GB) 3) ran cleantable and got 60gb... looks like i need both, this is a one time thing
    – Tomasz
    Apr 5, 2019 at 15:09
  • @Tomasz I edited my answer, not sure what you mean by 'defrag all indexes' but Reorg (what you said in your question) & Rebuild (what I said in this answer) are not the same thing.
    – James Jenkins
    Apr 5, 2019 at 15:32
  • 1
    ah, sorry. i reorganized them each time. i will run one more test where i will drop the column and rebuild the index and share the results. thank you.
    – Tomasz
    Apr 5, 2019 at 15:49
  • 1
    I ran the rebuild (online) for the clustered index (where the LOB data resided) and it worked great. They only issue, for me, was that I didn’t have enough space provisioned on the dev server for the log and there was no way of backing up the log while the rebuild was running, had to let it finish (took a few tries). Even though I will be using this method instead of the cleantable to reclaim the space, I have to award the correct answer to Armitage because it did answered my original question. Thank you for your help James.
    – Tomasz
    Apr 8, 2019 at 14:24

Your Answer

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

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