Parallel DBCC– SQL Server September 27th, 2010

Vinod Kumar

SQL Server 2005+ Enterprise Edition gives you the added boost to verify your data quickly and efficiently with multiprocessor support. Database console commands (DBCC) such as DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE check the allocation and integrity of the database objects. In short, these commands are used to ensure that the database is free from corruption—pages are linked properly, the data is valid, and page offsets are correct. It is important to execute these checks against your healthy system to be sure that any internal database problems are corrected early, any hardware or database issues that cause corruption are detected and corrected, and that your database is responding in the appropriate manner to application requests. When you execute DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE against a large database or table stored in the Enterprise Edition of SQL Server, the Database Engine may check multiple objects in parallel if system resources are available and the Database Engine deems the load would benefit from parallel processing. The query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked.

noteParallel DBCC should typically be left enabled, but can be disabled by using trace flag 2528.

Checking database objects in parallel reduces maintenance and operational costs and improve database availability. Large databases may require a long maintenance window to complete validation, which can affect operations during normal business hours. By using multiple processors to complete maintenance operations, SQL Server 2005 Enterprise Edition completes database validation more quickly, which will free up system resources and tempdb, and in some cases reduce locking during your vital production hours.

Digg This

Tags: , , , , , ,

This entry was posted on Monday, September 27th, 2010 at 03:49 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Parallel DBCC– SQL Server”

  1. Jenny Smith says:

    It was certainly interesting for me to read the blog. Thank you for it. I like such themes and everything that is connected to them. I would like to read a bit more soon.

    Jenny Smith
    escort service kiev

  2. MJ says:

    I’ve recently started a blog, the information you provide on this site has helped me tremendously. Thank you for all of your time & work.

Leave a Reply