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?
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?
Restore the backup as a portion of a testing process or log shipping.
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.
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.
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.
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.
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.