MSSQL DBA LAB interview Questions

 MSSQL DBA LAB interview Questions


SECTION 1 — Installation, Instance Configuration & Startup (15)

  1. Install SQL Server 2022 with only Database Engine + Full‑Text; validate features and service accounts.
  2. Change default instance to use a dedicated collation; assess rebuilding system databases vs new install.
  3. Move system databases (master, model, msdb) to new drives and restart cleanly.
  4. Configure default data/log/backup directories for new databases.
  5. Run instance under gMSA and validate SPNs for Kerberos.
  6. Configure MaxDOP and Cost Threshold for Parallelism based on CPU layout; benchmark before/after.
  7. Enable instant file initialization and validate it’s effective.
  8. Setup tempdb with multiple data files, equal sizes, fixed growth; confirm latch contention reduction.
  9. Instance fails to start with “Error 17113” after path change—recover without reinstall.
  10. Switch from mixed to Windows authentication; safely rotate the sa password and disable the sa login.
  11. Configure Dedicated Admin Connection (DAC) and test remote DAC access.
  12. Diagnose “Login failed for user” storms; identify source app from logs.
  13. Automate SQL Server startup options (trace flags) and verify via error log.
  14. Validate TLS 1.2‑only connectivity; disable weak ciphers at OS level; test client apps.
  15. Fix collation mismatch between instance and database during migration.

🟦 SECTION 2 — Security, Logins, Roles, Encryption (20)

  1. Map orphaned users to logins after database restore; fix SID mismatches.
  2. Enforce Contained Database authentication and migrate logins into DB scope.
  3. Implement Always Encrypted with secure enclaves for a PII column; prove client‑side encryption.
  4. Configure Transparent Data Encryption (TDE); back up cert and key; test restore on another server.
  5. Audit failed logins and privileged actions using SQL Server Audit to a file target.
  6. Create custom server roles and delegate least privilege for deployment pipelines.
  7. Identify logins with UNSAFE assemblies or CONTROL SERVER; reduce privileges safely.
  8. Force password policy and expiration for SQL logins; detect non‑compliant accounts.
  9. Implement row‑level security predicate for a multi‑tenant table; verify plan stability.
  10. Restrict xp_cmdshell usage and detect any attempts to re‑enable.
  11. Configure Always On endpoints with certificates for cross‑domain replicas.
  12. Trace who dropped a critical login; reconstruct timeline from default trace/extended events.
  13. Enable TDS encryption (force encryption) and validate from client side.
  14. Detect logins connecting from unexpected subnets; enforce logon triggers with guardrails.
  15. Rotate TDE keys; re‑encrypt the database; validate restore ability with old backups.
  16. Secure Linked Server creds; detect plaintext password usage in scripts.
  17. Identify and disable SQL Browser where not required; test multi‑instance connectivity.
  18. Implement Certificate‑based signatures for elevated stored procedures.
  19. Lock down CLR with strict permission sets and signing; detect unsafe assemblies.
  20. Inventory all public role permissions and revert accidental grants.

🟦 SECTION 3 — Database Files, Transaction Log & Recovery (20)

  1. A database log grows to fill disk—stabilize log growth without data loss; identify cause.
  2. Switch recovery model FULL → SIMPLE → FULL safely; validate log backup chain continuity.
  3. Fix “The log for database is not available” after abrupt shutdown; recover with minimal downtime.
  4. Automate pre‑sizing and auto‑growth policies to eliminate VLF proliferation; validate with DBCC LOGINFO/LOGINFO2.
  5. Shrink a log file safely after addressing cause—prove no log reuse wait remains.
  6. Diagnose log reuse wait reasons using sys.databases; eliminate ACTIVE_TRANSACTION or REPLICATION wait.
  7. Split data and log to separate disks; baseline write latencies with sys.dm_io_virtual_file_stats.
  8. Fix “Cannot recover the master database” on startup—restore master from backup.
  9. Convert multiple‑file filegroup design for a hot table; test proportional fill distribution.
  10. Enable Accelerated Database Recovery (ADR); measure rollback/recovery improvements.
  11. Repair inconsistent file metadata after interrupted growth.
  12. Implement FILESTREAM; store and query BLOBs; test backup/restore.
  13. Recover a database stuck in In Recovery state after crash.
  14. Move data files to new storage with ALTER DATABASE … MODIFY FILE and downtime planning.
  15. Detect auto‑shrink enabled and remove it; demonstrate fragmentation impact.
  16. Create read‑only filegroup for archive data; prove write blocking.
  17. Fix suspect database; decide between restore vs emergency mode repair.
  18. Use DBCC CHECKDB with PHYSICAL_ONLY on schedule; test full integrity weekly.
  19. Interpret DBCC CHECKDB output; repair minimally without data loss where possible.
  20. Investigate and fix page checksum errors; track to storage vs memory issues.

🟦 SECTION 4 — Backup, Restore, PITR & DR (25)

  1. Implement full + diff + log strategy with msdb job history and retention.
  2. Validate page‑level restore for a single damaged page.
  3. Perform file/filegroup restore for a large DB to minimize recovery time.
  4. Execute point‑in‑time restore using marked transactions.
  5. Restore a database with MOVE clauses to new paths; fix orphaned users post‑restore.
  6. Test copy‑only backups not breaking diff chain.
  7. Implement backup compression; measure CPU vs time savings.
  8. Encrypt backups at rest; store keys safely; verify out‑of‑band restore.
  9. Validate backups with RESTORE VERIFYONLY and DBCC CHECKDB on restored copies.
  10. Script automated cleanup of old backup files; prevent accidental deletion of newest.
  11. Restore production backup to lower environment with data masking.
  12. Simulate DR to a different data center; document RTO/RPO achieved.
  13. Restore master and msdb to a clean instance; re‑register jobs, operators, alerts.
  14. Validate log shipping configuration; simulate primary outage and manual failover.
  15. Compare log shipping vs AG readable secondary for reporting; measure latency.
  16. Throttle backups to reduce I/O contention during business hours.
  17. Investigate failed backups due to VSS writers on Windows; fix coordination.
  18. Capture backup throughput metrics and identify bottlenecks.
  19. Prove you can restore TDE‑encrypted backup on a new server (cert/keys).
  20. Restore a differential without proper base—detect and correct sequence.
  21. Validate transaction log chain across copy‑only full backups.
  22. Implement staging restores for nightly integrity checks.
  23. Test partial restores for read‑only filegroups.
  24. Build DR runbook; conduct a full failover simulation.

🟦 SECTION 5 — High Availability: Always On AG / FCI / DR (30)

  1. Build a 2‑node FCI (Failover Cluster Instance) on WSFC; validate shared storage.
  2. Deploy a 3‑replica Always On Availability Group with automatic failover.
  3. Configure synchronous + asynchronous replicas; test commit latency and failover combos.
  4. Configure Readable Secondary routing lists; validate read‑intent connection strings.
  5. Test automatic seeding; measure backup preferences impact.
  6. Simulate automatic failover; verify client reconnection with listener.
  7. Diagnose AG synchronization health; identify HADR_WAITONLINE issues.
  8. Fix seeding failures and AG replica join errors with Extended Events.
  9. Simulate split‑brain risk (multi‑subnet); show prevention with quorum/witness.
  10. Configure multi‑subnet listener; test client retry logic across subnets.
  11. Tune Availability Mode and Session Timeout; observe failover sensitivity.
  12. Validate backup on secondary strategy; ensure proper copy‑only usage.
  13. Test planned manual failover with zero data loss; confirm synchronization state.
  14. Validate DTC support across AGs for distributed transactions.
  15. Integrate AGs with TDE across replicas; synchronize certs.
  16. Implement read‑scale availability groups (no WSFC) and load balance reporting.
  17. Capture AG latency and redo queue metrics; correlate to workload.
  18. Rebuild a failed replica without re‑initializing the primary.
  19. Diagnose log send/redo queue growth under heavy load; optimize.
  20. Perform in‑place upgrade of AG nodes with minimal downtime.
  21. Restore quorum after multiple node failures in a stretched cluster.
  22. Evaluate distributed availability groups across regions; measure lag.
  23. Fix listener DNS registration issues across domains.
  24. Configure automatic seeding with compression; verify impact.
  25. Enable Accelerated Database Recovery with AGs; observe failover behavior.
  26. Create policy‑based management to enforce HADR configurations.

🟦 SECTION 6 — Performance, Waits, Query Store & XE (30)

  1. Capture top waits (sys.dm_os_wait_stats) over a peak window; produce before/after snapshot.
  2. Identify high SOS_SCHEDULER_YIELD vs PAGEIOLATCH_*; propose CPU vs I/O fixes.
  3. Detect parameter sniffing; compare forced plan vs recompile strategies.
  4. Enable Query Store; capture regressions; force a known good plan.
  5. Diagnose CXPACKET/CXCONSUMER waits; tune MaxDOP/CTFP properly.
  6. Trace a sudden regression with an Extended Events session; tie to code release.
  7. Identify Top 10 queries by CPU/reads/writes with Query Store.
  8. Detect implicit conversions causing index scans; fix datatypes.
  9. Find missing indexes safely; validate benefit vs overhead.
  10. Detect memory grants causing spills; adjust workload group or query.
  11. Correlate tempdb spills using actual execution plans and XE.
  12. Baseline PFS/SGAM/GAM contention; verify tempdb configuration improvements.
  13. Identify blocking chains; capture lead blocker and query text.
  14. Use Resource Governor to cap runaway ETL workloads; prove effect.
  15. Capture deadlocks using XE; diagram the victim/culprit.
  16. Track auto‑statistics updates; correlate with plan changes.
  17. Diagnose ASYNC_NETWORK_IO; identify chatty app patterns.
  18. Measure server memory pressure via memory clerks/pressure notifications.
  19. Find top I/O consumers per db/file; verify with perf counters.
  20. Capture spinlock contention (advanced); interpret.
  21. Identify RBAR procedural T‑SQL patterns; rewrite set‑based.
  22. Prove improvement from table variables → temp tables under load.
  23. Diagnose latch vs lock contention differences.
  24. Use Automatic Tuning (Azure SQL) to force last good plan; measure impact.
  25. Build a performance baseline workbook and automate daily captures.

🟦 SECTION 7 — Indexing & Schema Design (20)

  1. Find and drop redundant/duplicate indexes; re‑test workload.
  2. Evaluate filtered indexes for selective predicates; validate plan picks.
  3. Convert wide clustered index to narrow surrogate key; measure page density.
  4. Test columnstore index for large fact table; monitor rowgroup quality.
  5. Rebuild vs reorganize strategy; automate thresholds based on fragmentation + page count.
  6. Create covering indexes for hot queries; verify key lookup elimination.
  7. Detect ascending key problems; enable OPTIMIZE FOR SEQUENTIAL KEY (SQL 2022).
  8. Use computed columns + indexes; prove sargability with persisted computed.
  9. Identify heap tables with forwarded records; evaluate clustering.
  10. Evaluate partitioned tables and aligned indexes for sliding window loads.
  11. Migrate a hot unique index to hash partitioning (where applicable).
  12. Detect GUID vs BIGINT PK tradeoffs; show fragmentation differences.
  13. Build spatial indexes on geography data; validate query shapes.
  14. Tune columnstore batch mode performance; address row mode fallbacks.
  15. Validate statistics sampling vs fullscan impact on regressions.
  16. Implement incremental statistics on partitioned tables.
  17. Test memory‑optimized table indexes (Hekaton) for specific workload.
  18. Identify write amplification from too many nonclustered indexes.
  19. Prove benefit of compressed indexes (row/page) on I/O‑bound systems.
  20. Design unique filtered indexes to enforce conditional business rules.

🟦 SECTION 8 — Concurrency, Locks, Deadlocks & Isolation (20)

  1. Compare READ COMMITTED vs SNAPSHOT isolation; measure version store impact.
  2. Enable RCSI; monitor tempdb version store growth; catch long‑running readers.
  3. Generate and capture a deadlock; analyze victim selection and fix.
  4. Diagnose LCK_M_S/IX/X waits from a hot table; deploy mitigations.
  5. Identify metadata locking during DDL; rewrite deployment to avoid blocks.
  6. Track ghost record cleanup and its impact on queries.
  7. Capture lock escalation events; prevent with partitioning or hints.
  8. Fix blocking caused by long implicit transactions.
  9. Identify abandoned transactions from app pool recycles; add safeguards.
  10. Tune snapshot isolation for reporting replicas; validate throughput.
  11. Prove NOLOCK side effects with inconsistent reads; propose alternatives.
  12. Identify THREADPOOL waits under connection storms; tune worker counts.
  13. Isolate HADR_SYNC_COMMIT latency affecting OLTP.
  14. Diagnose SNI / network timeouts vs lock waits.
  15. Trace sp_prepare/sp_execute misuse causing excessive plan cache bloat.
  16. Capture spinlock backoffs tied to latch contention (advanced).
  17. Identify and fix temp table/table variable concurrency hotspots.
  18. Prove improvement from batch mode on rowstore (where supported).
  19. Eliminate implicit conversions on join keys causing S‑X lock skew.
  20. Capture and resolve availability group readable routing deadlocks.

🟦 SECTION 9 — Tempdb, Memory, Storage & I/O (15)

  1. Size tempdb correctly; add files and set trace flags; validate reduced SGAM contention.
  2. Identify spill events to tempdb for sorts/hashes; tune memory grants.
  3. Diagnose PAGEIOLATCH_SH/EX vs WRITELOG patterns; propose storage changes.
  4. Track READ_AHEAD misses; validate sequential scan patterns.
  5. Validate storage tiering benefits (SSD/NVMe) for log vs data.
  6. Use Buffer Pool Extension (legacy feature) on SSD; measure effect (educational).
  7. Detect compressed backups vs storage dedupe impacts.
  8. Analyze I/O queue depth saturation; coordinate with SAN team.
  9. Prove impact of PAGE verification (CHECKSUM) on corruption detection.
  10. Identify tempdb growth storms; pin to culprit queries.

🟦 SECTION 10 — SQL Agent, SSIS/ETL & Automation (15)

  1. Harden SQL Agent security; proxy credentials; separate service accounts.
  2. Create robust retry logic for failing jobs; add alerts/operators.
  3. Build database mail with OAuth (where supported) or secure relay; send alerts.
  4. Audit which jobs modify data in key databases; tag and document owners.
  5. Capture job runtime baselines; alert on anomalies automatically.
  6. Implement SSIS catalog; deploy package; secure environments.
  7. Troubleshoot SSIS package memory leaks and parallelism settings.
  8. Automate index and statistics maintenance with adaptive thresholds.
  9. Create a self‑healing job to detect and kill runaway sessions by policy.
  10. Build a first responder script pack execution (sp_Blitz, etc.) on schedule.
  11. Orchestrate ETL dependencies with Agent; add failure fencing.
  12. Capture job history to a warehouse; visualize SLAs.
  13. Validate Agent failover behavior on FCI/AG nodes.
  14. Implement Powershell health checks across a fleet of instances.
  15. Build policy‑based management for configuration drift.

🟦 SECTION 11 — Azure SQL / Managed Instance / Hybrid (20)

  1. Migrate on‑prem DB to Azure SQL Managed Instance with DMS; validate compatibility.
  2. Enable Geo‑replication (Azure SQL DB); test failover and latency.
  3. Configure Auto‑failover groups across regions; test read‑write listener routing.
  4. Use Automatic tuning (force last good plan) and measure improvement.
  5. Diagnose TempDB performance on Managed Instance; adjust workload.
  6. Capture wait stats and query store on Azure SQL DB with limited permissions.
  7. Integrate Azure Key Vault with TDE; rotate keys and validate restore.
  8. Evaluate Hyperscale tier (Azure SQL) for large database; measure log service behavior.
  9. Validate long‑term backup retention (LTR) restores in a sandbox.
  10. Compare Private Link vs Public endpoint; restrict inbound traffic.
  11. Validate vCore sizing vs DTU workloads; scale up/down with minimal impact.
  12. Audit connection failures via Azure diagnostics; correlate to app changes.
  13. Implement failover runbook for Azure SQL + on‑prem hybrid apps.
  14. Test BACPAC export/import for schema‑only migrations; capture pitfalls.
  15. Validate serverless auto‑pause/resume behavior on Azure SQL DB.
  16. Measure Storage IOPS limits per tier; throttle workloads.
  17. Implement ADF/SSIS IR pipelines to/from Azure SQL; secure secrets.
  18. Capture Intelligent Insights incidents; validate recommendations.
  19. Test Active Geo‑replica seeding time vs DB size.

Comments

Post a Comment