SQL Server - Senior DBA Interview Questions

Asked By Joe K on 16-Jan-08 03:49 PM
I have to give a Senior SQL Server DBA interview next week.  I would like to
obtain a list of Senior SQL Server DBA questions.

Thanks,




Greg D. Moore \(Strider\) replied on 16-Jan-08 05:26 PM
(this should generate a good discussion since it's not an absolute answer.)


--
Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html
Kalen Delaney replied on 16-Jan-08 05:59 PM
And then later in the interview ask:

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
Hugo Kornelis replied on 16-Jan-08 07:11 PM
And to top it off:


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Greg D. Moore \(Strider\) replied on 16-Jan-08 07:17 PM
That's just plain mean.

I like that.

Seriously, having just interviewed a number of DBA candidates, I found
either variation on the question to be useful to ask.

How they answer gives good insight into how they think about problems.


Another good one:

What's the difference between DBCC DBREINDEX and DBCC INDEXDEFRAG?





--
Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html
Greg Linwood replied on 16-Jan-08 07:31 PM
And then you might ask the Senior DBA candidate why defragging databases can
be a bad practise?

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
serge replied on 16-Jan-08 07:40 PM
Why or when would defragging databases be a bad practice?

I am curious to find out more.

Thank you
Uri Dimant replied on 17-Jan-08 01:44 AM
Joe
http://vyaskn.tripod.com/iq.htm
Greg Linwood replied on 17-Jan-08 03:41 AM
DRP or report server synchronisations solutions such as Log Shipping,
Mirroring and Replication can be brought down by the massive levels of
tlogging generated by index rebuilds. This is a very common cause of lost
sleep amongst DBAs around the world.

The crazy thing about defragging databases is that it generally helps
queries which are fully scanning tables which can be easily tuned with
indexes. So, although defragging sure can help the performance of a well
tuned database (because queries are hammering the disks) it usually doesn't
do much for well tuned databases. Unfortunately, many SQL DBAs have
developed a dependance on defragging databases rather than fixing the real
underlying problems.

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
Uri Dimant replied on 17-Jan-08 05:32 AM
In addition to Greg's great comment , I'd prefer to identify  heavely
fragmented  tables / indexes and run ALTER INDEX ... command
And I'm fully agree with Greg that we need  "fixing the real underlying
problems"
Kevin3NF replied on 17-Jan-08 08:37 AM
Joe,

You need a mix of "theoretical" and specific "technical" questions.
Open-ended vs. specific answers.

Theoretical:
and techniques"
a Senior DBA should be able to discuss these at length.
non-technical language (i.e. to a BA or Jr. Developer)

Technical:
environment" (look for With move, replace, recovery/norecovery, etc.)
Specific features of the versions you care about.

If a senior DBA can't backup or restore in T-SQL from his head...it becomes
a short interview :)

Certainly not a comprehensive list, and you need some basic ones on the
front side to immediately weed a candidate out.  The backup/restore
questions have caused me to prematurely end more than a few interviews I
done with guys that wanted to sub-contract with me.

Hope that helps

--

Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
MatthewBand replied on 17-Jan-08 09:02 AM
Here are a couple:

(1) Given a table that you do NOT want to give a user access to, but you
have a stored proc that modifies this table and you DO want to give the user
access to modify the table, but only through the stored proc.  How would you
accomplish this?

The answer will vary based on version of SQL Server you're dealing with, but
2005 introduces "execute as" clauses.

(2) Ask for a description of Database Snapshots, what they are, why you
would use them, and what do you need to be careful of when using them.

Info is readily available on snapshots in the SQL 2005 BOL.

I realize that these questions are very SQL 2005 biased, but SQL 2008 is
just around the corner already.

I hope that this helps.
TheSQLGuru replied on 17-Jan-08 06:36 PM
Just me but I don't think it is a good idea to have someone who isn't
already a very knowledgeable DBA interview someone for a Sr DBA position.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
Kalen Delaney replied on 17-Jan-08 06:56 PM
So does this mean you are wondering why the OP asked this question at all?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
Greg D. Moore \(Strider\) replied on 17-Jan-08 08:38 PM
I tend to agree, but for smaller companies, it can be tough to bring in such
talent when you don't already have it.



--
Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html
Greg Linwood replied on 17-Jan-08 08:50 PM
Maybe the poster of this thread is experienced enough to know that research
amongst colleagues helps (c:

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
serge replied on 18-Jan-08 12:48 AM
Thanks Greg/Uri for the answers.


Soon I'll be dealing with what you just describe above. We have a 170GB
db (we're working on reducing the size) that does replication to another
SQL 2000 server for reporting purposes. We might start using Log
Shipping and/or Database Mirroring also.

What could really be the problem here when rebuilding indexes? Like
what could go wrong? The replication, log shipping, database mirroring
would fail because a rebuilding index is occuring? Or they would simply
be out of synch because rebuilding a big table could take dozens of
minutes and to synchronize all these changes could be GBs of transaction
log files being generated? If that's what you mean as problems then I can
see your point. I've experienced problems with Log Shipping with smaller
sized databases where the time to copy/restore something would not be
enough when the transaction log file was bigger than usual.

Would this be the problem you mean?

I would still need to at least defrag the database once as it's pretty
fragmented
right now; so I am not sure if I should stop the replication completely,
once done with fully rebuilding all the indexes, set up a new replication
altogether
with such a big sized db? It's being replicated on the same subnet so LAN
connection is the bandwidth so there's no worry for WAN bandwidth usage.

Speaking of fragmentation, the mdf file Windows Analyze from Disk
Defragmenter
indicates as the file being fragmented to close to 50,000 pieces! The .mdf
is
stored on a Hitachi SAN. Some people say SAN don't have fragmentation while
others say that's non-sense. I suspect it is non-sense and the .mdf is
really
terribly fragmented. Is there any system monitor counter I can use to
demonstrate
without a doubt that physical file fragmentation is causing a lot of I/O
stress and
slowing things down? Is there any DMV that could verify physical file
fragmentation?

BTW, how does the reindexing work internally?

If I have a 100GB mdf file and a table that is 10GB and has a clustered
index.
If I rebuild the indexes on this table, the transaction log file will first
grow to occupy
the new sorted table, then SQL Server would search for a contiguous space in
the mdf
file that can contain this new table that is in the log file? If it doesn't
find, it would
increase the .mdf file at the end, enough space to fit the new table at the
end, copy
the data from the log file to mdf and then delete the old table in the mdf
file?
Is this how it would work internally?

Thanks again.
Jack Vamvas replied on 18-Jan-08 06:09 AM
I think questions focused around: approach to problems , for example
trouleshooting a sudden drop in performance , are very effective.
In reality , knowledge of every command is not necessary to be a good DBA.
Although you definately need to know what is available
and how to go about tackling standard SQL Server tasks

--

Jack Vamvas
___________________________________
Search  IT jobs from multiple sources-   http://www.ITjobfeed.com
Rita Roy replied on 03-Aug-08 01:54 PM
I noticed a very huge set of DBA interview questions here at :-

http://www.kanbal.com/index.php?/Database/dba-interview-questions-set-2.html



I cleared my interview based on these sets. there seem to a around 7 to 8 sents of such questions and answers. Happy learning.
Srinivas Rao Boddu replied to Joe K on 22-Dec-09 01:14 AM
Hi
Uri Dimant replied to Srinivas Rao Boddu on 22-Dec-09 01:55 AM
http://vyaskn.tripod.com/iq.htm
jerry vn replied to Jack Vamvas on 22-Jul-10 05:44 AM
Hi,



I do not agreed with you. Any way, your ideal make me thinking about some thing for my project.



Please try to keep posting. Tks and best regards
jerry vn replied to jerry vn on 02-Aug-10 09:49 PM
Apart from that, this link below may be useful:  http://www.humanresources.hrvinet.com/shipping-interview-questions