patternsqlMinor
dbcc cleantable batch size explanation
Viewed 0 times
sizedbccbatchcleantableexplanation
Problem
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..
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..
Solution
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:
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)
Context
StackExchange Database Administrators Q#234041, answer score: 8
Revisions (0)
No revisions yet.