0

I have a maintenance task, which do a DBCC CHECKDB WITH PHYSICAL_ONLY on the Databases on my server (SQL Server 2016 Standard Edition).

This usually takes about 7h30 for about 120 databases and it is an acceptable time. But once in a while (like yesterday), it took 10 hours to do this operation on the same amount of databases.

From the logs, I know that every DBCC CHECKDB took longer than usual to run. It's not only one DBCC CHECKDB that took 3 hours more. It's all individual CHECKDB that took a few minutes more.
The CHECKDB are running during the night, when clients are still using the Database, but where there's less activity.The databases have increased in size, but not that much that it would cause a 3 hours increase.

Other than client's activity on the Database, which can cause some delays, what can cause DBCC CHECKDB to take a much longer time than usual? I need to at least have an explanation to give to my boss as to why once in a while it does this and then reverts back to a normal time.

Thanks

closed as too broad by LowlyDBA, Erik Darling, RDFozz, SqlWorldWide, mustaccio Oct 18 '17 at 19:29

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Aside from client activity, was there any other maintenance going on? Is this a VM? Are the drives local or on a SAN? Was client activity different? There are tons of possible reasons -- no one here knows your environment better than you do. – Erik Darling Oct 18 '17 at 14:39
  • Sounds like a noisy neighbor scenario, given no other details. – John Eisbrener Oct 18 '17 at 14:55
  • 3
    @JohnEisbrener TURN DOWN FOR WHAT – Erik Darling Oct 18 '17 at 15:06
  • Thanks for the suggestions, this is not a VM. It's local drives in RAID. I will try to investigate more – Danielle Paquette-Harvey Oct 18 '17 at 15:29
1

Lots of options, kind of hard to speculate:

  • Slower IO to disks, perhaps due to activity elsewhere on the SAN?
  • Other activity on the box interfering (user activity, maintenance jobs, other databases, etc.)
  • Memory or CPU pressure on the box (admin logged on locally leaving things running, big SSIS packages eating up CPU or local memory)

Narrowing down which of these it might be won't be easy, you'll probably have to turn on some long-running perfmon logs and do some trending over time.

Not the answer you're looking for? Browse other questions tagged or ask your own question.