dbcc cleantable batch size explanation

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:

• Reorganize and Rebuild Indexes (Microsoft)
• Rebuild or Reorganize: SQL Server Index Maintenance (Brent Ozar)
• SQLskills SQL101: REBUILD vs. REORGANIZE(Paul Randal)

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