SQL Server Q&A | Part 1

SQL Server Interview Questions & Answers

200 Questions: Fundamentals to Architect-Level Scenarios

Part I: Core SQL Server Questions

Topic 1: SQL Server Fundamentals

  1. What is SQL Server?
  2. What is the difference between SQL Server and SSMS?
  3. What is a SQL Server instance?
  4. What are the SQL Server system databases?
  5. What is tempdb?
  6. What is the difference between MDF, NDF and LDF files?
  7. What is a filegroup?
  8. What is a transaction?
  9. Explain ACID properties.
  10. What is a heap?

Topic 2: SQL Server Architecture & Internals

  1. What happens internally when SQL Server executes a query?
  2. What is an 8 KB data page?
  3. What is an extent?
  4. What are PFS, GAM and SGAM pages?
  5. What is the Buffer Pool?
  6. What is a checkpoint?
  7. What is a dirty page?
  8. What is Write-Ahead Logging?
  9. Does a clustered index physically sort a table forever?
  10. What happens during SQL Server crash recovery?

Topic 3: Database Files, Filegroups & Storage

  1. Why would you create multiple data files?
  2. What is the PRIMARY filegroup?
  3. What are user-defined filegroups?
  4. Can tables and indexes be placed on different filegroups?
  5. How does SQL Server allocate pages and extents?
  6. What happens when a database file becomes full?
  7. What is file autogrowth?
  8. Percentage growth vs fixed-MB growth: which is preferable?
  9. Can a database have multiple transaction log files?
  10. When are multiple log files actually useful?

Topic 4: Recovery Models & Transaction Log

  1. What SQL Server recovery models are available?
  2. What is Simple recovery?
  3. What is Full recovery?
  4. What is Bulk-Logged recovery?
  5. Why does the transaction log keep growing?
  6. Does a full backup truncate the transaction log?
  7. What breaks the transaction log backup chain?
  8. What is the transaction log?
  9. What are Virtual Log Files?
  10. Should the transaction log be shrunk regularly?
  11. How do you determine why log space cannot be reused?
  12. What does log_reuse_wait_desc tell you?

Topic 5: Backup & Restore

  1. What backup types are available?
  2. What is a Full backup?
  3. What is a Differential backup?
  4. What is a transaction log backup?
  5. What is a tail-log backup?
  6. What is a COPY_ONLY backup?
  7. Explain NORECOVERY, RECOVERY and STANDBY.
  8. What is Point-in-Time Recovery?
  9. What is a differential base?
  10. Why should DBAs regularly test restores?

Topic 6: Indexes & Statistics

  1. What is an index?
  2. Clustered vs nonclustered index?
  3. What is index fragmentation?
  4. Rebuild vs reorganize?
  5. What is a covering index?
  6. What is a Key Lookup?
  7. Index Seek vs Index Scan?
  8. Can adding more indexes make SQL Server slower?
  9. What are included columns?
  10. What are SQL Server statistics?

Topic 7: Query Processing & Performance Tuning

  1. How do you troubleshoot a slow SQL query?
  2. What is an execution plan?
  3. Estimated vs Actual Execution Plan?
  4. What is parameter sniffing?
  5. How would you troubleshoot a parameter-sensitive plan?
  6. What are SQL Server wait statistics?
  7. What does PAGEIOLATCH_* indicate?
  8. What does WRITELOG indicate?
  9. Is 99% Buffer Cache Hit Ratio always good?
  10. Does increasing SQL Server memory always improve performance?
  11. Should MAXDOP always be 1?
  12. What is Query Store?

Topic 8: Locks, Blocking & Deadlocks

  1. What is locking?
  2. What is blocking?
  3. What is a deadlock?
  4. Blocking vs deadlocking?
  5. How do you troubleshoot deadlocks?
  6. What is lock escalation?
  7. What are Shared, Update and Exclusive locks?
  8. Is NOLOCK a solution to blocking?

Topic 9: Isolation & Concurrency

  1. What isolation levels does SQL Server support?
  2. What is READ UNCOMMITTED?
  3. What is READ COMMITTED?
  4. What is RCSI?
  5. RCSI vs SNAPSHOT isolation?
  6. What is a dirty read?
  7. What is a non-repeatable read?
  8. What is a phantom read?

Topic 10: Security

  1. Login vs database user?
  2. What are fixed server roles?
  3. What are database roles?
  4. What is Transparent Data Encryption?
  5. TDE vs Always Encrypted?
  6. What is the principle of least privilege?

Topic 11: HA/DR & Administration

  1. What SQL Server HA/DR technologies are available?
  2. FCI vs Availability Groups?
  3. What is RPO and RTO?
  4. What should be monitored on a production SQL Server?
Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Popular posts from this blog

SQL Server Interview Questions & Answers: 200 Questions from Fundamentals to Architect Level

Image
Technical Architects Zone (TAZ) SQL Server Interview Preparation SQL Server Interview Questions & Answers 200 Questions from Fundamentals to Advanced DBA and Architect-Level Scenarios Introduction Preparing for a SQL Server interview is not only about remembering definitions, commands and feature names. As database professionals move into Senior DBA, Technical Lead and Database Architect roles, interviews increasingly focus on how production problems are investigated, how technical decisions are made and how critical database situations are handled. To make interview preparation more practical, Technical Architects Zone provides a structured collection of 200 SQL Server Interview Questions & Answers . The questions progress from core SQL Server fundamentals to advanced production scenarios covering troubleshooting...
Read more

SQL Server Storage Architecture Explained: How SQL Server Stores Data Internally

Image
  Introduction Have you ever wondered what happens when you insert a row into a SQL Server table? Where does the data actually go? How does SQL Server find free space? How are indexes stored? Why do DBAs talk about Pages, Extents, GAM, SGAM, PFS, and IAM? Understanding SQL Server Storage Architecture is one of the most important skills for any DBA, Database Architect, or Performance Engineer because almost every performance issue eventually leads back to how data is stored internally. In this article, we will simplify SQL Server's internal storage mechanism using practical examples. Why Storage Architecture Matters Understanding storage internals helps you: ✅ Troubleshoot performance issues ✅ Understand index fragmentation ✅ Optimize large tables ✅ Reduce disk I/O ✅ Improve backup and restore strategies ✅ Analyze space usage ✅ Perform advanced DBA troubleshooting The Foundation: Data Pages SQL Server stores all data in Pages . A Page is the smallest unit of storage. Page Size -...
Read more

SQL Server Performance Troubleshooting: Essential Scripts Every Database personnel or Architect Should Have

Image
TECHNICAL ARCHITECTS ZONE SQL Server Performance Troubleshooting A practical collection of SQL Server scripts and diagnostic techniques for investigating CPU, I/O, blocking, waits, memory, TempDB and query-performance issues. Performance issues are among the most common challenges handled by SQL Server DBAs. A typical incident starts with a broad statement such as “the application is slow,” but that statement does not identify whether the underlying problem is blocking, CPU pressure, storage latency, memory pressure, TempDB contention or an inefficient query. Effective troubleshooting begins by collecting evidence from SQL Server before making configuration or application changes. This article provides practical Dynamic Management View queries that can help identify where to begin. TAZ Practical Note Capture evidence ...
Read more