Moved from: bobsql.com
Last week’s post (SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better) talked about several improvements to DBCC CHECKDB to make it run faster. In today’s post, we will talk about additional improvements to extended logical checks.
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 reported 10x slower performance for CHECKDB if the database has filtered indexes), if the database tables being checked contain one of the following data types or indexes:
-
Filtered indexes
-
Persisted Computed columns
-
UDT 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 columns will not be run by default to 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 when EXTENDED_LOGICAL_CHECKS option 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 the EXTENDED_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.)
Posted at https://sl.advdat.com/35lzcpwhttps://sl.advdat.com/35lzcpw
