Question 1
Difficulty: medium
How do you approach monitoring database performance and identifying the root cause of slow queries?
Sample answer
I start by separating the symptom from the cause. If users report slowness, I first check whether the issue is isolated to one query, one application path, or the whole database. Then I review query execution plans, wait events, CPU, memory, disk I/O, and lock activity to see where time is being spent. I also compare current behavior with baseline performance so I know whether the issue is new or part of a trend. From there, I look for common problems like missing indexes, poor statistics, parameter sniffing, excessive scans, or blocking sessions. I prefer to fix the underlying cause rather than apply a temporary workaround unless there is a business-critical urgency. After resolving the issue, I document what happened and what monitoring alerts or tuning changes should be added so the same problem is caught earlier next time.
Question 2
Difficulty: hard
Describe a time when you had to recover a database after an outage or corruption issue.
Sample answer
In a previous role, we had a storage-related outage that caused one production database to become unavailable during business hours. My first priority was to protect the integrity of the data and reduce the risk of making the situation worse, so I confirmed the status of the backups and isolated the affected instance. I coordinated with infrastructure to stabilize the storage layer while I validated the most recent clean backup and transaction logs. Because the business needed the system back quickly, I restored the database to a separate environment first, ran consistency checks, and verified the data against application expectations before promoting it back to production. I kept stakeholders updated throughout so they knew what was happening and what the recovery timeline looked like. The recovery was successful, and afterward I improved our backup verification process and failover documentation so we had more confidence in future incidents.
Question 3
Difficulty: hard
How do you design and test a backup and recovery strategy for a critical database?
Sample answer
I build the strategy around the business recovery objectives, not just around technical convenience. First I confirm the RPO and RTO expectations, because those define how much data loss is acceptable and how fast the system must be back online. Then I design a backup schedule that includes full, incremental or differential, and log backups where appropriate, along with offsite or immutable storage for protection against corruption and ransomware. I also make sure backup encryption and access controls are in place. Testing is just as important as creating backups, so I regularly perform restore drills in a non-production environment and verify that the database comes back clean and usable, not just technically restored. I like to test different scenarios, including point-in-time recovery and full server failure, because those are often where assumptions break down. A backup plan only matters if it has been proven under realistic recovery conditions.
Question 4
Difficulty: medium
What steps do you take to secure sensitive data in a database environment?
Sample answer
My approach starts with minimizing exposure. I use the principle of least privilege so users and service accounts only have the access they genuinely need. For sensitive fields, I look at options like encryption at rest, encryption in transit, and masking or tokenization depending on the use case and compliance requirements. I also review audit logging so we can track who accessed what and when, especially for regulated data. On the administrative side, I pay close attention to patching, credential management, and separation of duties, because a secure database is not just about data encryption; it is also about reducing operational risk. I work closely with application and security teams to ensure controls do not break workflows, since security that slows everyone down too much tends to get bypassed. My goal is always to make security practical, measurable, and sustainable rather than something people only think about during audits.
Question 5
Difficulty: medium
Tell me about a time you had to handle a production issue while multiple teams were depending on you.
Sample answer
I was once responsible for a production database during a peak usage period when an application deployment triggered a spike in locking and response times. Support, application engineers, and management were all watching the issue closely, so I had to stay calm and keep the process organized. I quickly identified that the new release was causing a long-running transaction pattern that was blocking other requests. Rather than jumping straight into a fix without coordination, I set up a short incident call, explained the symptoms in plain language, and assigned clear actions: one person handled rollback preparation, another monitored user-facing impact, and I focused on the database diagnostics. Once we confirmed the cause, we rolled back the problematic change and validated that performance returned to normal. Afterward, I worked with the application team to add better pre-production testing for locking behavior. The experience reinforced how important communication is during incidents, not just technical skill.
Question 6
Difficulty: medium
How do you approach database indexing, and when would you avoid adding an index?
Sample answer
I treat indexing as a targeted performance tool, not a default fix. Before adding anything, I look at the actual workload: which queries are slow, how often they run, whether they are read-heavy or write-heavy, and what the current execution plans show. If a query is scanning large amounts of data because it lacks a useful access path, an index may help a lot. But I also consider the trade-offs, because every additional index adds maintenance overhead for inserts, updates, and deletes. In some cases, the right answer is to rewrite the query, update statistics, or change the data model instead of adding another index. I also avoid indexing blindly on low-cardinality columns unless the access pattern clearly supports it. After creating an index, I monitor whether it improves the query in practice and whether it has any negative effect on other workloads. The goal is always to improve the system overall, not just one statement in isolation.
Question 7
Difficulty: hard
How do you troubleshoot blocking and deadlocks in a busy production database?
Sample answer
I start by capturing enough evidence to understand the transaction behavior rather than guessing. For blocking, I look at who is holding locks, what they are waiting on, how long the transactions have been open, and whether the same application pattern keeps appearing. Deadlocks require a little more detail, so I review the deadlock graphs or trace data to identify the exact resources and ordering conflicts involved. Once I know the pattern, I look for the root cause: long transactions, inconsistent table access order, missing indexes, overly broad queries, or application logic that keeps locks open too long. I usually work in parallel with the application team because many blocking issues are really design issues, not just database configuration problems. Short-term, I may help reduce lock duration or adjust isolation settings where appropriate. Long-term, I focus on removing the pattern that creates the contention so the issue does not keep coming back during peak traffic.
Question 8
Difficulty: hard
How do you manage a database migration with minimal downtime?
Sample answer
For a migration, I start by understanding the source and target platforms, the data volume, dependency chains, and the acceptable downtime window. Then I build a detailed cutover plan that includes pre-migration validation, backup checkpoints, rehearsal runs, and a rollback path if something unexpected happens. If downtime needs to be minimal, I look at options like replication, log shipping, phased synchronization, or a blue-green style cutover depending on the technology stack. I also make sure the application team, infrastructure team, and business stakeholders all know exactly when changes will happen and what the impact will be. In practice, the biggest risk is often not the data transfer itself but the hidden dependencies, like scheduled jobs, permissions, connection strings, or incompatible stored procedures. I always test the migration in a non-production environment first and compare row counts, checksums, and business-critical queries after the move. A good migration is one that feels uneventful to users.
Question 9
Difficulty: medium
What would you do if a developer asked for direct production access to investigate an urgent issue?
Sample answer
I would take the request seriously, but I would not grant broad access without controls. In an urgent situation, I try to balance speed with security and auditability. My first step would be to understand exactly what the developer needs to inspect and whether I can help by running the query myself, providing read-only access, or creating a controlled session with limited permissions. If direct access is necessary, I would work through the approved process, keep the access scoped as narrowly as possible, and make sure logging is enabled so the activity is fully traceable. I also prefer to pair with the developer during the investigation so the issue gets solved quickly without creating unnecessary risk. After the incident, I would review whether our standard access model is too restrictive for legitimate troubleshooting and whether we should improve our break-glass procedures. The goal is to support the business without weakening security or accountability.
Question 10
Difficulty: easy
How do you ensure data consistency and integrity in a database environment?
Sample answer
I treat data integrity as a combination of design, controls, and ongoing validation. At the design level, I rely on primary keys, foreign keys, check constraints, and appropriate normalization where it makes sense for the application. I also make sure transactions are structured carefully so related changes succeed or fail together, which prevents partial updates. On the operational side, I review jobs, ETL processes, and application integrations because many data issues come from outside the database engine itself. I like to monitor for orphaned records, duplicate entries, and unusual patterns that suggest a process has gone off track. For critical systems, I also schedule integrity checks and compare key metrics against known baselines so we catch issues early. When problems do appear, I focus on identifying whether the source is bad input, an application bug, or a broken process, because fixing the symptom without correcting the source only postpones the next incident. Data integrity has to be actively maintained, not assumed.
