Interview Questions

1. Can anyone predict how long DBCC Check DB will run on any database?

As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. The following are some factors that affect DBCC CHECKDB’s run time: 

  • The size of the database. This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it.
  • The load on the system. DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
  • The capabilities of the system. If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
  • The options specified. If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time.
  • The complexity of the database schema. The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
  • The corruptions that are found. Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
  • The tempdb configuration. DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down. As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it, 
By Hena Parveen 2 0
Is this helpful? Yes No

Submit an interview question

Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of w3Sniff.

Get Started

Comments

Leave a Comment


Check out more interview questions

Based on your skills

MS SQL Server

1702351 1720 23 859

Angular

43951 92 0 46

Wordpress

35443 72 1 36