[UPDATE: See the updated list of 20 questions here.]
There have been several blog posts about SQL Server Database Administrator interview questions. I have listed some examples here for your review, but the best thing you can do to prepare for an interview as someone looking for a job is to know SQL Server databases. If you are performing the interview as someone in the hiring process, think about the specific skills you are looking for and target your questions to help identify the applicants strengths and weaknesses.
1. What is a four-part name?
If the candidate ever crosses database boundaries with his queries, they should know this answer. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Production instance, the four part name would look like this:
2. How do you trace the traffic hitting a SQL Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
3. What is a Linked Server?
Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
4. Why would you use SQL Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
5. What happens on checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database. One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
6. What are the dynamic management views and what value do they offer?
The DMV’s are a set of system views new to SQL Server 2005 and beyond to gain insights into particular portions of the engine
- Here are some of the DMV’s and the associated value:
- sys.dm_exec_query_stats and sys.dm_exec_sql_text – Buffered code in SQL Server
- Additional Information: Identifying the input buffer in SQL Server 2000 vs SQL Server 2005
- Additional Information: Buffer Pool Space in SQL Server 2005
- sys.dm_tran_locks – Locking and blocking
- Additional Information: Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
- sys.dm_os_wait_stats – Wait stats
- Additional Information: Waitstats performance metrics in SQL Server 2000 vs SQL Server 2005
- sys.dm_exec_requests and sys.dm_exec_sessions – Percentage complete for a process
- Additional Information: Finding a SQL Server process percentage complete with dynamic management views
- sys.dm_exec_query_stats and sys.dm_exec_sql_text – Buffered code in SQL Server
7. What are the primary differences between an index reorganization and an index rebuild?
- A reorganization is an “online” operation by default; a rebuild is an “offline” operation by default
- A reorganization only affects the leaf level of an index
- A reorganization swaps data pages in-place by using only the pages already allocated to the index; a rebuild uses new pages/allocations
- A reorganization is always a fully-logged operation; a rebuild can be a minimally-logged operation
- A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transactional and must be completed in entirety to keep changes
8.How can you control the amount of free space in your index pages?
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.
9. 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.
10. What authentication modes does SQL Server support?
SQL Server supports Windows Authentication, SQL Server Authentication, and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server.
11. Define candidate key, alternate key, composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.
12. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which usually makes it makes it slow, really slow if there are a large number of rows in the target database. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.
13. How do you determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit my SQL Server service packs and versions list.
14. What is ACID?
ACID stands for Atomicity, Consistency, Isolation, Durability. You can read more details here.
15. When And Where Does The Local User Group Meet?
This is a suggestion from Brent Ozar, and I really like the intent of the question. It isn’t important if the candidate goes to every meeting, but they should know that they exist and when they usually meet. It is even better if they attend on some fairly regular schedule. They won’t know everything and this gives you an idea if they connect to the local community of professionals so if they do run into an issue they know there is somewhere they can go for help besides Google. This will work even for remote or relocating candidates, as they should answer this question about their group in their current city.
BONUS – Why is a manhole cover round?
Sometimes we get so focused to technical questions, we forget that there are questions that aren’t just about SQL Server. Simple questions that aren’t about databases or technology can reveal a lot of information about how a person thinks and reacts to questions they couldn’t have practiced or studied for in advance. There are several questions you could ask, like the example above, or ask them how many gas stations are open 24 hours a day in the state of Idaho, or how much the Pacific ocean weighs in kilograms. It doesn’t matter so much what the correct answer should be, but how they react to the question, how they describe the process of calculating their response, and the logic they use to justify or defend their answer.
By the way, there are several reasons a manhole cover is round in shape, versus square or triangle:
- The heavy metal cover is round so the maintenance worker can easily roll the cover on the smooth edge instead of picking it up when it needs to be moved from place to place.
- The cover doesn’t have to be aligned in any specific angle to be placed back onto the exposed manhole. Other shapes would require precise alignment.
- A round shape is easily sized so the cover can’t be dropped though the manhole and possibly injure workers under the street. Safety is important.
I once had a job candidate think about the answer for several seconds and respond that the cover is round because the manhole is round, and the manhole is round because the men who use the manhole are also round. While that is a funny answer, it doesn’t indicate they spent much time trying to find the logic in the shape of the original design.