What if interviewer is wrong?

Question

What if interviewer is wrong?

dan-572483

SSCoach

Points: 15870
More actions
July 2, 2014 at 9:29 am

#290537

During a job interview yesterday the DBA interviewing me was incredulous that at a former employer were running DBCC CHECKDB on a 1TB database nightly. He felt it would take more than one night to accomplish before users come online in the morning. I'm sure I recall at this site such a job consistently completing in the 3-5 hour ballpark - on a server with 130GB of RAM and fast SAN drives of course. But he was sure that "no one" runs CheckDB directly on a 1TB production database every day. He also seemed unaware that DBCC CheckDB runs against a hidden snapshot to avoid locking tables and other performance issues - something I've seen Paul Randal write about several times.
I have an email out to a DBA who still works at my former company asking about the length of time this CheckDB job takes. Until I hear back, is 3-5 hours to run CheckDB on a 1TB db on a high-performance system really unheard of? How do you respond to an interviewer who thinks you don't know what your talking about when it is he who may be operating like it's 2004 and he's running SQL 2000 on a 32-bit system?

Viewing 12 posts - 1 through 11 (of 11 total)

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

j.a.c SSCrazy Points: 2283 More actions · Answer 1

We get about 3.6 GB per minute of throughput on our old Dell 2950 (with fiber channel disk attached to a 2Gb fabric) when running DBCC CHECKDB WITH PHYSICAL ONLY. Not sure if you were running the consistency check with that modifier but I say that 3-5 hours seems completely possible even on a busy system.

Thanks,

Andy

dan-572483 SSCoach Points: 15870 More actions · Answer 2

Well, I have confirmed with someone still working at my former employer that a DBCC CHECKDB job I set up to run overnight on a 1TB+ production database shows recent run times of 4.5 to 4.8 hours.

Too bad I may have blown an interview because the interviewer was unaware that DBCC CHECKDB runs against a hidden snapshot to avoid lock-related performance issues and believed it was impossible to run DBCC CHECKDB on a 1TB database in less than 5 hours.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

dan-572483 (7/9/2014)
Well, I have confirmed with someone still working at my former employer that a DBCC CHECKDB job I set up to run overnight on a 1TB+ production database shows recent run times of 4.5 to 4.8 hours.
Too bad I may have blown an interview because the interviewer was unaware that DBCC CHECKDB runs against a hidden snapshot to avoid lock-related performance issues and believed it was impossible to run DBCC CHECKDB on a 1TB database in less than 5 hours.

Perhaps he actually was aware of that fact and was also aware of the fact that sometimes DBCC CHECKDB cannot create the hidden snapshot you speak of and was hoping that you'd explain how your previous company managed to make it so on a 1TB database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan-572483 SSCoach Points: 15870 More actions · Answer 4

How can you tell if it creates a hidden snapshot - given that it's hidden?

Whether it did or not, it still runs in under 5 hours and there were no complaints about performance from the overnight shift.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

dan-572483 (7/10/2014)
How can you tell if it creates a hidden snapshot - given that it's hidden?
Whether it did or not, it still runs in under 5 hours and there were no complaints about performance from the overnight shift.

That's kind of the point... did you tell the interviewer that it usually creates a hidden snapshot but not always?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You tell because there will be an error in the error log saying that the snapshot couldn't be created and that CheckDB is attempting to take table locks. It's pretty rare, only cases I've seen (other than explicitly running checkDB WITH TABLOCK) was when the previous checkDB run failed to drop the hidden snapshot properly. It's fixed by a restart of SQL.

There's a limit on the size of the sparse file, but you typically hit that during CheckDB's execution, not at the start (and it causes CheckDB to fail) and it's when there's a lot of concurrent activity on the database during the CheckDB process.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jeff Moden (7/9/2014)
hoping that you'd explain how your previous company managed to make it so on a 1TB database.

Maybe I'm missing something, but the way I did it (SQL 2005) when I worked at the bank was DBCC CheckDB ('Database name') with no_infomsgs, all_errormsgs

DB hit 1.2 TB at its largest, no problems with CheckDB creating its snapshots.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

GilaMonster (7/10/2014)
Jeff Moden (7/9/2014)
hoping that you'd explain how your previous company managed to make it so on a 1TB database.
Maybe I'm missing something, but the way I did it (SQL 2005) when I worked at the bank was DBCC CheckDB ('Database name') with no_infomsgs, all_errormsgs
DB hit 1.2 TB at its largest, no problems with CheckDB creating its snapshots.

I would say you're not missing a thing. I think the interviewer was missing something.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan-572483 SSCoach Points: 15870 More actions · Answer 9

Gail,

Here's what happened during the interview:

He asked me about maintained routines I had implemented in my most recent job, particularly on a mission critical 1TB database which was hosted on a SQL2008 R2 instance which is used 24/7 (but does have a low usage period overnight).

Among the jobs I described was nightly DBCC CHECKDB. He was surprised that I ran this on a production DB rather than restoring it to another instance and running CHECKDB there.

I told him that since CHECKDB runs against a hidden snapshot it did not create performance issues for users. He replied "what snapshot?" He also felt CHECKDB on a 1TB database would take too long to be run overnight unless you specified PHYSICAL_ONLY which he felt would useless.

I told him this server had 130 GB of RAM and the database files were on high-speed SAN disks, and that the check took somewhere between 3 and 5 hours. He said that a full CHECKDB on a 1TB database would still take much longer and that he'd never heard of someone doing this nightly on a production database of that size.

What I didn't think of saying at the time was that this database is split into several files on separate logical disks that were RAID-10 arrays composed of tier-1 physical disks on the SAN, which should also allow the process to run faster. Again I've confirmed that in recent nights this has completed successfully in 4.5 to 5 hours.

I suspect he was thinking of limitations he learned working with SQL 2000 or slower hardware and was not aware of improvements that could be taken advantage of since SQL 2005. Do you agree?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

In response to the original question, yes I have had several clients run checkdb nightly on their TB+ databases. 3.5-4hrs was normal.

There is a tweak that can be made to help it run faster by using resource governor to limit memory which helps checkdb run faster.

http://www.sqlskills.com/blogs/jonathan/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect/

Download a preso by Argenis Fernandez here.

http://performance.sqlpass.org/Home.aspx?EventID=1047

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 11

I would have done what you did; recognise that SQL is such a large product that even the smartest people have gaps in their knowledge and try to explain as briefly as possible that you were confident of your answer.

If they continued to push it I would suggest that perhaps they can research it further after the interview, or we can have a session on it after I've started working with them.

But beyond that, if they're willing to pull out the stops and accuse you of making things up in the interview (it's rare but not unheard of) then be thankful you've just dodged a bullet; who wants to commit the next year or more working for someone who has such poor interpersonal skills.