DBCC Extended Checks

The SQL Server CSS team has a new post on DBCC performance improvements:

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. 

This is telling me that we’ll want to have two separate CHECKDB processes, one which regularly runs CHECKDB (or CHECKTABLE) and one which occasionally runs CHECKDB with EXTENDED_LOGICAL_CHECKS.

Related Posts

Handling MAXDOP On Azure SQL Database

Arun Sirpal plays with MAXDOP settings on Azure SQL Database: Can we change it? No. EXEC sp_configure 'cost threshold for parallelism', 10; GO RECONFIGURE; GO Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure ‘sp_configure’. Msg 40510, Level 16, State 1, Line 11 Statement ‘CONFIG’ is not supported in this version of SQL Server. […]

Read More

Database Migration With dbatools

Jess Pomfret shows how easy it is to migrate databases from one SQL Server instance to another using dbatools: Now that there are no connections we can move the database.  Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the […]

Read More


March 2016
« Feb Apr »