Consistency checks on large databases

This is a bit of a follow up from my last post about torn pages in SQL Server. Within that post I mentioned the DBCC CHECKTABLE command which performs a consistency check against a specified table name.

It reminded me of a situation that I've certainly come across a few times where a databases size has meant it's outgrown the acceptable maintenance window and that resource intensive operations like consistency checks are now overlapping into production hours. This can seriously impact items like ETL processes so a clever approach that balances both consistency and resource contention is now required. 

One way to achieve this is to  split up the consistency checks covering smaller objects and native functionality allows us to do just that, we can perform the checks at the table level or indeed if they are implemented at the filegroup level too using the DBCC CHECKFILEGROUP command.

How to go about this is pretty straightforward; take the list of tables, split them into equal(ish) groups. The groups now form a pool of objects and within a nightly (or daily) window perform the check on each object in the pool. This effectively spreads a database consistency check over multiple days, you avoid the impact on production activities but also ensure all objects are checked over time.


DBCC CHECKTABLE ('dbo.ReallyImportantTable')
GO


DBCC CHECKTABLE ('dbo.AnotherReallyImportantTable')
GO

So the next question is how often should consistency checks be performed? For me the answer is simple; as often as possible. I've always suggested nightly checks where possible and if the database size means we can't do this then we start to break down the objects. Perhaps we have two pools of tables and on our first maintenance night we do pool one, night two we perform the checks on pool two and we repeat the process throughout the week. It could be 3 pools, even more - but at least the checks are being performed and are avoiding other processes.

The golden rule is to ensure database consistency and there are occasions that due to its intensive nature we have to find ways of avoiding impact on to production systems. So it's worth knowing that there are options available that make it more manageable, avoid the impact on these other processes and maintain confidence that your databases (or rather their objects) are consistent; and that is the most important thing.