Senior DBA Interview Questions

Joe,

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

Theoretical:
"Describe your preferred backup scenario"
"Describe your approach to troubleshooting performance, including all tools
and techniques"
a Senior DBA should be able to discuss these at length.
"Explain the difference between clustered and non-clustered indexes in
non-technical language (i.e. to a BA or Jr. Developer)

Technical:
"Give me the syntax for backing up/restoring a database from Prod to our Dev
environment" (look for With move, replace, recovery/norecovery, etc.)
"How do you check for index fragmentation"
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.



"Joe K." wrote in message
news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>
> 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,

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.



"Joe K." wrote:

>
> 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,

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


"Joe K." wrote in message
news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>
> 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,

So does this mean you're wondering why the OP asked this question at all?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"TheSQLGuru" wrote in message
news:13ovpkfc6gofkf5-at-corp.supernews.com...
> 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
>
>
> "Joe K." wrote in message
> news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>>
>> 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,
>
>

"TheSQLGuru" wrote in message
news:13ovpkfc6gofkf5-at-corp.supernews.com...
> 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.

I tend to agree, but for smaller companies, it can be tough to bring in such
talent when you don't already have it.


>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Joe K." wrote in message
> news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>>
>> 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 Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

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

"TheSQLGuru" wrote in message
news:13ovpkfc6gofkf5-at-corp.supernews.com...
> 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
>
>
> "Joe K." wrote in message
> news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>>
>> 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,
>
>

Thanks Greg/Uri for the answers.

> 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.

Soon I'll be dealing with what you just describe above. We have a 170GB
Database (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 Database? 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.

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




"Joe K." wrote in message
news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>
> 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,

That's a whole lot of stuff for a news group post serge. Sounds like it is
time for you to hire a mentor!!!

A few quickies:

1) indexing can swamp replication and cause issues

2) ANY disk can get fragmentation and lots of file fragments are bad for
performance. I had a client that picked up 18% throughput improvement with
their sql server databases by defragging their drives.

3) DMVs don't know about the OS-level physical file fragments to my
knowledge.

4) Read Kalen's books on sql internals and query optimizing for information
about indexing stuff.

5) biggest thing to remember about index frag/defrag is that if you don't
have sufficient free space in the database then you are mostly wasting your
time because it won't get laid down sequentially anyway.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"serge" wrote in message
news:FD489BD6-532D-4187-8804-EBEA1CA67461-at-microsoft.com...
> Thanks Greg/Uri for the answers.
>
>> 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.
>
> Soon I'll be dealing with what you just describe above. We have a 170GB
> Database (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 Database? 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.
>

Yes. Someone who is not already knowledgeable about DBA stuff just cannot
IMHO acquire enough information from a few forum responses to discern the
abilities and know-how of a supposed DBA expert during an interview.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Kalen Delaney" wrote in message
news:%23KGyrSWWIHA.5716-at-TK2MSFTNGP05.phx.gbl...
> So does this mean you're wondering why the OP asked this question at all?
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
>
> "TheSQLGuru" wrote in message
> news:13ovpkfc6gofkf5-at-corp.supernews.com...
>> 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
>>
>>
>> "Joe K." wrote in message
>> news:BAE930ED-C333-4182-BD13-4EEC6FCBEAD3-at-microsoft.com...
>>>
>>> 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,
>>
>>
>
>