First published on MSDN on Mar 01, 2016
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:
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.)
Ajay Jagannathan - Principal Program Manager
Bob Dorr - Principal SQL Server Escalation Engineer
