Senior DBA Interview Questions

On Mon, 4 Aug 2008 12:26:05 -0700, kannan
wrote:

>Generally a DBA can be a DBA any database. Almost all the RDMS have
>concepts. Even I went to that link, the questions are good. And the most
>important thing is if you are DBA you will not say this statement.

While there are many aspects of DBA work that are universal, the devil
is in the details. The concepts of backup and restore are universal,
but you had better be ready to handle the details when you have to
recover a server. Not that you have to memorize every word of the
syntax, but you had better not be surprised by what exactly is and is
not possible with the RDBMS at hand.

Roy
 >> Stay informed about: Senior DBA Interview Questions 

Hi



Joe K wrote:

Senior DBA Interview Questions
16-Jan-08

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,

Previous Posts In This Thread:

On Wednesday, January 16, 2008 3:49 PM
Joe K wrote:

Senior DBA Interview Questions
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,

On Wednesday, January 16, 2008 5:26 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
x"Joe K." wrote in message


"Why should you not put a clustered index on your Identity column?"

(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

On Wednesday, January 16, 2008 5:59 PM
Kalen Delaney wrote:

Re: Senior DBA Interview Questions
And then later in the interview ask:

"Why should you put a clustered index on your Identity column?"
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

On Wednesday, January 16, 2008 7:11 PM
Hugo Kornelis wrote:

Re: Senior DBA Interview Questions
And to top it off:

"Why should you not use an Identity column?"

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Wednesday, January 16, 2008 7:17 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
"Kalen Delaney" wrote in message


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

On Wednesday, January 16, 2008 7:31 PM
Greg Linwood wrote:

And then you might ask the Senior DBA candidate why defragging databases can
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

"Greg D. Moore (Strider)" wrote in message


On Wednesday, January 16, 2008 7:40 PM
serge wrote:

Why or when would defragging databases be a bad practice?
Why or when would defragging databases be a bad practice?

I am curious to find out more.

Thank you

On Thursday, January 17, 2008 1:44 AM
Uri Dimant wrote:

Re: Senior DBA Interview Questions
Joe
http://vyaskn.tripod.com/iq.htm

On Thursday, January 17, 2008 3:41 AM
Greg Linwood wrote:

DRP or report server synchronisations solutions such as Log Shipping,
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

"serge" wrote in message


On Thursday, January 17, 2008 5:32 AM
Uri Dimant wrote:

In addition to Greg's great comment , I'd prefer to identify heavely
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"




"Greg Linwood" wrote in message


On Thursday, January 17, 2008 8:37 AM
Kevin3NF wrote:

Joe,You need a mix of "theoretical" and specific "technical" 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


On Thursday, January 17, 2008 9:02 AM
MatthewBand wrote:

RE: Senior DBA Interview Questions
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:

On Thursday, January 17, 2008 6:36 PM
TheSQLGuru wrote:

Just me but I don't think it is a good idea to have someone who isn't already
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


On Thursday, January 17, 2008 6:56 PM
Kalen Delaney wrote:

So does this mean you're wondering why the OP asked this question at all?
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

On Thursday, January 17, 2008 8:38 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
"TheSQLGuru" wrote in message


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

On Thursday, January 17, 2008 8:50 PM
Greg Linwood wrote:

Maybe the poster of this thread is experienced enough to know that research
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


On Friday, January 18, 2008 12:48 AM
serge wrote:

Thanks Greg/Uri for the answers.
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.

On Friday, January 18, 2008 6:09 AM
Jack Vamvas wrote:

Re: Senior DBA Interview Questions
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


On Sunday, August 03, 2008 1:54 PM
Rita Roy wrote:

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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build A Managed C# Playing Card UserControl
http://www.eggheadcafe.com/tutorials/aspnet/3bdc8513-f37f-4fb1-90d1-f4...997a18d
 >> Stay informed about: Senior DBA Interview Questions 

http://vyaskn.tripod.com/iq.htm


<Srinivas Rao Boddu> wrote in message
...
> Hi
>
>
>
> Joe K wrote:
>
> Senior DBA Interview Questions
> 16-Jan-08
>
> 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,
>
> Previous Posts In This Thread:
>
> On Wednesday, January 16, 2008 3:49 PM
> Joe K wrote:
>
> Senior DBA Interview Questions
> 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,
>
> On Wednesday, January 16, 2008 5:26 PM
> Greg D. Moore \(Strider\) wrote:
>
> Re: Senior DBA Interview Questions
> x"Joe K." wrote in message
>
>
> "Why should you not put a clustered index on your Identity column?"
>
> (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
>
> On Wednesday, January 16, 2008 5:59 PM
> Kalen Delaney wrote:
>
> Re: Senior DBA Interview Questions
> And then later in the interview ask:
>
> "Why should you put a clustered index on your Identity column?"
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> On Wednesday, January 16, 2008 7:11 PM
> Hugo Kornelis wrote:
>
> Re: Senior DBA Interview Questions
> And to top it off:
>
> "Why should you not use an Identity column?"
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
> On Wednesday, January 16, 2008 7:17 PM
> Greg D. Moore \(Strider\) wrote:
>
> Re: Senior DBA Interview Questions
> "Kalen Delaney" wrote in message
>
>
> 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
>
> On Wednesday, January 16, 2008 7:31 PM
> Greg Linwood wrote:
>
> And then you might ask the Senior DBA candidate why defragging databases
> can
> 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
>
> "Greg D. Moore (Strider)" wrote in
> message
>
>
> On Wednesday, January 16, 2008 7:40 PM
> serge wrote:
>
> Why or when would defragging databases be a bad practice?
> Why or when would defragging databases be a bad practice?
>
> I am curious to find out more.
>
> Thank you
>
> On Thursday, January 17, 2008 1:44 AM
> Uri Dimant wrote:
>
> Re: Senior DBA Interview Questions
> Joe
> http://vyaskn.tripod.com/iq.htm
>
> On Thursday, January 17, 2008 3:41 AM
> Greg Linwood wrote:
>
> DRP or report server synchronisations solutions such as Log Shipping,
> 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
>
> "serge" wrote in message
>
>
> On Thursday, January 17, 2008 5:32 AM
> Uri Dimant wrote:
>
> In addition to Greg's great comment , I'd prefer to identify heavely
> 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"
>
>
>
>
> "Greg Linwood" wrote in message
>
>
> On Thursday, January 17, 2008 8:37 AM
> Kevin3NF wrote:
>
> Joe,You need a mix of "theoretical" and specific "technical" 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
>
>
> On Thursday, January 17, 2008 9:02 AM
> MatthewBand wrote:
>
> RE: Senior DBA Interview Questions
> 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:
>
> On Thursday, January 17, 2008 6:36 PM
> TheSQLGuru wrote:
>
> Just me but I don't think it is a good idea to have someone who isn't
> already
> 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
>
>
> On Thursday, January 17, 2008 6:56 PM
> Kalen Delaney wrote:
>
> So does this mean you're wondering why the OP asked this question at all?
> 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
>
> On Thursday, January 17, 2008 8:38 PM
> Greg D. Moore \(Strider\) wrote:
>
> Re: Senior DBA Interview Questions
> "TheSQLGuru" wrote in message
>
>
> 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
>
> On Thursday, January 17, 2008 8:50 PM
> Greg Linwood wrote:
>
> Maybe the poster of this thread is experienced enough to know that
> research
> 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
>
>
> On Friday, January 18, 2008 12:48 AM
> serge wrote:
>
> Thanks Greg/Uri for the answers.
> 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.
>
> On Friday, January 18, 2008 6:09 AM
> Jack Vamvas wrote:
>
> Re: Senior DBA Interview Questions
> 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
>
>
> On Sunday, August 03, 2008 1:54 PM
> Rita Roy wrote:
>
> DBA interview
> 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.
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Build A Managed C# Playing Card UserControl
> http://www.eggheadcafe.com/tutorials/aspnet/3bdc8513-f37f-4fb1-90d1-f4...997a18d
 >> Stay informed about: Senior DBA Interview Questions 

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




Jack Vamvas wrote:

Re: Senior DBA Interview Questions
18-Jan-08

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


Previous Posts In This Thread:

On Wednesday, January 16, 2008 3:49 PM
Joe K wrote:

Senior DBA Interview Questions
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,

On Wednesday, January 16, 2008 5:26 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
x"Joe K." wrote in message

"Why should you not put a clustered index on your Identity column?"

(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

On Wednesday, January 16, 2008 5:59 PM
Kalen Delaney wrote:

Re: Senior DBA Interview Questions
And then later in the interview ask:

"Why should you put a clustered index on your Identity column?"
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

On Wednesday, January 16, 2008 7:11 PM
Hugo Kornelis wrote:

Re: Senior DBA Interview Questions
And to top it off:

"Why should you not use an Identity column?"

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Wednesday, January 16, 2008 7:17 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
"Kalen Delaney" wrote in message

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

On Wednesday, January 16, 2008 7:31 PM
Greg Linwood wrote:

And then you might ask the Senior DBA candidate why defragging databases can
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

"Greg D. Moore (Strider)" wrote in message


On Wednesday, January 16, 2008 7:40 PM
serge wrote:

Why or when would defragging databases be a bad practice?
Why or when would defragging databases be a bad practice?

I am curious to find out more.

Thank you

On Thursday, January 17, 2008 1:44 AM
Uri Dimant wrote:

Re: Senior DBA Interview Questions
Joe
http://vyaskn.tripod.com/iq.htm

On Thursday, January 17, 2008 3:41 AM
Greg Linwood wrote:

DRP or report server synchronisations solutions such as Log Shipping,
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

"serge" wrote in message


On Thursday, January 17, 2008 5:32 AM
Uri Dimant wrote:

In addition to Greg's great comment , I'd prefer to identify heavely
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"




"Greg Linwood" wrote in message


On Thursday, January 17, 2008 8:37 AM
Kevin3NF wrote:

Joe,You need a mix of "theoretical" and specific "technical" 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


On Thursday, January 17, 2008 9:02 AM
MatthewBand wrote:

RE: Senior DBA Interview Questions
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:

On Thursday, January 17, 2008 6:36 PM
TheSQLGuru wrote:

Just me but I don't think it is a good idea to have someone who isn't already
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


On Thursday, January 17, 2008 6:56 PM
Kalen Delaney wrote:

So does this mean you're wondering why the OP asked this question at all?
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

On Thursday, January 17, 2008 8:38 PM
Greg D. Moore \(Strider\) wrote:

Re: Senior DBA Interview Questions
"TheSQLGuru" wrote in message

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

On Thursday, January 17, 2008 8:50 PM
Greg Linwood wrote:

Maybe the poster of this thread is experienced enough to know that research
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


On Friday, January 18, 2008 12:48 AM
serge wrote:

Thanks Greg/Uri for the answers.
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.

On Friday, January 18, 2008 6:09 AM
Jack Vamvas wrote:

Re: Senior DBA Interview Questions
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


On Sunday, August 03, 2008 1:54 PM
Rita Roy wrote:

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

On Tuesday, December 22, 2009 1:14 AM
Srinivas Rao Boddu wrote:

need interview questions
Hi

On Tuesday, December 22, 2009 1:55 AM
Uri Dimant wrote:

http://vyaskn.tripod.com/iq.htm<Srinivas Rao Boddu> wrote in message
http://vyaskn.tripod.com/iq.htm


<Srinivas Rao Boddu> wrote in message


Submitted via EggHeadCafe - Software Developer Portal of Choice
Win a Free License of SandRibbon for Silverlight
http://www.eggheadcafe.com/tutorials/aspnet/cb3912a1-be37-4032-a71a-59...e18c0d7
 >> Stay informed about: Senior DBA Interview Questions 

Apart from that, this link below may be useful: http://www.humanresources.hrvinet.com/shipping-interview-questions



jerry vn wrote:

Shipping interview questions
22-Jul-10

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

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Overriding Deserialization of Persisted Workflow Instances
http://www.eggheadcafe.com/tutorials/aspnet/40fe2b27-ef79-45d0-82e1-6e...ed67df8
 >> Stay informed about: Senior DBA Interview Questions