Junior SQL Server DBA Interview Questions by uzd19483

VIEWS: 101 PAGES: 6

									Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

Written By:  Edgewood Solutions Engineers  -- 11/13/2008

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 are 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 is
meant by that is you want to know what knowledge someone has on day one and what you will
need to teach them overtime.  As such, here are a baseline set of questions for a junior SQL
Server DBA:




Backup and Recovery


   - Question 1 - What are 2 options to validate whether or not a backup will restore
successfully?
   - Restore the backup as a portion of a testing process or log shipping.
   - Restore the backup with the Verify Only option.
   - Additional information -  Verifying Backups with the RESTORE VERIFYONLY Statement




  - Question 2 - How can you issue a full backup and not interrupt the LSN's?
  - Issue a copy only backup.
  - Additional information -  COPY_ONLY Backups with SQL Server 2005




                                                                                           1/6
Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

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.
   - Additional information -  SQL Server Command Line Tools To Manage Your Server


   - 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.
   - Additional information -  SQL Server Performance Statistics Using a Server Side Trace


   - Perfmon - Perfmon is responsible for macro level metrics related to processes and sub
systems.
   - Additional information -  Free Microsoft Tools to Help Setup and Maintain PerfMon


  - Dynamic Management Views and Functions - New to SQL Server 2005, the Dynamic
Management Views and Functions offer a real time view into the SQL Server sub systems.
  - Additional information -  Dynamic Management Views and Functions in SQL Server 2005


   - 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.
   - Additional information -  How To Collect Performance Data With TYPEPERF.EXE


   - SQL Server Management Studio Built-in Performance Reports - As part of the installation
of SQL Server 2005 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 2005
instance. If you don't have an instance of Reporting Services installed then the icon will be
disabled.
   - Additional information -  Built-In Performance Reports in SQL Server 2005




   - Question 2 - How do you go about tuning a SQL Server query?



                                                                                            2/6
Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

   - 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.
   - Additional information -  Query Plans in SQL Server 2000 vs SQL Server 2005




Maintenance


   -   Question 1 - What are the three options in SQL Server 2005 to rebuild indexes?
   -   CREATE INDEX with DROP_EXISTING
   -   DROP INDEX and CREATE INDEX
   -   ALTER INDEX
   -   Additional information -  Index Rebuilds in SQL Server 2000 vs SQL Server 2005




   - 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. Spotlight will also show you this same information.
   - 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



                                                                                                       3/6
Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

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.
   - Additional information -  SQL Server Command Line Tools To Manage Your Server




Database Design


   - Question 1 - What happens when you add a column in the middle of a table (dbo.Test1) in
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.
   - Additional information -  Modifying SQL Server database tables and keeping like columns
together




   - Question 2 - What are included columns with respect to SQL Server indexing?
   - A new type of index was developed in SQL Server 2005 that assists in covering queries:
Indexes With Included Columns. 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.



                                                                                              4/6
Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

  - Additional information -  Improve Performance with SQL Server 2005 Covering Index
Enhancements




SQL Server Business Intelligence


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


  - Question 2 - How do you backup Analysis Services databases?
  - Create the XML statement to backup the Analysis Services databases, then create a SQL
Server Agent Job to perform the task on a daily basis.
  - Additional information -  Automating Backups for SQL Server 2005 Analysis Services
Databases




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



                                                                                             5/6
Junior SQL Server DBA Interview Questions

Written by Administrator
Thursday, 11 December 2008 14:11 -

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.




                                                                                           6/6

								
To top