BEGINNER LEVEL (Q1–Q35)

1. What does a SQL Server DBA actually do day to day?

At its core, a DBA keeps databases running, safe, and fast. That means making sure backups are happening and are actually restorable, keeping an eye on performance, managing who has access to what, applying patches, and being the person who gets paged at 2 AM when something goes sideways. On any given day it’s a mix of proactive maintenance and reactive firefighting.

2. What are the different editions of SQL Server, and how do they differ?

The main ones are Express (free, with size and resource limits — great for small apps), Standard (core features for typical business workloads), and Enterprise (the full feature set, including advanced high-availability, partitioning, and columnstore capabilities for large, mission-critical systems). There’s also Developer edition, which has all Enterprise features but is licensed for non-production use only.

3. What’s the difference between an instance and a database?

An instance is a full running copy of the SQL Server engine — it has its own set of system databases, security, and configuration. A database lives inside an instance and holds your actual application data. You can have multiple instances on one server, and each instance can host many databases.

4. What are the system databases in SQL Server, and what does each do?

master holds instance-wide configuration and metadata about all databases. model acts as a template — anything in it gets copied into every new database you create. msdb stores information for SQL Server Agent (jobs, schedules, alerts) and backup history. tempdb is scratch space for temporary tables, sorting, and other short-lived work, and it gets recreated fresh every time the instance restarts.

5. What is the difference between SQL Server Authentication and Windows Authentication?

Windows Authentication lets users log in using their existing Windows/Active Directory credentials — SQL Server trusts Windows to have already verified who they are. SQL Server Authentication uses a separate username and password stored and checked by SQL Server itself, independent of Windows. Most security-conscious shops prefer Windows Authentication because it centralizes account management and password policies.

6. What’s the difference between a login and a user?

A login is created at the server (instance) level and controls who can connect to the SQL Server instance at all. A user is created at the database level and maps to a login, controlling what that person can actually do inside a specific database. Think of the login as your building keycard and the user as your permission to enter a specific office inside that building.

7. What are the three basic types of backups in SQL Server?

Full backups capture the entire database at a point in time. Differential backups capture only the data that’s changed since the last full backup. Transaction log backups capture all the log activity since the last log backup, which is what enables point-in-time recovery. Most production systems use some combination of all three.

8. What are the three database recovery models, and how do they differ?

Simple recovery model automatically clears out the transaction log after each checkpoint, which means no log backups are needed, but you also can’t do point-in-time recovery — you’re limited to your last full/differential backup. Full recovery model keeps everything in the log until you back it up, enabling point-in-time recovery, but you must actively take log backups or the log file will keep growing. Bulk-logged is a middle ground, mainly used to minimize logging during large bulk operations while still allowing some log backup capability.

9. What is a checkpoint in SQL Server?

A checkpoint writes all the dirty (modified but not yet saved to disk) pages from memory down to the actual data files on disk. It happens periodically and helps reduce the amount of work SQL Server has to redo during a crash recovery, since it doesn’t have to replay the entire log from the beginning.

10. What is SQL Server Agent, and what is it used for?

SQL Server Agent is the built-in job scheduler and automation engine. DBAs use it to schedule backups, maintenance tasks (like index rebuilds or integrity checks), and alerts that notify you when something goes wrong (like a job failure or a specific error occurring). It’s one of the first things a DBA sets up on a new instance.

11. What is the difference between a clustered and a non-clustered index?

A clustered index determines the actual physical order the data is stored in on disk — a table can only have one, since data can only be physically sorted one way. A non-clustered index is a separate structure that points back to the data, kind of like the index at the back of a book — you can have many of them on a single table.

12. What is a primary key, and how does it relate to indexes?

A primary key is a constraint that uniquely identifies every row in a table and doesn’t allow NULLs. By default, SQL Server automatically creates a unique clustered index to enforce it, though you can specify it as non-clustered if you already have another column you’d rather cluster on.

13. What’s the difference between DELETE, TRUNCATE, and DROP?

DELETE removes rows one at a time (optionally with a WHERE clause), logs each row deletion, and can be rolled back. TRUNCATE removes all rows at once, is minimally logged, and is much faster, but you can’t filter which rows to remove. DROP removes the entire table structure itself, not just the data — the table stops existing altogether.

14. What is a transaction, and what does “ACID” mean?

A transaction is a group of one or more operations treated as a single unit of work — either all of them succeed, or none of them do. ACID stands for Atomicity (all-or-nothing), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions don’t interfere with each other), and Durability (once committed, changes survive even a crash).

15. What’s the difference between COMMIT and ROLLBACK?

COMMIT permanently saves all the changes made during a transaction. ROLLBACK undoes everything done since the transaction started, as if it never happened. If you don’t explicitly commit or roll back and the connection drops or an error occurs, SQL Server’s behavior depends on your transaction handling settings — but generally uncommitted work will be rolled back.

16. What is SSMS, and what’s it used for?

SQL Server Management Studio is the primary graphical tool DBAs and developers use to connect to instances, write and run queries, manage security, configure backups, and generally administer everything about SQL Server through a visual interface rather than pure command line.

17. How do you check the version of SQL Server you’re running?

The simplest way is running SELECT @@VERSION;, which gives you the full version string including the edition and build number. You can also check SELECT SERVERPROPERTY('ProductVersion'); for just the numeric version, which is handy for scripting comparisons.

18. What is the purpose of the tempdb database, and why does its configuration matter?

Tempdb is shared scratch space used by every database on the instance for temporary tables, table variables, sort operations, and version stores (for features like snapshot isolation). Because it’s shared and heavily used, poor tempdb configuration (like having too few data files, or files on slow storage) can become a bottleneck affecting every single database on the instance.

19. What is a DMV, and can you name a couple of commonly used ones?

DMV stands for Dynamic Management View — these are built-in views that expose real-time information about server health, performance, and internal state. Common ones include sys.dm_exec_requests (currently running queries), sys.dm_os_wait_stats (what SQL Server has been waiting on), and sys.dm_db_index_usage_stats (how your indexes are actually being used).

20. What is blocking, and is it always a bad thing?

Blocking happens when one session holds a lock on a resource that another session needs, forcing the second session to wait. It’s actually a completely normal and necessary part of how relational databases keep data consistent — the problem only arises when blocking becomes excessive or long-lasting, which is when users start noticing slowness or timeouts.

21. What is a deadlock?

A deadlock occurs when two (or more) sessions each hold a resource the other one needs, and neither can proceed — they’re stuck waiting on each other in a circle. SQL Server automatically detects this situation and kills off one of the sessions (the “deadlock victim”) to let the other one continue.

22. What’s the difference between a heap and a clustered table?

A heap is a table with no clustered index — rows aren’t stored in any particular guaranteed order, just wherever there’s space. A clustered table has a clustered index, so the data rows themselves are physically organized based on the index key. Heaps can be efficient for pure insert-heavy staging tables but often perform worse for regular query workloads.

23. What is the purpose of the SQL Server Error Log, and where do you find it?

It’s a text-based log recording significant server-level events — startups, shutdowns, errors, backup completions, and more. You can view it in SSMS under Management > SQL Server Logs, or query it directly with sp_readerrorlog. It’s usually the very first place to check when something’s gone wrong on an instance.

24. What is the difference between a scheduled job failing and an alert firing?

A job failure means a specific SQL Server Agent job (like a backup task) didn’t complete successfully. An alert is a separate mechanism that watches for specific conditions — like a particular error number, severity level, or performance condition — and can notify you (often via email) independent of any job. You typically want both configured so you hear about problems from multiple angles.

25. What does RTO and RPO mean, and why do DBAs care?

RPO (Recovery Point Objective) is how much data loss is acceptable, measured in time — “we can afford to lose up to 15 minutes of data.” RTO (Recovery Time Objective) is how quickly you need to be back up and running after a failure. These two numbers directly shape your backup strategy and high-availability design — a low RPO/RTO business need means far more sophisticated (and expensive) infrastructure.

26. What is a maintenance plan?

It’s a built-in wizard-driven feature for scheduling routine upkeep tasks — backups, integrity checks (DBCC CHECKDB), index reorganization/rebuilds, and statistics updates. Many DBAs eventually move to custom scripts (like the well-known Ola Hallengren maintenance solution) for more control, but maintenance plans are a solid starting point, especially for smaller environments.

27. What is DBCC CHECKDB used for?

It checks the logical and physical integrity of all the objects in a database — catching corruption before it becomes a much bigger problem. Running it regularly (and reviewing backups’ integrity) is one of the most important, if unglamorous, DBA habits.

28. What’s the difference between a schema and a database?

A database is the overall container for your data, files, and objects. A schema is a logical grouping within a database used to organize objects (tables, views, procedures) and manage permissions more granularly — for example, Sales.Orders and HR.Employees might live in the same database but under different schemas.

29. What is the purpose of collation in SQL Server?

Collation determines how SQL Server compares and sorts character data — things like case sensitivity, accent sensitivity, and sort order rules for different languages. It’s set at the server, database, and even column level, and mismatched collations between systems can cause frustrating comparison errors during joins or data migrations.

30. What is the difference between a view and a table?

A table physically stores data. A view is a saved, reusable query that presents data (often from one or more tables) without storing it separately — it’s essentially a virtual table that recalculates its result every time you query it (unless it’s an indexed/materialized view).

31. What is a stored procedure, and why are they commonly used?

A stored procedure is a precompiled, reusable batch of SQL code stored in the database that can accept parameters and return results. DBAs and developers like them because they reduce network traffic, allow for centralized logic, and — importantly for security — let you grant execute permissions on the procedure without exposing direct table access.

32. What is the purpose of a service account for SQL Server, and why shouldn’t it just run as a local administrator?

The service account is the Windows account under which the SQL Server process actually runs. Running it as a full local administrator is a security risk — if the SQL Server process were ever compromised, the attacker would inherit full admin rights over the whole machine. Best practice is a dedicated, least-privilege service account with only the specific rights SQL Server actually needs.

33. What is the difference between a physical and a logical file name for a database?

The logical name is what you refer to the file as within SQL Server (used in commands like ALTER DATABASE). The physical name is the actual file path and name on disk (like D:\Data\MyDB.mdf). They’re often similar but don’t have to match, and confusing the two is a classic mistake when scripting file operations.

34. What is the purpose of the model database specifically?

Whatever settings, objects, or configurations exist in the model database get copied into every new database created afterward on that instance. So if you want every new database to start with a certain recovery model, or a standard table/schema, you’d configure it once in model.

35. Why is it important to regularly test your backups, not just take them?

A backup is only as good as your ability to actually restore it — corrupted backup files, missing log chains, or overlooked permissions issues often only surface when you try to restore, which is the worst possible time to discover them. “Untested backup” and “no backup” carry more similar risk than most people realize.

INTERMEDIATE LEVEL (Q36–Q70)

36. Walk through how you’d restore a database to a specific point in time.

You’d need an unbroken chain: restore the last full backup with NORECOVERY, then the most recent differential (if any) with NORECOVERY, then apply transaction log backups in sequence, using WITH STOPAT to specify the exact time you want to recover to, and finally bring the database online with RECOVERY on the last step. Missing any link in that chain, or having a broken log sequence, means point-in-time recovery isn’t possible past that gap.

37. What is the difference between WITH NORECOVERY and WITH RECOVERY during a restore?

WITH NORECOVERY leaves the database in a restoring state, expecting more backup files to be applied afterward — useful mid-sequence. WITH RECOVERY rolls back any uncommitted transactions and brings the database fully online, ready for use — this should only be your final restore step.

38. What is log shipping, and what are its trade-offs compared to Always On Availability Groups?

Log shipping regularly backs up the transaction log on a primary server and automatically restores it on one or more secondary servers, keeping them in a slightly delayed, near-real-time synced state. It’s simple, well-understood, and cheap in licensing terms, but failover is manual (or scripted) rather than automatic, and there’s typically more data-loss exposure than with Availability Groups, since it operates on a scheduled interval rather than continuous synchronization.

39. What is an Always On Availability Group, and what does it require?

It’s a high-availability and disaster-recovery feature where a set of user databases fail over together as a single unit across multiple replicas, using continuous data synchronization rather than scheduled log shipping. It requires Windows Server Failover Clustering (WSFC) underneath (or a similar clustering mechanism in newer non-Windows configurations) and Enterprise edition for the full multi-replica feature set, though Standard edition supports a limited “Basic Availability Group” with just two replicas.

40. What’s the difference between synchronous and asynchronous commit in an Availability Group?

In synchronous commit mode, the primary waits for the secondary to confirm the transaction log has been written before considering the transaction committed — this guarantees zero data loss on failover but adds latency. Asynchronous commit mode lets the primary commit immediately without waiting, which is faster but means a failover could lose the most recent transactions that hadn’t yet made it to the secondary.

41. What is database mirroring, and why has it largely been replaced?

Mirroring maintained a single, complete copy of a database on a separate server, with automatic failover support in high-safety mode using a witness server. Microsoft deprecated it in favor of Always On Availability Groups because AGs support multiple databases failing over together, multiple readable secondaries, and more flexible topologies — mirroring only ever handled one database at a time.

42. What is a witness server, and what role did it play?

In database mirroring’s high-availability mode, the witness was a lightweight third server whose only job was to help the principal and mirror agree on whether an automatic failover should occur — essentially breaking ties to avoid “split-brain” scenarios where both servers might think they should be primary.

43. What is replication in SQL Server, and how is it different from Always On?

Replication copies and synchronizes specific data (whole tables, or even filtered subsets of rows/columns) from one database to one or more destinations, often across different platforms or for reporting/offloading purposes. Always On operates at the whole-database level for high availability and disaster recovery, whereas replication is more about distributing and sharing data broadly, sometimes to non-SQL Server destinations entirely.

44. What are the main types of replication?

Snapshot replication periodically takes a full copy of the data and pushes it out. Transactional replication continuously sends individual data changes as they happen, keeping subscribers very close to real-time. Merge replication allows changes at multiple locations (including subscribers) that get reconciled together, which is useful for distributed or occasionally-connected scenarios but adds conflict-resolution complexity.

45. How would you troubleshoot a sudden spike in blocking on a production server?

I’d start by querying sys.dm_exec_requests and sys.dm_os_waiting_tasks to identify the head blocker — the session at the root of the blocking chain — and look at what query it’s running and what lock it’s holding. From there, it’s often either a long-running transaction that hasn’t committed, a missing index causing an overly broad scan/lock, or an application not managing its transactions properly.

46. What is a wait type, and how do you use wait statistics to troubleshoot performance?

A wait type describes what a query was waiting on when it wasn’t actively running on the CPU — things like waiting for a lock, waiting for a disk read to complete, or waiting for memory. Aggregated wait statistics (from sys.dm_os_wait_stats) give you a top-down view of where the instance as a whole is spending its idle time, which is often a much faster diagnostic starting point than guessing at individual queries.

47. What is PAGELATCH contention, and when do you typically see it?

PAGELATCH waits happen when multiple threads are trying to access the same in-memory page simultaneously — commonly seen on tempdb’s allocation pages when many sessions are creating and dropping temp objects concurrently. The classic fix is adding more tempdb data files (of equal size) to spread that allocation contention across multiple files.

48. Why does Microsoft recommend multiple tempdb data files, and how many should you configure?

Because tempdb’s internal allocation structures (like GAM/SGAM pages) can become a contention point under heavy concurrent load, and splitting tempdb into multiple equally-sized files lets SQL Server spread that allocation work across them via round-robin (proportional fill). A common modern starting guideline is one file per logical CPU core, up to about 8, then reassessing based on observed contention beyond that.

49. What is the difference between a shared lock and an exclusive lock?

A shared lock allows multiple sessions to read the same data simultaneously but prevents anyone from modifying it while those shared locks are held. An exclusive lock is held by a session that’s modifying data, and it blocks any other session — read or write — from accessing that same data until the exclusive lock is released.

50. What is lock escalation, and why can it cause unexpected blocking?

When a query holds a very large number of individual row or page locks, SQL Server may automatically “escalate” those into a single table-level lock to reduce memory overhead. The downside is a table-level lock is far more blunt — it blocks essentially all other access to that table, which can surprise you when a seemingly simple update suddenly blocks unrelated queries.

51. What is the difference between optimistic and pessimistic concurrency?

Pessimistic concurrency (the SQL Server default) assumes conflicts are likely, so it takes locks proactively to prevent other sessions from interfering. Optimistic concurrency (used with row versioning/snapshot isolation) assumes conflicts are rare, letting transactions proceed without blocking readers and instead checking for conflicts only at commit time.

52. What is Read Committed Snapshot Isolation (RCSI), and what problem does it solve?

RCSI changes the default READ COMMITTED behavior so readers use a versioned snapshot of the data instead of taking shared locks — meaning readers no longer block writers, and writers no longer block readers. It’s a database-level setting that many organizations enable to dramatically cut down on blocking-related complaints, though it does add some tempdb overhead for storing those row versions.

53. What is index fragmentation, and how do you check for it?

Fragmentation happens over time as data is inserted, updated, and deleted, causing the logical order of index pages to drift out of alignment with their physical order on disk (and/or leaving pages only partially full). You check it using sys.dm_db_index_physical_stats, which reports a fragmentation percentage per index that you can use to decide between reorganizing or rebuilding.

54. What’s the difference between rebuilding and reorganizing an index?

Rebuilding drops and recreates the index from scratch, fully removing fragmentation and updating statistics with a full scan — but it’s more resource-intensive and, depending on edition/options, can be an offline operation. Reorganizing is a lighter-weight, always-online operation that defragments pages in place without fully rebuilding the structure, making it a good fit for lower fragmentation levels.

55. What are statistics, and why do DBAs need to keep them updated?

Statistics are summaries of data distribution that the query optimizer relies on to estimate row counts and choose efficient execution plans. If statistics go stale (common after large data loads or deletes), the optimizer’s estimates get inaccurate, often resulting in poor plan choices — this is why routine statistics maintenance is such a standard part of DBA housekeeping.

56. What is the “auto update statistics” and “auto create statistics” database setting?

These are database-level options that let SQL Server automatically create missing single-column statistics and refresh existing statistics once enough of the underlying data has changed. Leaving these on is the sensible default for most workloads, though very large tables sometimes benefit from more controlled, manual statistics maintenance instead.

57. How do you move a database from one server to another with minimal downtime?

Common approaches include: backup and restore (simple but requires a downtime window for the final log backup/restore), log shipping the database ahead of time and doing a final short log backup/restore at cutover, or using Always On Availability Groups / database mirroring to get the new server fully synced beforehand, then failing over with only a very brief interruption.

58. What is the purpose of the sp_who2 or sys.dm_exec_sessions commands?

Both give you visibility into currently connected sessions — who’s connected, from where, what database they’re using, and (with sp_who2) whether they’re blocked and by whom. sys.dm_exec_sessions is the modern, more detailed DMV-based equivalent, and it’s often combined with sys.dm_exec_requests for a fuller picture of what’s actively running.

59. What is SQL Server’s Buffer Pool, and why does memory configuration matter?

The buffer pool is the in-memory cache holding recently used data pages, so SQL Server can avoid expensive physical disk reads whenever possible. Setting max server memory appropriately (leaving enough for the OS and other processes) is one of the most fundamental and impactful configuration tasks a DBA performs, since an under-configured buffer pool leads directly to excessive physical I/O.

60. What happens if you don’t set “max server memory,” and why is that a problem?

By default, SQL Server will happily consume nearly all available system memory for its buffer pool, potentially starving the operating system and any other applications running on the same box of the memory they need — which can lead to instability or even OS-level memory pressure symptoms that are confusing to diagnose if you don’t know SQL Server is the culprit.

61. What is the difference between a hot backup and a cold backup?

A hot (online) backup is taken while the database is still up and actively being used — this is how SQL Server backups normally work. A cold backup requires the database (or server) to be offline/shut down during the backup, which is uncommon in modern SQL Server environments but still relevant conceptually, especially for file-system-level backup approaches.

62. What is a differential base, and why does it matter for differential backups?

The differential base is the specific full backup that a differential backup measures its “changes since” against. If you take a new full backup, all subsequent differentials measure against that new full backup — so you always need the correct full backup paired with your most recent differential during a restore, or the differential simply won’t apply correctly.

63. How would you shrink a transaction log file safely, and why is doing it routinely a bad idea?

You’d typically back up the log first (to truncate it, freeing internal space), then use DBCC SHRINKFILE. Doing this routinely as a “maintenance task” is discouraged because the log will just grow again to accommodate normal activity, and repeated shrink/grow cycles cause file fragmentation and are simply wasted, disruptive effort — it’s best used as an occasional corrective action, not a habit.

64. What causes a transaction log to grow unexpectedly large, and how do you diagnose it?

Common causes include a long-running open transaction, a database in full recovery model without regular log backups, or high-volume operations like bulk loads or index rebuilds generating heavy log activity. Querying sys.databases for log_reuse_wait_desc tells you exactly why SQL Server currently can’t reuse/truncate the log space — that field is usually your fastest answer.

65. What is the purpose of sp_configure, and can you give an example setting a DBA might change?

sp_configure lets you view and change server-wide configuration options, such as max degree of parallelism, cost threshold for parallelism, or max server memory. Many of these settings require running RECONFIGURE (and sometimes a restart) afterward to actually take effect.

66. What is “max degree of parallelism” (MAXDOP), and why is the default not always ideal?

MAXDOP controls the maximum number of CPU cores a single query can use when SQL Server decides to run it in parallel. The out-of-the-box default (0, meaning “use all available cores”) can lead to excessive parallelism overhead on modern multi-core, multi-socket servers, which is why Microsoft’s current guidance recommends a more conservative value based on the number of cores per NUMA node.

67. What is “cost threshold for parallelism,” and how does it relate to MAXDOP?

This setting defines the minimum estimated cost a query plan must have before SQL Server will even consider going parallel at all. The default of 5 is quite old and low by modern hardware standards, so many DBAs raise it (commonly to 25–50) to prevent smaller, cheaper queries from unnecessarily consuming multiple cores.

68. What is the difference between a full-text index and a regular index?

A regular index helps with exact-match or range lookups on structured column values. A full-text index is specifically designed for searching large blocks of text for words and phrases — supporting things like “contains any form of the word running” — which a standard B-tree index simply isn’t built to handle efficiently.

69. What is Change Data Capture (CDC), and what’s it typically used for?

CDC tracks insert, update, and delete activity on specified tables and makes that change history available in queryable change tables — commonly used to feed data warehouses or ETL pipelines incrementally, without having to reprocess entire tables each time.

70. What is the difference between CDC and Change Tracking?

Change Tracking is lighter-weight — it just tells you which rows changed, without capturing the actual historical values or the specific DML operations in detail. CDC is heavier but far more informative, capturing the actual before/after data and operation type, making it suitable for more detailed downstream processing needs.

ADVANCED LEVEL (Q71–Q100)

71. Explain how you would design a high-availability and disaster recovery strategy for a mission-critical database.

I’d start by clarifying the actual business RPO/RTO requirements, since that drives everything else. Typically that means an Always On Availability Group with a synchronous-commit replica in the same datacenter for automatic local failover (zero/near-zero data loss), plus an asynchronous-commit replica in a geographically separate region for disaster recovery. I’d pair that with a solid backup strategy independent of the AG (since AGs protect against server failure, not against corruption or accidental data deletion), regular restore testing, and clearly documented, rehearsed failover runbooks — because untested DR plans tend to fail exactly when you need them most.

72. What is a distributed availability group, and when would you use one?

It’s an Availability Group made up of two separate, independent Availability Groups (each with its own WSFC cluster), linked together — often used to span across data centers or even across on-premises and Azure, without requiring a single stretched Windows cluster. It’s particularly useful for cross-cluster or cross-domain scenarios where a single traditional AG topology wouldn’t work.

73. What is a read-only routing list in an Availability Group, and how does it work?

It’s a configured list on each replica specifying where read-intent connection requests should actually be routed when a client connects with ApplicationIntent=ReadOnly. This is what makes it possible to automatically offload reporting or read-heavy workloads onto secondary replicas without the application needing to hardcode a specific server name.

74. Walk through how SQL Server actually processes a transaction using the transaction log (write-ahead logging).

SQL Server follows the write-ahead logging (WAL) protocol: before any data modification is written to the actual data pages on disk, the corresponding log record describing that change must first be hardened (written) to the transaction log. This guarantees that even if the server crashes right after a commit, SQL Server can replay the log during crash recovery to redo committed changes that hadn’t yet made it to the data file, and roll back any uncommitted ones.

75. What is a corrupted database, and what are your options for recovery beyond just restoring a backup?

If a good, recent backup exists, that’s almost always the safest and fastest path. If not, DBCC CHECKDB with a repair option (like REPAIR_ALLOW_DATA_LOSS) is a last resort — it can fix some corruption, but as the option name honestly admits, it may involve losing some data, since it sometimes has to deallocate unreadable pages entirely. For serious cases, especially involving specific pages, restoring just those pages from a backup (page-level restore) while the rest of the database stays online is often a better middle ground.

76. What is page-level restore, and when is it useful?

It lets you restore just the specific corrupted data page(s) from a backup, rather than the entire database, while the rest of the database can remain online and available (in Enterprise edition, at least). It’s extremely useful for large databases where a full restore would mean unacceptable downtime, but only a tiny, isolated portion of data is actually affected by corruption.

77. What is the “torn page” and “checksum” page verification, and what’s the difference?

These are database-level settings controlling how SQL Server detects storage-level corruption. Torn page detection is an older, lighter-weight method using a bit pattern to catch incomplete (torn) writes. Checksum (the modern default) calculates and stores a full checksum for each page and re-verifies it on every read, catching a wider range of corruption issues, not just torn writes.

78. Explain the internal steps SQL Server takes during crash recovery.

Crash recovery happens in three phases: the Analysis phase scans the log to determine which transactions were active (uncommitted) and which pages were dirty at the time of the crash; the Redo phase replays logged changes forward to bring data pages up to the state they should have been in, including for transactions that eventually committed; and the Undo phase rolls back any transactions that were still in progress and never committed, restoring the database to a consistent state.

79. What is “Instant File Initialization,” and why does it matter for restore and growth performance?

Normally, when SQL Server creates or grows a data file, Windows zeroes out every byte of that new space before SQL Server can use it, which takes real time for large files. Instant File Initialization (enabled by granting the SQL Server service account the “Perform Volume Maintenance Tasks” permission) skips that zeroing step for data files, dramatically speeding up file creation, growth, and restores — though for security reasons it doesn’t apply to log files, which always get zeroed.

80. What is NUMA, and why does it matter for SQL Server configuration on large servers?

NUMA (Non-Uniform Memory Access) is a hardware architecture where memory is divided into regions, each more “local” (faster to access) to a particular group of CPU cores than to others. SQL Server is NUMA-aware and tries to schedule work and allocate memory intelligently within each node, but misconfigured settings (like an overly high MAXDOP crossing NUMA boundaries unnecessarily) can lead to more expensive cross-node memory access and reduced performance on large multi-socket servers.

81. What is Resource Governor, and what problem does it solve?

Resource Governor lets you define resource pools and workload groups to cap or guarantee CPU, memory, and I/O usage for different categories of workload on the same instance — for example, preventing a handful of runaway ad-hoc reporting queries from starving your critical OLTP workload of resources. It’s a way to enforce fairness and predictability on a shared instance without needing physically separate servers.

82. What is Transparent Data Encryption (TDE), and what exactly does it protect against?

TDE encrypts the actual data and log files at rest, on disk, so that if someone steals the physical files (or a backup file) without the corresponding certificate/key, they can’t read the data. Importantly, it does not encrypt data in transit over the network, nor does it protect against someone with legitimate query access to the running database — for those concerns you’d look at Always Encrypted and TLS/SSL connection encryption respectively.

83. What is Always Encrypted, and how is it different from TDE?

Always Encrypted encrypts specific sensitive columns at the client/application level, meaning the data stays encrypted even while it’s in memory inside SQL Server and even from DBAs querying it directly — only an application with the correct key can decrypt and see the plaintext values. This is fundamentally different from TDE, which protects data at rest but leaves it fully readable to anyone with legitimate database access.

84. What is a Filtered Index, and give a practical scenario where you’d use one?

A filtered index only includes rows that match a specified WHERE condition, rather than indexing the entire table — making it smaller, faster to maintain, and more efficient for queries targeting that specific subset. A classic example is indexing only the “Active” or non-NULL rows in a status/soft-delete column, when the vast majority of queries only ever care about active records anyway.

85. Explain how the Query Optimizer decides on parallelism, and how you’d investigate excessive/insufficient parallelism issues.

The optimizer estimates the cost of a serial plan, and if that estimated cost exceeds the configured “cost threshold for parallelism,” it considers parallel plan alternatives, capped by MAXDOP. To investigate real-world issues, I’d look at wait stats like CXPACKET/CXCONSUMER (which point to parallelism-related waits), review actual execution plans for the parallelism icon, and consider whether MAXDOP and cost threshold settings are appropriately tuned for the hardware and workload mix rather than left at old defaults.

86. What is the “Query Store,” and how would you use it to resolve a plan regression across a deployment?

Query Store persists historical query text, plans, and runtime metrics directly in the database, letting you compare performance before and after a change (like a deployment or statistics update) using its built-in regression reports. If you find the previous plan was genuinely better, you can force that specific plan directly through Query Store as an immediate fix while investigating and addressing the actual root cause.

87. How would you approach patching/upgrading a highly available production SQL Server environment with minimal downtime?

With an Availability Group (or failover cluster) in place, you can patch secondary replicas first, fail over to a freshly patched replica, then patch what was previously the primary — achieving the update with just a brief failover interruption rather than extended downtime. This requires careful planning around version/build compatibility during the transition window, plus thorough pre-testing in a non-production environment first.

88. What is the difference between a Failover Cluster Instance (FCI) and an Availability Group?

An FCI provides instance-level high availability using shared storage — if the active node fails, another node takes over the entire instance, using the same underlying disks. An Availability Group operates at the database level with separate, independent storage for each replica, using data replication instead of shared storage, and additionally offers readable secondaries, which an FCI does not.

89. What is a quorum in a Windows Server Failover Cluster context, and why does it matter for Always On?

Quorum is the mechanism that determines how many nodes/votes need to agree for the cluster to remain operational and avoid a dangerous “split-brain” scenario where multiple nodes might independently believe they should be active. Getting quorum configuration right (including appropriate use of a file share or cloud witness for even-numbered node counts) is critical, because a poorly configured quorum can cause unnecessary, unwanted failovers or, worse, prevent a legitimate failover from happening at all.

90. What is the “Extended Events” (XEvents) framework, and how does it compare to SQL Server Profiler/Trace?

Extended Events is the modern, lightweight event-capturing framework that replaced the older Profiler/Trace mechanism (which is deprecated). It has a much smaller performance footprint on the server, offers far more granular event and filter options, and can capture data even for very high-frequency events without noticeably degrading server performance — a real concern that classic Profiler traces often had.

91. How would you troubleshoot a database that’s suddenly consuming far more CPU than usual, with no obvious code changes?

I’d check whether a specific query’s plan has changed (via Query Store regression detection or comparing cached plans), since parameter sniffing or a statistics update causing a plan flip is a very common silent cause. I’d also check for a recent statistics update, an index that was dropped or disabled, a change in data volume/distribution, or even a change in server-level settings like MAXDOP — CPU spikes without application changes are almost always something shifted underneath the query, not the query text itself.

92. What is “Query Store’s Automatic Plan Correction,” and how does it differ from manually forcing a plan?

Automatic Plan Correction (part of Automatic Tuning) actively monitors for plan regressions and, if it detects a new plan is significantly worse than a previous one, can automatically force the better-performing prior plan on its own — self-healing without DBA intervention. Manually forcing a plan requires you to identify the regression and take action yourself; automatic correction is essentially that same safety net running continuously and proactively.

93. Explain the concept of “poison waits” and give an example.

Poison waits are specific wait types that, when they appear at all (even briefly), point to a serious, often storage-related, underlying problem rather than just normal contention — a classic example is IO_ATTACH issues or unusually long WRITELOG/PAGEIOLATCH waits, which can indicate the storage subsystem itself is struggling, not just a query design issue. Recognizing these as “the storage layer is unhealthy” signals rather than “let’s tune this specific query” is an important, experience-based distinction.

94. What is “soft-NUMA,” and why might a DBA configure it manually?

Soft-NUMA lets you manually subdivide physical NUMA nodes (or a system with no real hardware NUMA at all) into smaller logical nodes within SQL Server’s configuration, primarily to reduce the workload handled by a single I/O completion/lazy writer thread group, improving scalability on servers with a very high core count per physical node.

95. How would you design a backup strategy for a multi-terabyte, mission-critical database with tight RPO requirements?

I’d combine weekly (or more frequent) full backups, more frequent differentials to keep individual restore chains shorter, and very frequent transaction log backups (potentially every few minutes) to satisfy a tight RPO. For a database of that size, I’d also strongly consider backup compression to reduce time and storage footprint, and possibly file/filegroup backups combined with piecemeal restore strategies if certain filegroups (like historical, read-only data) don’t need to be backed up as frequently as active ones.

96. What is a “Filegroup,” and how can strategic filegroup design help with very large databases (VLDBs)?

A filegroup is a logical grouping of one or more physical data files that you can place objects (tables, indexes, partitions) into. Strategic use of filegroups lets you spread I/O across multiple disks, back up and restore subsets of a huge database independently (piecemeal restore), and mark older, rarely-changing filegroups as read-only to skip them in regular backup routines entirely — all valuable techniques once a database grows past comfortable single-file management.

97. What is table partitioning, and what problem does it solve at scale?

Partitioning splits a single logical table into multiple physical storage units based on a partitioning column (commonly a date), while the table still appears and behaves as one object to queries. This helps enormously with very large tables by enabling much faster data loading/archiving (via partition switching, which is essentially a metadata-only operation), improved maintenance (rebuilding just one partition’s index instead of the whole table), and sometimes better query performance through partition elimination.

98. Explain “partition switching,” and why is it so much faster than a regular DELETE or INSERT for archiving data?

Partition switching moves an entire partition’s worth of data in or out of a partitioned table by simply repointing metadata — the actual data pages never move or get individually processed at all. This makes archiving (or loading) potentially millions of rows a near-instantaneous, minimally-logged operation, compared to a traditional row-by-row DELETE or INSERT which has to process, log, and lock every single row involved.

99. What are some key considerations when migrating a large on-premises SQL Server workload to Azure (SQL VM, Managed Instance, or Azure SQL Database)?

It really depends on which target you pick: Azure SQL Database offers the least admin overhead but has feature/compatibility limitations and a different backup/HA model entirely managed for you; Managed Instance gives near-full SQL Server compatibility with much less admin burden than a VM; and a SQL Server VM gives full control (and full admin responsibility) similar to on-prem. Beyond picking the target, I’d think carefully about network latency and bandwidth for the migration itself, compatibility-level and feature-parity testing, licensing (Azure Hybrid Benefit can meaningfully reduce cost if eligible), and validating the actual cutover/rollback plan well before the real migration night.

100. Looking back on major production incidents, what’s a lesson learned that changed how you approach SQL Server administration afterward?

A recurring lesson across the industry (and worth reflecting on honestly in an interview) is that the incidents that hurt the most are rarely about a single missing skill — they’re about a small gap in process: a backup that was running but never test-restored, an alert that was configured but routed to an inbox nobody actually watched, or a change made in production without a documented rollback plan. The technical skills matter, but building genuinely reliable habits and verification steps around them is often what actually prevents the next 2 AM page.