Faster DBCC CHECKDB by using Trace Flag 2562 and 2549
Skrivet den 05 juni 2012 i SQL Server 2008R2, SQL Server 2012, Level 400, SQL Server maintenance, Steinar Andersen, Trace Flag, sv, en
Today many of our customers have large databases, and today I would consider a database over 1 TB as large. The larger the database, the more time it takes to do maintenance of various kinds. One of the procedures that can take a long time is DBCC CHECKDB in various forms.
Therefore it can be good to know that there are 2 Trace Flags for SQL Server 2008 R2 and SQL Server 2012 that in certain cases can help reduce the runtime for DBCC CHECKDB considerably. Specifically when you have a database that is split in several files that each resides on their own physical disk. NOTE: A side effect that you should be aware of, is the possibility of increased Tempdb space usage.
The Trace Flags are 2562 and 2549, and they are described a little more in detail here http://support.microsoft.com/kb/2634571 and here http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx
Please remember that Trace Flags are advanced options, and should be used with care. And if you need assistance of any kind with Microsoft SQL Server, please feel free to contact us.