Junior SQL Server DBA Interview Questions

By:   |   Updated: 2012-01-27   |   Comments (11)   |   Related: More > Professional Development Interview Questions DBA


Problem

My organization is in the process of hiring a junior SQL Server DBA to add to our team.  What are some fair questions to ask as a portion of the interview process?  I want to make sure we hire someone who has a good base of knowledge.  I am concerned our Senior SQL Server DBAs are looking to stump the candidates which is not really my goal.  Do you have any suggestions?

Solution

Trying to assess any technical person's skill set can be difficult.  An interview process with appropriate questions for your environment and the correct skill set are key.  In some respects understanding what a candidate knows and does not know could be equally beneficial.  What I mean by that is you want to know what knowledge someone has on day one and what you will need to teach them over time.  As such, here are a baseline set of questions for a junior SQL Server DBA.

SQL Server Backup and Recovery

  • Question 1 - What are 2 options to validate whether or not a backup will restore successfully?

 

SQL Server Performance Tuning

  • Question 1 - Name as many native SQL Server performance monitoring and tuning tools that you know of and their associated value.
    • System objects - System objects such as sp_who2, sp_lock, fn_get_sql, etc. provide a simple means to capture basic metrics related to locking, blocking, executing code, etc.
    • Profiler - In a nutshell, Profiler provides the lowest common denominator of activity on a SQL Server instance.  Profiler captures per session code with the ability to filter the data collection based on database, login, host name, application name, etc. in order to assess the IO, CPU usage, time needed, etc.
    • Perfmon\System Monitor - Perfmon\System Monitor is responsible for macro level metrics related to processes and sub systems.
    • Dynamic Management Views and Functions - New to SQL Server 2005 and beyond, the Dynamic Management Views and Functions offer a real time view into the SQL Server sub systems.
    • TYPEPERF.EXE - TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. It is necessary to capture performance data whenever you are trying to diagnose performance issues on a server. Performance data provides information on the server's utilization of the processor, memory, and disk, as well as SQL Server-specific performance data.
    • SQL Server Management Studio Built-in Performance Reports - As part of the installation of SQL Server 2005 and beyond a number of performance-related reports are installed. To get to these reports open the SQL Server Management Studio (SSMS) and connect to a SQL Server instance. If you don't have an instance of Reporting Services installed then the icon will be disabled.
    • Additional resources for this question:

 

  • Question 2 - How do you go about tuning a SQL Server query?
    • Identify the query causing the issue.
    • Review the query plan by issuing SHOWPLAN_TEXT, SHOWPLAN_ALL, Graphical Query Plan or sys.dm_exec_query_stats.
    • Review the individual query components to determine which components of the query have the highest cost.
    • Outline options to improve the query such as moving from cursor based logic to set based logic or vice versa, changing the JOIN order, WHERE clause or ORDER BY clause, adding indexes, removing indexes, creating covering indexes, etc.
    • Test the options to determine the associated performance improvement.
    • Implement the solution.

SQL Server Maintenance

 

  • Question 2 - Name 3 or more DBCC commands and their associated purpose.
    • DBCC CACHESTATS - Displays information about the objects currently in the buffer cache.
    • DBCC CHECKDB - This will check the allocation of all pages in the database as well as check for any integrity issues.
    • DBCC CHECKTABLE - This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
    • DBCC DBREINDEX - This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
    • DBCC PROCCACHE - This command will show you information about the procedure cache and how much is being used. 
    • DBCC MEMORYSTATUS - Displays how the SQL Server buffer cache is divided up, including buffer activity.
    • DBCC SHOWCONTIG - This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
    • DBCC SHOW_STATISTICS - This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
    • DBCC SHRINKFILE - This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
    • DBCC SQLPERF - This command will show you much of the transaction logs are being used.
    • DBCC TRACEON - This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
    • DBCC TRACEOFF - This command turns off a trace flag.

SQL Server Database Design

  • Question 1 - What happens when you add a column in the middle of a table (dbo.Test1) in SQL Server Management Studio?
    • Management Studio creates a temporary table called dbo.Tmp_Test1 with the new structure.
    • If there is data in the original table dbo.Test1 this data is inserted into the new temp table dbo.Tmp_Test1 (now you have two sets of the same data).
    • The original table dbo.Test1 is dropped.
    • The new table dbo.Tmp_Test1 is renamed to dbo.Test1.
    • If the table has indexes all of the indexes are recreated.

 

  • Question 2 - What are included columns with respect to SQL Server indexing?
    • A new type of index was developed in SQL Server 2005 and beyond that assists in situations where a covering index is needed. 
    • Indexes with Included Columns are nonclustered indexes that have the following benefits:
      • Columns defined in the include statement, called non-key columns, are not counted in the number of columns by the database engine.
      • Columns that previously could not be used in queries, like nvarchar(max), can be included as a non-key column.
      • A maximum of 1023 additional columns can be used as non-key columns.
    • Additional information - Improve Performance with SQL Server 2005 Covering Index Enhancements

SQL Server Business Intelligence

  • Question 1 - Name some new features from Data Transformation Services to SQL Server Integration Services.
    • SSIS checkpoints.
    • SSIS logging.
    • SSIS package configurations.
    • SSIS Breakpoint.
    • Dynamic flat file connections.
    • SSIS batch processing.
    • MERGE JOIN.
    • Additional information - SQL Server Integration Services

 

Next Steps
  • To the interviewers:
    • As you prepare for a junior level SQL Server interview, be sure to have appropriate questions in place to challenge the candidate and understand their skill level on any given topic.
    • Be sure to ask questions to understand the depth and breadth of the candidates knowledge base.
  • To the interviewees:
    • Be prepared for your technical interviews.  The questions in this tip are intended for a newbie SQL Server DBA, but more often than not, you will get some very challenging questions and scenarios from interviewers.  Many of these situations and questions will probably not be familiar to you, so think quickly and be sure to prepare a response to questions you simple do not know the answer to.
  • Check out these related tips:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-01-27

Comments For This Article




Saturday, June 25, 2016 - 1:39:40 AM - Ben Back To Top (41765)

"The questions in this tip are intended for a newbie SQL Server DBA"

 

 

If you're going for a junior position and get asked these questions, then walk up and leave. These are horrible for a junior position, this will intimidate the candidate and you will never hear from him again and will probably think he's not cut out for this industry even if he gets 60% correct. Granted he needs to have good base knowledge but really? This is very patronising and these are mid-level at the least!

I would never ask (and have never asked) these awful questions to potential junior candidates.

I usually know it's a good sign when they brings in detailed reports of projects and self directed study then get him to talk through them step by step. 

I've had graduates with good grades not be able to answer these.

Please guy and girls don't get intimidated by these questions please. Do have foundation knowledge but these are ridiculous.

 

Friday, August 14, 2015 - 11:28:17 PM - Manish Rao Back To Top (38451)

Hi ,

 

I Just Insatalled SQL Server and in Few Moments it failed!!! Now where the Information are available for failed still not MS SQL Folder has been created.

Please guide for the same

 

 

 

Friday, April 17, 2015 - 10:51:46 AM - Jeff Moden Back To Top (36964)

Heh... considering how few Senior DBAs know how to tune a query, I'd have to say that the number of Junior DBAs that know how to tune a query would be quite low, indeed. ;-)

Wednesday, December 4, 2013 - 5:28:27 PM - Too Wide a Range Back To Top (27692)

Pretend you're asking for help on a forum - give them a small sample schema, some small sample data, and either ask them to give you a query to do X, or tune a small query you give them.  If you can, provide them a laptop with a small Express database to do their tuning with - watch how they actually do it when they can't simply regurgitate memorized lists.  Let them look up syntax for commands - just watch how long it takes.

Compare your candidates against each other - see how you feel.

 

Go ahead and ask all your questions too, but they cover a huge range - SSIS is a separate, though related, skillset.  Developing SQL vs. maintaining systems, etc.  Like Jeff said, the LSN questions are pretty advanced, and I find the "interrupt the LSN's" phrasing very odd.

 

Some of your answers seem odd to me - i.e., for tuning, if you know the data, you don't need to look at a query plan first to see "You're getting too much data" or "your join is wrong" or "you shouldn't need a DISTINCT here".  First do basic tuning (and gather evidence that it did improve things), then look at a plan.  I didn't see anything on breaking up queries, derived tables, etc. - there's lots of approaches to tuning, and many of them are very valid.

Tuesday, December 3, 2013 - 1:57:03 PM - Mike Back To Top (27677)

I would have to agree with Jeff on this one, I'm not looking for somebody that has memorized all the different exact syntaxes for things, but rather somebody that has the ability to troubleshoot issues. You can Google anything you need in seconds, but the person has to be able to know what to look for in the first place. 

I wouldn't expect a junior DBA to know most of the things listed here off the top of their head, but if they were aware of them, then that is just as important. The ability to learn and grow is more important than a few things memorized from a bootcamp or brain dump site.

Thursday, July 25, 2013 - 12:50:57 PM - Jeff Moden Back To Top (26009)

Junior DBA questions, huh?  You must have some truly great Senior DBAs or the DBAs that I've interviewed in the past (most claiming more than 7 years of experience) were worse than I thought.  I've found very few, for example, that actually know what an LSN is and none that knew how it pertainned to Point-in-time restores.  I've also found very few DBAs with enough hybrid skills to actually be able to run parts of a stored procedure that updates data  without screwing the data up never mind troubleshooting it for performance.

Consider the level you're trying to hire for.  Why should a Junior DBA know anything about how to backup SSAS, SSIS, or SSRS?  Be happy if they happen to understand the database engine backups and can actually do a simple restore.

By the same token, I've worked with some great SYSTEM DBAs that actually knew very little about indexing.  Certainly, they didn't know what INCLUDE did.  But, they didn't actually need to... their job was to keep a hundred servers up and running and it was up do the Senior Developers to figure out the indexing.

 

What I'm trying to get at here is that I see (on other threads/forums) people saying things like what John said in this thread and want to warn that no set of questions are necessarily the right set of questions for a particular job or interview. Step 1 in hiring someone is to sit down and figure out what the hell it is that you actually want the person in that position to do and to what level you want them to do it.  Then sit down a make a set of reasonable questions that will allow the candidate to demonstrate the knowledge the job needs.  There should be some quesstions that allow the candidate to expound if they know more.

Never ask trick questions especially for a Junior positon.  You're trying to find out if the candidate can do the job... not how much you think you know when it comes to oolies.

 

The sad part is that many interviewers don't even look at the job description (if there is one) and are grossly unprepared to interview someone in the right manner.  Remember that the goal of an interview is to find someone that can actually do the job at the advertised level and that they have a decent attitude.

Monday, May 20, 2013 - 2:33:25 PM - Jeremy Kadlec Back To Top (24048)

John,

I would check out the Professional Development tips here on MSSQLTips.com - http://www.mssqltips.com/sql_server_professional_development_tips.asp.

I would also brush up on the skills as outlined by the job description.  Based on those topics, I would check out the following:

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Sunday, May 19, 2013 - 11:36:20 AM - John Back To Top (24035)

Hey Guys,

I will have interview for Junior SQL Server position next tuesday. So I need all the advice I can get and this is my first SQL Server interview

Wednesday, August 17, 2011 - 2:57:42 PM - Jason Yousef Back To Top (14439)

Great... Thanks for that..

Monday, January 17, 2011 - 11:32:27 AM - Jeremy Kadlec Back To Top (12619)

DavidB,

Thank you so much for the feedback.

Thank you,
Jeremy Kadlec

Thursday, November 13, 2008 - 9:33:04 AM - DavidB Back To Top (2205)

Nice variety of questions that forces the interviewee to use all their knowledge of SQL Server.














get free sql tips
agree to terms