DBCC check DB on SQL Server 2016

Question

Currently we are using hallengren for Database integrity (DBCC).

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES',
@PhysicalOnly = 'Y',
@LogToTable = 'Y'

Which is working fine with no issue. taking 5 hours in prod envi. (Which is expected).

Is there any way i can reduce the executing time by dividing DBCC job or in someother way. so that i want only to run max 2 hrs.

Or any Microsft provided script which can improve?

This is SQL Server 2016 AlwaysOn (Primrary+Secondary)

Answer 1

Hi @Vijay Kumar ,

Firstly, good to know that there is no issue when the database integrity check has been finished. Is this database is very large? Because it takes 5 hours. If you have a very large database, please refer to CHECKDB (Part 6): Consistency checking options for a VLDB to get more information, see below:
• Run a bi-weekly DBCC CHECKALLOC
• Figure out your largest tables (by number of pages) and split the total number into 7 buckets, such that there are a roughly equal number of database pages in each bucket.
• Take all the remaining tables in the database and divide them equally between the 7 buckets (using number of pages again)
• On Sunday:

• Run a DBCC CHECKALLOC
• Run a DBCC CHECKCATALOG
• Run a DBCC CHECKTABLE on each table in the first bucket
  • On Monday, Tuesday, Wednesday:
• Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th buckets, respectively
  • On Thursday:
• Run a DBCC CHECKALLOC
• Run a DBCC CHECKTABLE on each table in the 5th bucket
  • On Friday and Saturday:
• Run a DBCC CHECKTABLE on each table in the 6th and 7th buckets, respectively

And also, you can use below SQLQuery to monitor the progress of DBCC:

SELECT  session_id, request_id, percent_complete, estimated_completion_time ,  
DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,   
 start_time, status, command    
 FROM sys.dm_exec_requests  
WHERE command like '%DBCC%'  

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Is there any way i can reduce the executing time by dividing DBCC job or in some other way. so that i want only to run max 2 hrs.

You are already running with parameter @physicalonly which itself does not do complete checks. It does only physical level checks not logical. So reducing the checks to 2 hours would ideally be not possible if you run complete checkdb.

You can ofcourse can figure out the database which is important and give it a 2 hour window. Rest all can be run in separate windows. This what most people do out there.

Answer 3

I am not sure that Ola's tools take into consideration the MAXDOP setting - which is now available for Enterprise Edition. If you have Enterprise Edition - and your server/database MAXDOP setting is not the default value of 0, then CHECKDB will run at the server/database specified setting.

If you have Standard Edition - then CHECKDB always runs single-threaded.

Answer 4

The most obvious thing IMO would be to check some databases on Monday, some other on Tuesday etc.

Assumes of course that you have several databases and that not one of them is a huge database compared to the others.