Indexed Computed Columns and DBCC CHECKDB

Recently I spoke a couple times about using indices to solve some otherwise unsolvable performance problems, like turning non-searchable arguments into SARG ones.

Now it’s time to point out a hidden cost of this solution — incredibly slow database consistency check.

Problem

Let me illustrate this problem with a sample database I used i.e. at SQLSaturday Slovenia (if you wish to play with it, a setup script is here).

To establish a baseline we execute a DBCC CHECKDB on a database with 5 tables and without any indexed computed columns:

USE Indices;
GO

DBCC CHECKDB WITH NO_INFOMSGS;
GO

DECLARE @StartTime DATETIME;
SET @StartTime = GETDATE();
DBCC CHECKDB WITH NO_INFOMSGS;
PRINT ''Initial DBCC CHECKDB duration: '' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) as varchar(10)) + '' ms''
GO

 

On my machine it takes about 2 seconds.

Let’s check what will happen with two indexed computed columns:

ALTER TABLE [dbo].[SellingPriceTemplate]
ADD LineTotal AS ((SubTotal + TaxAmount) * OrderQty);
GO

CREATE INDEX SellingPriceExtendedAmount
ON [dbo].[SellingPriceTemplate](LineTotal)
GO

ALTER TABLE [dbo].[SellingPriceTemplate]
ADD FillerUp AS (UPPER(RIGHT(Filler,1)));
GO

CREATE INDEX SellingPriceInitial
ON [dbo].[SellingPriceTemplate](FillerUp)
GO

DBCC CHECKDB WITH NO_INFOMSGS;
GO

DECLARE @StartTime DATETIME;
SET @StartTime = GETDATE();
DBCC CHECKDB WITH NO_INFOMSGS;
PRINT ''DBCC CHECKDB duration with TWO indexed computed column: '' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) as varchar(10)) + '' ms''
GO

 

Database consistency check time went up to about 6 seconds as SQL Server has to compute those values for each row and compare them with index keys. For large database with lot of indices on computed columns this exponential decrease in performance means that a DBA would not be able to execute this command any more.

Workaround

The easiest workaround is to skip logical consistency tests altogether. If page checksums are enabled at a database level, this will more or less work which means that a needed time will be even shorter, but some corruption can be not found:

DBCC CHECKDB WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO

DECLARE @StartTime DATETIME;
SET @StartTime = GETDATE();
DBCC CHECKDB WITH PHYSICAL_ONLY, NO_INFOMSGS;
PRINT ''DBCC CHECKDB PHYSICAL_ONLY duration with TWO indexed computed column: '' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) as varchar(10)) + '' ms''
GO

 

The other bypass is more complicated to implement but safer — all you need to do is disable all indices on computed columns, check a database consistency and enable (rebuild) them afterwards:

ALTER INDEX SellingPriceExtendedAmount
ON [dbo].[SellingPriceTemplate]
DISABLE;

ALTER INDEX SellingPriceInitial
ON [dbo].[SellingPriceTemplate]
DISABLE;

DBCC CHECKDB WITH NO_INFOMSGS;
GO

DECLARE @StartTime DATETIME;
SET @StartTime = GETDATE();

DBCC CHECKDB WITH NO_INFOMSGS;

PRINT ''DBCC CHECKDB duration with disabled indices on computed column: '' + CAST(DATEDIFF(ms, @StartTime, GETDATE()) as varchar(10)) + '' ms''
GO

 

Lesson learned — indexed computed columns are great way to tremendously speed up an overall system performance. However, this gain does not come out of thin air. In addition to expected insertion, modification, and deletion time overhead, you will have to deal with this DBCC nuisance.

See you

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Performance and tagged , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required