How to determine DBCC Checkdb , execution time?

Question

How to determine DBCC Checkdb , execution time?

sivaprasad

SSCertifiable

Points: 6183
More actions
April 20, 2005 at 11:03 am

#92937

I have a database with size of 150 GB. I like to determine how long will it take to execute "DBCC CHECKDB('database1')" on this database?
What is the basis on which some rough figure/ value can be arrived?
Please do not reply stating that it depends on the server hardware and it's specification.
If you require any additional detail please let me know.
Any approximate figure would do, but expain how would arrive that?
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

It depends on your server hardware. Just kidding , but hardware an RAM definitely has an impact. Dont' know if this helps you or not, but I have a 16.5 Gig user DB on a 2 X 2.4 gig CPU server with 2 gigs of RAM and DBCC CheckDB's complete on this in about 7 1/2 minutes. On the same server, CheckDB on a 10 gig database completes in about 4 minutes. Not sure if there's an explicit way of determining this without actually running the command but I'd be interested in finding out if there is.

My hovercraft is full of eels.

Lowell SSC Guru Points: 323504 More actions · Answer 2

I don't think the time to run DBCC CHECKDB is a function of the size of the database only, but rather a combination of something like

(number of tables) * (number of rows in tables) * (number of pages of memory) / (processor speed * Dedicated RAM) the table is using.

i would figure a 150 GIG database with only one table, with three or four columns, full of work doc image files would process very very quickly , but a db with 15,000 tables and 100,000 columns of data would take much longer, even though the db sizes might be the same.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Here's the scenario:

SQL Cluster HP-DL580/HP-DL380 - active/passive

4 - 3.0 Gh Xeon CPUs

4 Gb RAM --> 3 Gb dedicated to SQL Server

EMC SAN storage --> db data/db logs on separate meta-LUNs

Database size 50 Gb data (the database is allocated larger, this is the actual data usage)

Full DBCC triage (CHECKDB, CHECKALLOC, CHECKCATALOG)

Also, there are at about a dozen other DBCC's executing at the same time on databases ranging in size from 5 Gb to 30 Gb on this same server during our nightkly maintenance.

execution time varies between 22-25 minutes

I'll let you do the extrapolation on a time estimate ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 4

It also depends on what else is running and whether or not the DBCC finds any errors. We have an 200+G database on which DBCC takes about an hour, if no backup is running and no errors are found.

If a database backup is running, it can take hours and hours as it fights contention.

If our DBCC runs for more than an hour, we can almost guarantee that it is going to return consistency errors. In this case, it can sometimes run for 4 or 5 hours before returning those errors.

Good luck.

J. Bagwell

UVA Health System