SQL Server DBA Interview Questions Part 1

Problem

Finding DBAs is difficult, then properly interviewing a DBA to assess their skills is even more of a challenge. This situation gets compounded when the interviewer does not fully understand the technology at a low level, but needs to find the right technical team member to join their team who does. As such, in this three tip series we will cover some common questions to ask DBAs during a technical interview related to backups, maintenance, indexing, internal storage, etc. Good luck!

Solution

Question Difficulty = Easy

• Question 1: Consider a scenario where you issue a full backup. Then issue some transaction log backups, next a differential backup, followed by more transaction log backups, then another differential and finally some transaction log backups. If the SQL Server crashes and if all the differential backups are bad, when is the latest point in time you can successfully restore the database? Can you recover the database to the current point in time without using any of the differential backups?
  • Answer: You can recover to the current point in time, as long as you have all the transaction log backups available and they are all valid. Differential backups do not affect the transaction log backup chain.
  • Additional Information: Backup Tips
• Question 2: Assume the same scenario, however instead of issuing differential backups, all three of the differential backups were full backups. Assume all the full backups are corrupt with the exception of the first full backup. Can you recover the database to the current point in time in this scenario?
  • Answer: Yes, just as it is with question 1. Full backups do not affect the transaction log backup chain. As long as you have all of the transaction log backups and they are valid, you can restore the first full backup and then all subsequent transaction log backups to bring the database current.
  • Additional Information: Recovery Tips
• Question 3: What methods are available for removing fragmentation of any kind on an index in SQL Server?
  • Answer (SQL Server 2000):
    • DBCC INDEXDEFRAG
    • DBCC DBREINDEX
    • CREATE INDEX…DROP EXISTING (cluster)
    • DROP INDEX; CREATE INDEX
  • Answer (SQL Server 2005): The same processes as SQL Server 2000, only different syntax
    • ALTER INDEX…REORGANIZE
    • ALTER INDEX…REBUILD
    • CREATE INDEX…DROP EXISTING (cluster)
    • DROP INDEX; CREATE INDEX
  • AAdditional Information: Indexing Tips and SQL Server 2000 to 2005 Crosswalk – Index Rebuilds

Question Difficulty = Moderate

• Question 1: What is the fundamental unit of storage in SQL Server data files and what is it’s size?
  • Answer: A page with a size of 8k.
• Question 2: What is the fundamental unit of storage in SQL Server log files and what is it’s size?
  • Answer: A log record, size is variable depending on the work being performed.
• Question 3: How many different types of pages exist in SQL Server?
  • Answer:
    • Data
    • Index
    • Text/Image (LOB, ROW_OVERFLOW, XML)
    • GAM (Global Allocation Map)
    • SGAM (Shared Global Allocation Map)
    • PFS (Page Free Space)
    • IAM (Index Allocation Map)
    • BCM (Bulk Change Map)
    • DCM (Differential Change Map)
  • AAdditional Information: Indexing Tips

Question Difficulty = Difficult

• Question 1: What are the primary differences between an index reorganization and an index rebuild?
  • Answer:
    • 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
  • Additional Information: Indexing Tips and SSQL Server 2000 to 2005 Crosswalk – Index Rebuilds
• Question 2: Can you explain the differences between a fully-logged and minimally-logged operations?
  • Answer: In a fully logged bulk operation, depending on the type of operation being performed, SQL Server will log either each record as it is processed (when performing a bulk data-load for example), or an image of the entire page that was changed (when performing a re-index/create index for example). In a minimally-logged operation, SQL Server will log space allocations only, and also flip bit values in the BCM pages, assuming they are not already flipped, for extents that are modified during the bulk operation.
  • This minimizes both the space required to bulk log operations during the execution of the operation, and also the time required to complete the bulk operation, since very little data is logged and updated compared to a fully-logged scenario.
  • When a database is bulk-changeable (i.e. in the bulk-logged recovery model), the BCM pages are reset when the first “BACKUP LOG” operation occurs following the given bulk operation. During this transaction log backup, the extents that are marked as modified in the BCM pages are included, in their entirety within the transaction log backup. This results in a much larger transaction log backup than would be expected for the size of the active transaction log. This is what allows you to recover a bulk-logged operation if you have the transaction log backup following the operation despite the fact that during the operation you are logging only space allocations.
  • Additional Information: Minimally Logging Bulk Load Inserts into SQL Server and Backup Tips

Next Steps

• Stay tuned for the next two tips in the series which should be released shortly. These future tips will also have the easy, moderate and difficult questions.
• Check out the first set of interview questions – Interview Questions – SQL Server Concurrency and Locking