When checking database consistency using DBCC CHECKDB, in addition to the amount of data or number of tables and indexes, the duration can be exponentially longer, (for example: some customer workloads have reported10x slower performancefor CHECKDB if the database has filtered indexes), if the database tables being checked contain one of the following data types or indexes:
Persisted Computed columns
UDT columns based on CLR assemblies (such as clearing has_unchecked_assembly_data value)
Running consistency checks (DBCC CHECKDB) on a database containing these can take significantly longer. For instance, when a non-clustered index uses a persisted computed column, the value of the computed column is recomputed for every row based on the column definition during the consistency check.
Workarounds used by customers:
Perform full consistency check less often
Skip logical consistency check altogether by using PHYSICAL_ONLY option with CHECKDB
Disable indexes before consistency check
New SQL 2016 Behavior Starting with SQL Server 2016, additional checks on filtered indexes, persisted computed columns, and UDT columnswill not be run by defaultto avoid the expensive expression evaluation(s.) This change greatly reduces the duration of CHECKDB against databases containing these objects. However, the physical consistency checks of these objects is always completed. Only whenEXTENDED_LOGICAL_CHECKSoption is specified will the expression evaluations be performed in addition to already present, logical checks (indexed view, XML indexes, and spatial indexes) as part of theEXTENDED_LOGICAL_CHECKS option.
For filtered indexes, CHECKDB has also been improved to skip records that do not qualify as being indexed by target NC index.
‘It Just Runs Faster’– Out of the box SQL Server 2016 DBCC provides you better performance, scaling while shrinking your maintenance window(s.)