DBCC CHECKDB split for VLDB

[ChrisWoods1977]

It would be great if the consistency checks could split up a DB to follow Paul Randall's suggested split for VLDBs found at: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/

Figure out your largest tables (by number of pages) and split the total number into 7 buckets, such that there are a roughly equal number of database pages in each bucket. Take all the remaining tables in the database and divide them equally between the 7 buckets (using number of pages again) On Sunday: Run a DBCC CHECKALLOC Run a DBCC CHECKCATALOG Run a DBCC CHECKTABLE on each table in the first bucket On Monday, Tuesday, Wednesday: Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th buckets, respectively On Thursday: Run a DBCC CHECKALLOC Run a DBCC CHECKTABLE on each table in the 5th bucket On Friday and Saturday: Run a DBCC CHECKTABLE on each table in the 6th and 7th buckets, respectively

[markaugust]

I've been working on something similar to this. It doesn't follow this bucket approach, but rather does the checks in a rolling fashion, as we only have the ability to run checks during the weekends.

See MR #323. Feel free to contribute to/use my fork.

[EitanBlumin]

This is a much-needed feature. There's actually something like this already implemented by Microsoft's Tiger team in the Tiger Toolbox maintenance solution here:

https://github.com/microsoft/tigertoolbox/blob/master/MaintenanceSolution/5_job_Maintenance_MEA.sql#L628-L665

and here:

https://github.com/microsoft/tigertoolbox/blob/master/MaintenanceSolution/5_job_Maintenance.sql#L630-L667

It basically splits the tables into "daily" buckets based on size, while maintaining an isdone column for each bucket.

I suppose something like this could be implemented in Ola's solution as well, but also an alternative variation of it for those able to run the maintenance on weekends only:

• Split the tables into a set number of buckets, based on a parameter value instead of the number of weekdays.
• Instead of an isdone column, maintain a last_check_datetime column.
• Each check will select the bucket with the oldest last_check_datetime value.
• The @TimeLimit parameter could be utilized to control your maintenance window size, and a bucket will be selected for a check as long as we're within the allowed time limit.
• The Tiger Toolbox variation re-creates the buckets table upon each execution, but that won't be possible with this approach that I'm suggesting. The last_check_datetime would somehow need to be persisted between different executions.
  • Lately, I've been experimenting with utilizing extended properties to save "meta-data" about objects. I'm thinking that maybe those could be used by saving the last_check_datetime as an extended property per each table.
  • Since a table's size can change between different executions, some kind of reconciliation would need to be implemented in order to determine which tables should be assigned to which buckets. Currently, I'm thinking about a simple ORDER BY rule which gives priority to last_check_datetime first, and the table's size second.