Back to interview questions

100+ MS SQL Interview Questions and Answers

100+ MS SQL Interview Questions and Answers

Explore 120 MS SQL interview questions and answers on SQL Server administration, performance, security, backups, recovery and high availability.

Explore 120 MS SQL interview questions and answers on SQL Server administration, performance, security, backups, recovery and high availability.

By

Team Sherlock.sh

Add Sherlock as a preferred source on Google

MS SQL interviews should reveal whether a candidate can operate Microsoft SQL Server reliably, diagnose a problem from evidence and recover the service when a component fails. Knowing query syntax is only part of that responsibility. Recruiters should listen for how candidates reason about storage, permissions, backups, monitoring and the dependencies that keep an application available.

These 100+ MS SQL Interview Questions and Answers cover 120 questions across beginner, intermediate and experienced levels. Here, MS SQL refers to the Microsoft SQL Server platform and Database Engine. The questions focus on administration, performance, security, recovery and availability, progressing from core architecture to operational decisions involving multiple systems and competing constraints.

How can recruiters distinguish practical SQL Server experience from a list of familiar administrative commands?

Give the candidate a concrete symptom, such as a growing transaction log, a failed restore or a sudden latency increase. Ask what evidence they would collect first, how they would distinguish competing causes and what would confirm that a proposed change worked. Strong candidates connect the database state to application impact and explain the recovery path before making a consequential change.

Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation and unauthorized assistance during remote interviews. Combine those signals with diagnostic walkthroughs and follow-up questions about recovery dependencies and operational trade-offs. Use them as context for a fair review alongside demonstrated understanding, rather than as conclusive proof of misconduct.

MS SQL Interview Questions

Choose questions that reflect the role’s platform responsibilities and deployment environment. Supply the engine version, edition and relevant symptoms, and ask candidates to explain what evidence supports their next step. The complete question bank appears first, followed by identically numbered answers.

MS SQL Interview Questions for Beginners

Check whether the candidate understands the instance, database, storage and security boundaries they would administer. Ask how a basic configuration choice affects availability, recovery or ongoing maintenance.

  1. How does the SQL Server Database Engine differ from SQL, T-SQL and management tools?

  2. What is the difference between a SQL Server instance and a database?

  3. How do default and named instances affect connection troubleshooting?

  4. What edition differences should a beginner know for SQL Server 2022?

  5. Why should a SQL Server deployment plan distinguish Windows from Linux?

  6. What roles do SSMS and sqlcmd play in administration?

  7. What are master, model, msdb and tempdb used for?

  8. How do SQL Server data files and log files differ?

  9. What are pages and extents in SQL Server storage?

  10. What does a filegroup control?

  11. Why does SQL Server need a transaction log, and is it an audit log?

  12. How are the buffer pool, dirty pages and checkpoints related?

  13. How do simple, full and bulk-logged recovery models differ?

  14. What does a full database backup contain?

  15. How does a differential backup differ from an incremental row export?

  16. What does log truncation mean, and does a log backup shrink the LDF?

  17. What do RPO and RTO mean for a SQL Server backup plan?

  18. Why is a successful backup job not enough to prove recoverability?

  19. What is the difference between a login and a database user?

  20. What does Windows authentication versus mixed mode mean?

  21. How should server roles and database roles support least privilege?

  22. How do schemas help organize ownership and permissions?

  23. Why do SQL Server service-account permissions matter?

  24. What are the main parts of a SQL Server Agent job?

  25. Which log should you inspect for an engine problem versus a failed job?

  26. Why should administrators inventory instance, database and tempdb collations?

  27. How does database compatibility level differ from engine version?

  28. How would you identify the installed SQL Server build and edition?

  29. What kinds of free space should be checked before a database grows?

  30. Why are predictable autogrowth settings preferable to leaving capacity to chance?

  31. Why is routine database shrinking usually a poor maintenance habit?

  32. How do heaps, clustered indexes and nonclustered indexes differ physically?

  33. Why are statistics important to SQL Server’s optimizer?

  34. How do estimated and actual execution plans differ operationally?

  35. What are dynamic management views, and what limits their interpretation?

  36. How does Query Store help investigate performance changes?

  37. What can wait statistics tell you that CPU utilization cannot?

  38. How does blocking differ from a deadlock?

  39. How should SQL Server backups be protected outside the database?

  40. What should a basic SQL Server monitoring baseline include?

MS SQL Interview Questions for Intermediate

Explore how the candidate diagnoses recurring operational problems and maintains a database safely. Restore sequences, resource pressure and diagnostic evidence reveal whether the candidate can connect symptoms to engine behavior.

  1. How do you plan a full, differential and log restore sequence?

  2. What must be verified before using STOPAT for point-in-time recovery?

  3. When is a tail-log backup feasible after a database failure?

  4. Why use a copy-only full backup for an ad hoc database copy?

  5. What different confidence do backup checksums, VERIFYONLY and a restore rehearsal provide?

  6. What must accompany an encrypted backup to make disaster recovery possible?

  7. How do you diagnose a transaction log that keeps growing?

  8. How should VLF count and autogrowth influence log sizing?

  9. How do you decide whether tempdb needs more data files?

  10. How can row-versioning workloads create storage pressure, and where should you look?

  11. How would you choose max server memory without relying on a fixed percentage?

  12. How do MAXDOP and cost threshold for parallelism serve different tuning purposes?

  13. When should statistics maintenance be targeted rather than updating everything with FULLSCAN?

  14. How do index rebuild and reorganize differ in their effect on statistics?

  15. Why should index maintenance consider page density as well as fragmentation?

  16. How do included columns change the cost-benefit analysis of a covering index?

  17. Why might SQL Server not use a filtered index that appears to match a query?

  18. How do columnstore rowgroup states help explain disappointing analytical performance?

  19. Why does partitioning a large table not automatically make queries faster?

  20. How would you evaluate row or page compression for a production table?

  21. How would you investigate an implicit-conversion warning in a slow plan?

  22. How do estimated-versus-actual row discrepancies guide plan diagnosis?

  23. How do you distinguish a memory-grant wait from a spill?

  24. Why should wait statistics be compared over an interval rather than ranked once?

  25. How can a sleeping session still be the head of a blocking chain?

  26. How would you read a deadlock graph captured by Extended Events?

  27. Why prefer a targeted Extended Events session over a broad Profiler trace?

  28. How should Query Store plan forcing be used during a regression?

  29. How do SQL Server Agent proxies differ from the Agent service account?

  30. What should you verify before enabling automatic retries on a SQL Server Agent job step?

  31. How do you verify that a failed Agent job will actually notify an operator?

  32. How would you choose a CHECKDB schedule for a large database?

  33. What should be the first response to a confirmed corruption report?

  34. What does TDE protect, and which keys matter during recovery?

  35. When does Always Encrypted address a threat that TDE does not?

  36. Why is encrypting a SQL connection insufficient without certificate validation?

  37. How do you design a SQL Server Audit configuration that produces useful evidence?

  38. How do row-level security and dynamic data masking enforce different boundaries?

  39. How do contained database users improve portability, and what still needs migration?

  40. Why can a restored database user be orphaned even when a login with the same name exists?

MS SQL Interview Questions for Experienced

Probe decisions that span replicas, instances, storage and recovery processes. Ask the candidate to identify failure boundaries, quantify constraints and explain how they would validate a migration or incident response.

  1. How would you choose between an availability group and a failover cluster instance?

  2. What trade-off does synchronous commit make between latency and recovery point?

  3. What evidence is needed before a planned or forced AG failover?

  4. Why can an AG listener failover succeed while clients still cannot reconnect?

  5. What consistency limits apply when routing reads to an AG secondary?

  6. Why does an AG backup preference not guarantee that backups run on the preferred replica?

  7. What shared dependencies can defeat an FCI’s node redundancy?

  8. How does quorum affect availability during a cluster network partition?

  9. How would you assess log shipping readiness for a disaster?

  10. Why is transactional replication not a complete disaster-recovery plan?

  11. What makes a distributed AG useful but delicate during a version migration?

  12. How does database-level AG health detection differ from instance health monitoring?

  13. What belongs in an instance recovery runbook for system databases?

  14. Why is restoring master more than copying a backup file onto a replacement server?

  15. What is lost when msdb is unavailable, and what can still be recovered?

  16. How can piecemeal restore recover critical filegroups before the whole database?

  17. What limits apply when restoring databases across SQL Server versions?

  18. How do in-place and side-by-side upgrades change rollback planning?

  19. How would you stage a compatibility-level change after an engine upgrade?

  20. How would you roll out a cumulative update across an HA topology?

  21. How does Accelerated Database Recovery change operational capacity planning?

  22. How can a tighter indirect-checkpoint target affect a write-heavy workload?

  23. What does instant file initialization accelerate, and what are its limits?

  24. How would you investigate storage latency without relying on a universal threshold?

  25. How do NUMA and runnable queues affect CPU-pressure diagnosis?

  26. Why can SQL Server run out of worker threads while CPU is not fully busy?

  27. How does memory grant feedback change repeated-query resource use?

  28. When should OPTIMIZE_FOR_SEQUENTIAL_KEY be tested for insert contention?

  29. When should memory-optimized tempdb metadata be considered?

  30. What can Resource Governor isolate, and what remains shared?

  31. What operational costs remain when tables are memory-optimized?

  32. How would you diagnose automatic AG seeding that does not complete?

  33. What must a SQL Server ledger verification design protect outside the database?

  34. How can certificate-signed modules support a least-privilege operational workflow?

  35. How would you detect configuration drift across SQL Server instances?

  36. How would you evaluate noisy-neighbor isolation for multiple tenants?

  37. How do physical throughput limits shape a very large database maintenance plan?

  38. What durability trade-off does delayed durability introduce?

  39. How would you balance incident evidence collection with urgent mitigation?

  40. What evidence is needed before decommissioning a SQL Server instance?

MS SQL Interview Questions and Answers

The baseline is SQL Server 2022 Database Engine unless an answer states a different version. Edition, cumulative update, operating system and deployment topology can change feature availability or behavior. Azure SQL Database and Azure SQL Managed Instance have separate service boundaries; examples about a self-managed SQL Server instance should not be transferred to them without checking those differences.

MS SQL Interview Questions and Answers for Beginners

These answers explain SQL Server’s core platform concepts and their practical consequences. They connect files, services, backups and permissions to the administrative tasks a new team member needs to understand.

1. How does the SQL Server Database Engine differ from SQL, T-SQL and management tools?

The Database Engine is the service that stores data, executes requests, enforces permissions and recovers databases. SQL is a language family; T-SQL is Microsoft’s implementation and extension. SSMS and sqlcmd are clients that send requests to an engine. Installing a client does not install a database server.

For example, an administrator can use SSMS on a laptop to manage a SQL Server instance on another machine. If SSMS closes, the remote engine and its databases continue running. In an interview, distinguish a connection-tool problem from a stopped service or unavailable database before proposing a fix.

2. What is the difference between a SQL Server instance and a database?

An instance is an independently configured Database Engine service. A database is a managed collection of objects, data files and transaction-log files within that instance. One instance can host many databases, while a host can run multiple supported instances with their own configuration and security principals.

Two application databases on one instance share engine resources such as CPU and the buffer pool. They are not isolated like two separate machines. A restart of that instance affects both, whereas taking one database offline need not stop the other. This distinction matters when discussing maintenance scope and competing workloads.

3. How do default and named instances affect connection troubleshooting?

On Windows, a default instance is normally addressed by its host name, while a named instance is commonly addressed as host\instance. The name identifies an engine installation; it does not tell you which database the application needs. TCP connections ultimately require the correct host and listening port.

Do not assume every instance uses port 1433. A named instance may use a dynamic or fixed port, and name resolution can involve SQL Server Browser. An explicit tcp:dbhost,51433 endpoint bypasses instance-to-port discovery, but still requires a listening service, firewall access and valid authentication. On Linux, plan connections around the configured engine endpoint rather than Windows named-instance installation conventions.

4. What edition differences should a beginner know for SQL Server 2022?

Edition affects available features, resource limits and permitted use. SQL Server 2022 Developer provides Enterprise functionality for development and testing, but is not licensed for production. Express can serve small production workloads within its limits; Standard and Enterprise require an appropriate licensing arrangement.

For the 2022 release specifically, Express limits a relational database to 10 GB, its engine compute to the lesser of one socket or four cores, and its buffer pool to 1,410 MB per instance. That memory figure is not a blanket cap on every byte the process can allocate. SQL Server Agent is absent from Express, so a backup schedule needs another supported scheduler. Check the exact release’s edition matrix before carrying these numbers into an upgrade decision.

5. Why should a SQL Server deployment plan distinguish Windows from Linux?

The Database Engine is available on both platforms, but operating-system integration and supported components differ. A database workload using ordinary engine features may transfer readily, while a deployment depending on Windows-specific services, paths or authentication setup needs additional review.

For example, SQL Server 2022 on Linux does not provide every feature in the Windows feature matrix; FileTable and FILESTREAM are among the exclusions. File paths, service management and account permissions also change. Inventory the application’s actual dependencies and validate them against the Linux support matrix instead of treating successful database restoration as proof that the whole application is portable.

6. What roles do SSMS and sqlcmd play in administration?

SSMS provides a graphical management environment, query editor and diagnostic views. sqlcmd is a command-line client useful for repeatable scripted requests. Both connect to a Database Engine; neither replaces the service, its permissions or its backup strategy.

A script that works in an interactive SSMS session may fail under a scheduled account because the login, default database or client options differ. Record the intended server, database and authentication context, and check the client variant and version. Avoid placing credentials directly in shared command histories. Closing the client does not cancel every kind of server-side job that the client previously started.

7. What are master, model, msdb and tempdb used for?

These system databases support the instance rather than an individual application. Their responsibilities explain why backing up only user databases may leave an incomplete recovery plan. In particular, application data restoration does not automatically recreate every login or scheduled job.

For example, rebuilding an instance and restoring Sales may recover orders while leaving its nightly job missing because that job’s metadata belonged to msdb. Protect the relevant system databases and maintain scripts for instance configuration. tempdb is recreated when the engine starts and is not restored from a user backup.

Database

Operational responsibility

master

Instance metadata, including database registration and server principals

model

Template settings and objects for newly created databases

msdb

Agent jobs, schedules and backup/restore history

tempdb

Temporary objects and engine work areas; recreated at startup

8. How do SQL Server data files and log files differ?

Data files hold database pages containing table and index data and other structures. Transaction-log files hold the log records needed for transactional recovery. A database has a primary data file and at least one log file; it may also have secondary data files. The conventional extensions are .mdf, .ndf and .ldf, respectively.

A logical file name is the name SQL Server uses in management statements; a physical file name is its operating-system path. They are different identifiers. Never delete an LDF because it looks large or copy active MDF/LDF files as an improvised backup. Use supported backup or file-management procedures and account for service-account permissions at the destination.

9. What are pages and extents in SQL Server storage?

For ordinary disk-based rowstore storage, a page is an 8 KB unit and an extent contains eight contiguous pages, totaling 64 KB. Pages have headers and different purposes, so 8 KB does not mean 8 KB of user-row payload. Allocation structures track which pages and extents belong to database objects.

This helps explain why inserting a tiny row does not increase a file by exactly that row’s byte length: SQL Server uses space already allocated inside pages and allocates more storage as needed. Likewise, deleting rows does not automatically make the operating-system file smaller. These units describe rowstore storage; do not assume every specialized storage feature has identical layout rules.

10. What does a filegroup control?

A filegroup is a logical grouping of data files used for allocation and administration. A table or index can be placed on a selected filegroup, whose files provide its storage. Transaction-log files do not belong to filegroups.

An administrator might place a large archive on its own filegroup to support a deliberate storage or backup design. Merely adding two files on the same physical device does not create two independent I/O resources. Filegroups add management choices, but also require clear naming, capacity planning and recovery procedures; they are not a universal performance switch.

11. Why does SQL Server need a transaction log, and is it an audit log?

The transaction log supports rollback and recovery. SQL Server follows write-ahead logging: relevant log records reach durable storage before the corresponding dirty data pages are written. For normally durable commits, the commit’s log records are hardened before success is acknowledged; delayed durability deliberately changes that guarantee.

The log is not a convenient business audit trail with an indefinitely retained record of who changed every field. Its reusable space and recovery-oriented records serve another purpose. A payment system that needs an attributable change history should design auditing explicitly, while still protecting its transaction log and backup chain.

12. How are the buffer pool, dirty pages and checkpoints related?

The buffer pool caches database pages in memory. A changed cached page is dirty because its current contents differ from the on-disk page. Checkpoint processing writes dirty pages as part of limiting recovery work, while the transaction log preserves the ordering required for recovery.

A committed change can therefore remain in a dirty memory page while already being durable through the log. Conversely, writing a page does not mean every transaction affecting it committed; recovery can undo incomplete work. Avoid explaining checkpoint as “commit everything” or as a backup. It is an engine recovery mechanism, not a substitute for recoverable backup copies.

13. How do simple, full and bulk-logged recovery models differ?

A recovery model controls logging and recovery options. Simple allows inactive log space to become reusable without log backups, but still logs transactions and can still experience log growth. Full supports log backups and point-in-time recovery when the required backup chain is intact. Bulk-logged permits minimal logging for eligible bulk operations and has recovery restrictions.

Choose from the recovery requirement rather than the model’s name. A nightly rebuildable staging database may tolerate recovery only to a data backup. Orders entered throughout the day may require full recovery with frequent successful log backups. Selecting full alone does not establish a usable backup chain or prevent data loss; the backup and restore procedures must support it.

14. What does a full database backup contain?

A full database backup contains the database’s backed-up data plus enough transaction log to recover that backup consistently. It is not a raw copy of only the rows committed when the command began. SQL Server supports normal database activity during the backup, subject to operation-specific restrictions.

Suppose a full backup finishes overnight and the business needs recovery to mid-afternoon. That full backup is the starting point, not the afternoon recovery point; suitable subsequent differential and log backups may be needed. A full backup also does not replace the separate log-backup schedule under full recovery. Keep the backup type, finish time and intended restore sequence clear.

15. How does a differential backup differ from an incremental row export?

A differential database backup contains changed extents since its differential base, normally the most recent conventional full database backup. It is cumulative relative to that base, not relative to the previous differential, and it works at storage-allocation granularity rather than exporting only changed business rows.

If Monday’s full backup is the base, Wednesday’s differential includes relevant changes since Monday even if Tuesday also had a differential. A restore typically needs the base full and the selected differential, not every intervening differential. A copy-only full does not establish a new differential base. Track this relationship so an ad hoc backup does not create an unexpected restore dependency.

16. What does log truncation mean, and does a log backup shrink the LDF?

Log truncation makes inactive log space eligible for reuse; it does not reduce the physical log file’s size. Under full recovery, successful log backups are normally part of making space reusable, but active transactions and other retention requirements can still hold up reuse.

A 20 GB LDF can remain 20 GB after a successful log backup while having much more reusable space inside it. That is often desirable because the next workload can reuse the allocation. If it keeps growing, investigate the reuse wait and workload instead of repeatedly shrinking it. A log backup is a recovery artifact, not a command to reclaim disk space immediately.

17. What do RPO and RTO mean for a SQL Server backup plan?

Recovery point objective, or RPO, is the tolerated loss of recent data expressed as time. Recovery time objective, or RTO, is the target time to restore the required service. They describe business needs, not guarantees automatically provided by a backup schedule.

If the last usable backup recovers through 10:15 and a failure happens at 10:22, seven minutes of changes may be missing unless another recovery source is available. A five-minute RPO would not be met. If restoring takes 25 minutes but validation and reconnecting the application take another 20, the service recovery is 45 minutes, not 25. Include those steps when testing the RTO.

18. Why is a successful backup job not enough to prove recoverability?

A completed job shows that the configured operation reported success. It does not prove the complete restore chain is available, encryption keys are accessible, the destination has enough space or the application can operate after recovery. Backup verification is useful but narrower than a recovery rehearsal.

Restore representative backups to an isolated environment, run appropriate database consistency checks, and validate a small set of application-level expectations. Record the recovered point and elapsed service-recovery time. RESTORE VERIFYONLY checks backup readability and completeness within its documented scope; it does not perform a full database restore or establish that all logical data structures and application behavior are correct.

19. What is the difference between a login and a database user?

A login is an instance-level principal used to establish access to the server. A database user is a database-level principal through which permissions are granted inside a database. In the common mapped model, a login can map to users in several databases with different permissions.

After moving a database, a user may exist in the restored database while the corresponding login is missing or has a different security identifier. Matching names alone do not guarantee the mapping works. Contained database users are an important exception to the usual login-mapping model, so first identify the authentication design rather than assuming every user needs a new server login.

20. What does Windows authentication versus mixed mode mean?

On a conventional SQL Server Windows deployment, Windows authentication uses a Windows identity, while mixed mode also permits SQL Server logins with credentials managed by SQL Server. Authentication identifies the principal; authorization still determines what that principal can do. Enabling mixed mode does not itself grant application permissions.

For an organization with managed domain identities, integrated authentication can reduce separately maintained passwords. A SQL-authenticated application account may still be appropriate where supported by the architecture, but should have a narrow role and managed credentials. Linux integrated authentication requires its documented setup; do not assume the Windows installation experience transfers unchanged.

21. How should server roles and database roles support least privilege?

Server roles group permissions at instance scope, while database roles group permissions within a database. Give an application or operator the permissions needed for its task rather than broad administrator membership to avoid investigating an access error.

For example, a reporting account may need read access to selected reporting views in one database; sysadmin grants vastly more authority. Even db_datareader can be broader than required because it covers all user tables and views in that database. A custom role with explicit grants can match the intended boundary. Review inherited memberships as well as direct grants when explaining effective access.

22. How do schemas help organize ownership and permissions?

A schema is a named container for database objects and a securable on which permissions can be granted. Schema ownership is separate from an individual object’s name and from the user who originally created it. This lets a team organize objects under a stable boundary as personnel change.

For example, a reporting role can receive access at the Reporting schema level, but that decision can also expose future objects placed there. Do not put sensitive tables in the schema merely because their names sound related. Ownership chaining can affect permission checks, so a schema is a useful permission boundary rather than an independent security sandbox.

23. Why do SQL Server service-account permissions matter?

The Database Engine service runs under an operating-system identity, which needs access to its database files and to destinations used by server-side operations. The person submitting a backup command and the identity writing the backup file are not necessarily the same account.

If an administrator can browse a shared folder but a database backup cannot write there, inspect the service identity’s access to that location, including relevant share and filesystem permissions on Windows. Do not solve the problem by granting blanket operating-system administrator access. Use supported service-account configuration procedures and grant the specific resources required; changing an account casually can also break other integrations.

24. What are the main parts of a SQL Server Agent job?

A job contains one or more steps, with success and failure actions that control its flow. Schedules determine when it runs, and history records execution outcomes. Job ownership, the step subsystem and any proxy configuration influence the execution context.

A nightly import might first load files, then validate row counts, then publish the data only if validation succeeds. A green final status can be misleading if a failed step is configured to continue or report success. Check step outcomes and notifications, not only the schedule definition. Agent must be running, and SQL Server 2022 Express does not include it.

25. Which log should you inspect for an engine problem versus a failed job?

The SQL Server error log records engine events such as startup messages, recovery activity and selected errors. Agent history focuses on job and step execution, while the Agent log records scheduler-service events. On Windows, operating-system event logs add service and host context.

If a backup job fails, begin with the job’s step message and correlate its time with the engine error log and storage events. If Agent never started, a job-history search alone may show nothing useful. Preserve timestamps and account for time zones and retention. The absence of an event in a rotated or limited history is not proof that the event never occurred.

26. Why should administrators inventory instance, database and tempdb collations?

Collation is configured at several scopes. The instance setting influences system databases and defaults for newly created databases, while an existing user database can retain a different collation. Temporary objects commonly involve tempdb, so moving a database between instances can expose differences that were not visible on the original host.

Before a migration, inventory these settings and test the application’s temporary-object and cross-database workflows. Changing a database default does not automatically rewrite every existing column’s collation. Changing the instance collation is also a separate administrative operation, not a routine database-property adjustment. Treat a mismatch as a compatibility requirement to investigate rather than changing all collations without understanding the data.

27. How does database compatibility level differ from engine version?

Engine version identifies the installed SQL Server software. Database compatibility level controls selected database behaviors, including optimizer behavior associated with a release. A SQL Server 2022 instance can host databases at supported older compatibility levels; level 160 corresponds to SQL Server 2022 behavior.

An engine upgrade does not make every database operate as though its compatibility level were raised. Conversely, changing compatibility level does not install a new engine or add an edition license. Record both during an upgrade and compare workload behavior before raising the level. Older compatibility is not a guarantee that every aspect of a database behaves exactly like the older server.

SELECT name, compatibility_level
FROM sys.databases
ORDER BY

SELECT name, compatibility_level
FROM sys.databases
ORDER BY

SELECT name, compatibility_level
FROM sys.databases
ORDER BY

This read-only inventory returns each visible database and its compatibility setting. Visibility depends on permissions; a row showing 150 on a 2022 instance describes the database setting, not evidence that the engine is SQL Server 2019.

28. How would you identify the installed SQL Server build and edition?

Read the engine’s reported product version and edition, then compare the build against Microsoft’s release documentation. The SSMS version belongs to the client and cannot establish the server’s patch level. Keep the full build, not just the friendly major-version name.

For example, two instances can both be SQL Server 2022 while running different cumulative updates. That difference can matter when investigating a known issue or planning a maintenance window. Inventory the actual target instance and record the collection time rather than relying on an old installation document.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('ProductLevel') AS product_level,
       SERVERPROPERTY('ProductUpdateLevel') AS update_level,
       SERVERPROPERTY('Edition') AS

SELECT SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('ProductLevel') AS product_level,
       SERVERPROPERTY('ProductUpdateLevel') AS update_level,
       SERVERPROPERTY('Edition') AS

SELECT SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('ProductLevel') AS product_level,
       SERVERPROPERTY('ProductUpdateLevel') AS update_level,
       SERVERPROPERTY('Edition') AS

The returned values describe this instance; some properties can be NULL when not applicable. The result is an inventory input, not an automatic recommendation to install a particular update.

29. What kinds of free space should be checked before a database grows?

Distinguish free space inside allocated database files from free space on the underlying volume. A file may have reusable internal space even when the volume is nearly full, while an internally full file may need growth that the volume cannot support. Data and log files can have different pressure and reuse conditions.

Check current allocation, growth settings, maximum size, volume capacity and the recent growth rate. A large upcoming index operation or import can need temporary headroom beyond ordinary daily growth. The following inventory shows file allocation in the current database; it does not measure filesystem free space or log-space utilization.

SELECT name, type_desc,
       size / 128.0 AS allocated_mb,
       max_size, growth, is_percent_growth
FROM sys.database_files
ORDER BY

SELECT name, type_desc,
       size / 128.0 AS allocated_mb,
       max_size, growth, is_percent_growth
FROM sys.database_files
ORDER BY

SELECT name, type_desc,
       size / 128.0 AS allocated_mb,
       max_size, growth, is_percent_growth
FROM sys.database_files
ORDER BY

SQL Server reports size in 8 KB pages, so dividing by 128 gives megabytes in the customary binary SQL Server allocation convention. A value of 131,072 pages represents 1,024 MB. Interpret growth together with is_percent_growth rather than assuming it is always a page count.

30. Why are predictable autogrowth settings preferable to leaving capacity to chance?

Autogrowth is a fallback that expands a file when SQL Server needs more allocation; it should not replace planned sizing. A fixed-size increment makes the next allocation easier to estimate than a percentage, whose absolute size changes as the file grows.

For a 100 GB file, 10% initially means roughly 10 GB; on a 500 GB file it means roughly 50 GB. Either may be too large for the available headroom, while a tiny fixed increment can cause frequent growth events. Choose file-specific increments using observed workload and storage behavior, then pre-size for known large operations. There is no universally correct increment for every data and log file.

31. Why is routine database shrinking usually a poor maintenance habit?

Shrinking tries to return allocated file space to the operating system. If the workload soon needs that space again, the file grows again, adding avoidable work. Data-file shrinking can also move pages and increase index fragmentation.

A database that grew during a one-time archival project may justify a carefully planned size reduction after the data has permanently gone. A database that grows every day usually needs capacity planning instead. First distinguish data-file space from log reuse and determine why the allocation increased. A smaller file is not, by itself, evidence of a healthier or faster database.

32. How do heaps, clustered indexes and nonclustered indexes differ physically?

A disk-based rowstore heap stores table rows without a clustered index. In a clustered rowstore index, the leaf level contains the table’s data rows organized by the clustered key. A nonclustered index is a separate structure whose leaf entries contain index data and row locators to reach the base rows when needed.

This affects storage and maintenance: an additional nonclustered index needs space and work during modifications, and a wide clustered key can make nonclustered row locators wider. A clustered index does not guarantee a query’s output order without an explicit ordering requirement. Choose structures from access patterns and write costs rather than assuming every table needs the same index arrangement.

33. Why are statistics important to SQL Server’s optimizer?

Statistics describe aspects of data distribution so the optimizer can estimate how many rows an operation is likely to process. Those estimates influence access methods, join choices and memory needs. Statistics are metadata about distributions, not a second copy of an index or a guarantee of an exact estimate.

A table that grows from thousands to millions of rows can make an old estimate less representative, especially when new data has a different distribution. Compare estimates with observed execution evidence and inspect statistics before blaming hardware. Updating statistics can help, but correlation, sampling and predicate complexity can still produce inaccurate estimates; updating everything constantly also consumes resources.

34. How do estimated and actual execution plans differ operationally?

An estimated plan shows the plan SQL Server compiles without executing the statement. An actual plan is collected during execution and adds runtime information, such as observed row counts and relevant warnings. Both contain estimates; the word actual does not mean every number in the plan is measured.

For a slow report, compare estimated and observed row counts at key operators, along with elapsed time, reads and warnings. Do not collect an actual plan for a modifying statement on production merely to inspect it: collecting it executes the statement. An estimated plan can be a safer first inspection, although it cannot reveal the runtime behavior that never occurred.

35. What are dynamic management views, and what limits their interpretation?

Dynamic management views expose diagnostic information about current or accumulated engine activity. They can help inspect requests, waits, memory or other subsystems, but each view has a defined scope, lifetime and permission requirement. They are not automatically a durable monitoring history.

A snapshot taken after a slow request finishes may miss it, and some counters reset on restart or other events. Capture the time and server uptime alongside evidence. On SQL Server 2022, several performance DMVs require VIEW SERVER PERFORMANCE STATE rather than assuming an older permission rule applies to every view. Check the exact view’s documentation and grant diagnostic access deliberately.

36. How does Query Store help investigate performance changes?

Query Store keeps database-level query, plan and runtime history, making it possible to compare behavior across time windows after a request has finished. This helps answer whether a query’s plan or observed performance changed rather than relying only on the current plan cache.

For example, a report that slowed after a deployment can be compared with its earlier runtime intervals and plans. Capture policies, retention, storage limits and operational state determine what evidence is available. SQL Server 2022 enables Query Store by default for new databases, but an existing or restored database should be checked explicitly. Query Store is neither a complete record of every individual execution nor a replacement for application monitoring.

37. What can wait statistics tell you that CPU utilization cannot?

CPU utilization shows how busy processors are, while waits describe time tasks spend waiting for resources or coordination. A slow server with low CPU may be waiting on locks, storage or another resource. A high cumulative wait total alone does not identify the present bottleneck.

Compare wait deltas over the problem interval, distinguish useful signals from expected background waits, and correlate them with request and storage evidence. If latency rises while CPU is modest and blocked requests appear, adding cores may not address the problem. Wait categories point toward an investigation; they do not prove a root cause without workload context.

38. How does blocking differ from a deadlock?

Blocking occurs when one task must wait for a resource held incompatibly by another. It can be a normal, short-lived part of transactional processing. A deadlock is a cycle of dependencies in which the participants cannot all make progress; SQL Server detects it and selects a victim whose transaction is rolled back.

A long open transaction may block many sessions without any cycle, so it is not automatically a deadlock. For blocking, identify the head blocker and transaction context; for a deadlock, inspect the deadlock evidence and participating operations. Killing a session blindly can cause lengthy rollback and discard useful evidence. Application retry behavior matters after a deadlock victim is reported.

39. How should SQL Server backups be protected outside the database?

A backup contains valuable database information even when it sits outside the running server’s access controls. Restrict who can read or delete it, protect transfer paths and use an appropriate encryption design. Retention and off-host storage should address both recovery needs and access exposure.

If encrypted backups depend on a certificate and private key, protect recoverable copies of those keys separately and test recovery with the intended credentials. A backup that survives a server loss but cannot be decrypted is not a usable recovery copy. Conversely, copying production backups into a broadly accessible test folder can expose production data; test restores need their own access and data-handling controls.

40. What should a basic SQL Server monitoring baseline include?

A baseline records normal service availability, workload and resource behavior so a change can be recognized in context. Include database state, application connection success, backup freshness, job failures, CPU and memory pressure, storage latency and capacity trends. Track measurement time and workload periods, not just isolated screenshots.

For example, a nightly import may normally raise writes for twenty minutes; the same pattern lasting three hours deserves investigation. Combine resource signals with user-facing latency or failed-request measures, because an engine can be running while the application cannot use it. Assign an owner and response for actionable alerts, and review thresholds as data volume and business usage change.

MS SQL Interview Questions and Answers for Intermediate

The intermediate answers work through recovery, maintenance, monitoring and security decisions. Examples identify assumptions and limits so that a useful diagnostic query or maintenance action is not mistaken for a complete solution.

41. How do you plan a full, differential and log restore sequence?

Identify a compatible full backup, optionally the latest useful differential based on that full backup, and the uninterrupted log sequence needed to reach the recovery target. Verify backup metadata and recovery forks rather than arranging files only by their names or creation dates.

Stage

Example

State after restore

Base

Sunday full backup

NORECOVERY: additional backups can follow.

Optional shortcut

Tuesday differential based on Sunday full

NORECOVERY: log restores still needed.

Roll forward

Required logs through the target

Keep NORECOVERY until the final step.

Finish

Recover at the chosen target

RECOVERY: database becomes usable.

You do not restore every differential: each contains changes since its own base. Recovering the database too early prevents continuing that restore sequence without starting the necessary restore work again. Rehearse the exact sequence on an isolated target, including file relocation, encryption keys and application checks; a list of successful backup jobs is not proof that the recovery target is attainable.

42. What must be verified before using STOPAT for point-in-time recovery?

Confirm that the recovery model and available backup chain support the requested point, that the required log backups exist without a gap, and that the timestamp uses the intended time context. A stop time does not compensate for a missing log backup.

Suppose an unwanted change committed at 14:07 and logs cover 14:00–14:15. Restore a suitable full and any applicable differential, then apply the required logs with a consistent target just before the unwanted commit. Validate the resulting business state before directing clients to the recovered database. A wall-clock approximation may be insufficient when several transactions committed near the incident.

In bulk-logged recovery, a log backup containing bulk-logged changes limits point-in-time choices within that backup. Also account for the possibility that the desired target predates the selected differential. Preserve the original database and backups while rehearsing, and record the exact target rather than relying on an operator’s local timezone assumption.

43. When is a tail-log backup feasible after a database failure?

A tail-log backup attempts to preserve log records not yet captured by regular log backups before a restore overwrites the source state. Its feasibility depends on the recovery model, the accessibility and condition of the log, and the database’s failure state; a damaged data file does not automatically mean the log is unusable.

First establish whether the transaction log can still be read and whether the recovery sequence requires those final transactions. For an accessible database being taken into a restore workflow, the appropriate backup options can leave it in a restoring state. Damaged or offline scenarios have different documented options and restrictions, so choose them from the observed state rather than copying a universal command.

If the log itself is unavailable or damaged beyond a usable backup, acknowledge the recoverable endpoint supplied by existing backups. Do not promise zero loss or discard the original files before assessing recovery options. A failed tail-log attempt should be recorded separately from the validity of the earlier backup chain.

44. Why use a copy-only full backup for an ad hoc database copy?

A copy-only full backup can be restored like another full backup but does not establish or change the differential base. That makes it useful when taking an additional full backup without disrupting the differential backup strategy already in use.

Event

Effect on the differential base

Sunday conventional full

Establishes the base.

Monday copy-only full for a test refresh

Leaves Sunday as the base.

Tuesday differential

Still requires Sunday’s conventional full.

The Monday copy-only full is not a substitute base for Tuesday’s differential merely because it is newer. Keep backup headers and dependency information with the files so a recovery operator can identify the correct combination. Copy-only does not imply low resource use: reading and writing a large full backup can still affect storage throughput and production workload latency.

45. What different confidence do backup checksums, VERIFYONLY and a restore rehearsal provide?

Backup checksums help detect certain page or backup-stream errors. RESTORE VERIFYONLY checks backup readability and completeness and validates available checksums, but it does not restore the database or fully validate the logical structure of its contents. An actual restore exercises substantially more of the recovery path.

A useful verification ladder is to create backups with the intended checksum policy, inspect backup results, perform verification, and regularly restore a representative chain to an isolated target. Run integrity checks and application-level checks on the restored copy where appropriate. Record recovery duration as well as success.

For example, a readable backup may still require a certificate that is missing from the recovery server, or the log chain may contain a gap outside the single file being verified. No one check proves every layer. Avoid describing VERIFYONLY as equivalent to CHECKDB or as evidence that the application can meet its recovery-time objective.

46. What must accompany an encrypted backup to make disaster recovery possible?

Retain the encryption protector and the material required to use it on the recovery server. For certificate-based backup encryption, that includes the appropriate certificate and private key, protected by a securely stored password. A backup file alone may be unusable without these dependencies.

Document which certificate protected each retained backup and test importing the required material into an isolated recovery environment. Keep older protectors for as long as backups that depend on them are retained. Rotating the active certificate does not retroactively remove dependencies from old backups.

Distinguish backup encryption from TDE: a TDE-encrypted database also has a database-encryption-key protection hierarchy that must be recoverable. A recovery chain can therefore require more than one relevant protector. Protect keys separately from backup files, but make the authorized recovery procedure capable of obtaining both during an outage.

47. How do you diagnose a transaction log that keeps growing?

Separate the need for active log space from the ability to reuse inactive log space. Read the reuse wait, log utilization and workload history before changing file sizes. An active transaction, missing log backups or a lagging replication or availability component can prevent reuse for different reasons.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

SELECT total_log_size_in_bytes, used_log_space_in_bytes,
       used_log_space_in_percent
FROM

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

SELECT total_log_size_in_bytes, used_log_space_in_bytes,
       used_log_space_in_percent
FROM

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

SELECT total_log_size_in_bytes, used_log_space_in_bytes,
       used_log_space_in_percent
FROM

Run the second query in the affected database with the required monitoring permission. If the wait is LOG_BACKUP, confirm the full-recovery backup strategy and job health; if it is ACTIVE_TRANSACTION, investigate the oldest transaction. These are diagnostic observations, not instructions to kill a session or switch recovery models. Truncation makes internal log space reusable; it does not shrink the physical file. Repeated shrinking and regrowing can create avoidable pauses.

48. How should VLF count and autogrowth influence log sizing?

Pre-size the log for the workload and use deliberate growth increments so normal activity does not trigger frequent emergency growth. Assess the virtual log file layout alongside recovery and log-management behavior; neither “fewest possible VLFs” nor a universal growth size is a complete sizing strategy.

SELECT COUNT(*) AS vlf_count,
       SUM(vlf_size_mb) AS total_vlf_mb
FROM sys.dm_db_log_info(DB_ID())

SELECT COUNT(*) AS vlf_count,
       SUM(vlf_size_mb) AS total_vlf_mb
FROM sys.dm_db_log_info(DB_ID())

SELECT COUNT(*) AS vlf_count,
       SUM(vlf_size_mb) AS total_vlf_mb
FROM sys.dm_db_log_info(DB_ID())

SQL Server 2022 changed VLF creation for growth of 64 MB or less and can use instant file initialization for transaction-log growth up to 64 MB. Larger growth does not gain that log-growth benefit. These changes are version-specific, so old rules for predicting VLF counts should not be applied blindly.

The read-only DMV query requires appropriate permissions. If historical tiny growths left an excessive layout, plan any corrective resizing during a controlled window with recovery requirements preserved. The preferred steady state is a sensibly sized reusable log, not a scheduled cycle of shrink and regrow.

49. How do you decide whether tempdb needs more data files?

First establish whether the bottleneck is allocation-page latch contention, storage latency, metadata contention or simply insufficient space. More data files can help particular allocation contention patterns; they do not fix every tempdb wait or a slow storage subsystem.

Inspect the waiting resource and affected page types, current file sizes and growth settings, and workload concurrency. PAGELATCH waits concern in-memory page synchronization, whereas PAGEIOLATCH waits involve bringing pages from storage. Confusing them can lead to adding files when the real problem is I/O latency.

Start from Microsoft’s version-aware configuration guidance, keep data files comparably sized with consistent growth, and adjust based on observed contention. Do not add one file per logical processor without considering the existing count and evidence. Recheck throughput and waits after the change, and separately investigate workloads creating excessive temporary objects or spills.

50. How can row-versioning workloads create storage pressure, and where should you look?

Long-running readers can keep older row versions needed while concurrent updates continue producing new ones. In SQL Server 2022, the traditional version store uses tempdb when ADR is not enabled for the user database; with ADR enabled, the user database’s persistent version store is the relevant location for those versions.

Correlate version-store growth with active snapshot transactions, update volume and cleanup progress. A long report may consume little CPU while preventing older versions from being cleaned. Ending it is an operational decision that should account for the report and transaction state, not an automatic response to a large version store.

Provision capacity and shorten unnecessarily long transaction lifetimes, then verify cleanup. Adding tempdb space will not address growth in a user database’s persistent version store. Do not project SQL Server 2025’s ADR-in-tempdb features onto a SQL Server 2022 installation when selecting the monitoring path.

51. How would you choose max server memory without relying on a fixed percentage?

Budget memory for the operating system, other services, other SQL Server instances and allocations not governed by the setting, then observe the workload under representative load. max server memory is not a hard ceiling on every byte in the SQL Server process.

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('min server memory (MB)', 'max server memory (MB)')

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('min server memory (MB)', 'max server memory (MB)')

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('min server memory (MB)', 'max server memory (MB)')

On a dedicated host, the required operating-system and driver headroom may differ from a host also running ETL or backup utilities. Container or virtual-machine limits further change the usable budget. Monitor OS pressure, SQL Server memory signals and query concurrency before and after a controlled adjustment.

Avoid a universal “use 80%” answer. Too high a setting can starve neighboring components; too low can cause unnecessary cache churn or memory-grant pressure. The query reports configuration, not proof that the current budget is healthy, and the correct budget can change when the host’s responsibilities change.

52. How do MAXDOP and cost threshold for parallelism serve different tuning purposes?

MAXDOP limits parallel execution degree at the applicable scope, while cost threshold influences when the optimizer considers parallel plans. The cost is an optimizer estimate, not elapsed seconds. Both settings need workload and hardware context rather than one universal pair of numbers.

Inspect CPU topology, NUMA or soft-NUMA layout, concurrent request volume and whether the workload consists of many short requests or fewer large analytical queries. A parallel query may finish sooner while consuming more worker capacity, so per-query speed and whole-server throughput can move in opposite directions.

Use Microsoft’s topology guidance as a starting point and measure changes across a representative period. Account for database, query or workload-level overrides rather than assuming the server value is always effective. Raising the threshold does not repair inaccurate cardinality estimates, and setting MAXDOP to one globally can mask a problem while slowing valid analytical work.

53. When should statistics maintenance be targeted rather than updating everything with FULLSCAN?

Identify statistics relevant to a poorly estimated predicate or join, inspect their age and modification evidence, and choose an update scope and sample appropriate to the distribution. A recent timestamp alone does not prove that the histogram represents a highly skewed or newly appended range well.

For example, a table may receive a large new batch above the old maximum date while most historical rows remain unchanged. The critical issue is estimation for that new range, not simply the percentage of the entire table modified. Compare estimates and actual rows before and after a targeted update.

FULLSCAN can be expensive on large objects, and refreshing every statistic can cause unnecessary work and recompilation. Sampling may be sufficient, but validate representative parameter values. Also distinguish index statistics from independently created column statistics; rebuilding one index does not refresh every statistic on the table.

54. How do index rebuild and reorganize differ in their effect on statistics?

A rebuild recreates an index and updates its associated index statistics as part of that work. Reorganizing adjusts the existing index structure and does not update statistics. Neither operation automatically refreshes all unrelated column statistics on the table.

Operation

Statistics implication

Operational consideration

Reorganize

Separate statistics update may still be needed.

Incremental physical maintenance still consumes resources.

Rebuild

Associated index statistics are refreshed; sampling details have exceptions such as partitioned indexes.

Can require substantial log, space, CPU and locking coordination.

If a query improves after rebuilding, determine whether the benefit came from better statistics or physical layout before scheduling frequent rebuilds. A targeted statistics update might deliver the same benefit at lower cost. Online and resumable choices have version, edition and operation restrictions; online does not mean zero locking or zero workload impact.

55. Why should index maintenance consider page density as well as fragmentation?

Logical fragmentation describes page-order relationships, while page density describes how full pages are. Low density can make a query read and cache more pages even when fragmentation is modest. The practical impact depends on index size, access pattern and storage behavior.

A small index that fits in a few pages may show a high fragmentation percentage without causing a meaningful bottleneck. A large scan-heavy index with sparsely filled pages may consume substantial extra I/O and buffer space. Compare actual query reads and runtime with the maintenance cost rather than applying a percentage rule to every object.

Fill factor is a workload trade-off: reserving free space may reduce certain page splits but also lowers density. Do not lower it globally because some indexes split frequently. Target the demonstrated problem, measure the resulting density and workload behavior, and include log generation and maintenance-window constraints in the decision.

56. How do included columns change the cost-benefit analysis of a covering index?

Included columns can supply payload values at the index leaf level without making them key columns. This can eliminate lookups for a query, but the wider leaf structure consumes storage, memory and maintenance work, especially when included values change frequently.

For a customer-status lookup returning a short description, a small included payload may be worthwhile. Including every text and audit column to cover all possible queries can create a large secondary copy of the table and increase write amplification. Evaluate the queries that actually benefit and their frequency relative to writes.

Check whether the existing index already covers the needed values and whether a new index overlaps others. Included columns do not supply the same ordering or seek capabilities as leading key columns. Validate the plan and logical reads with representative parameters before accepting a missing-index suggestion as a complete design.

57. Why might SQL Server not use a filtered index that appears to match a query?

The optimizer must be able to establish that the query’s qualifying rows are covered by the index filter, and the index must offer a useful access path. Parameterization or a predicate that does not imply the filter can prevent a safe use even when one observed parameter value would fit.

For example, an index covering only active rows may suit a query whose predicate explicitly requires active status. A reusable plan for an unrestricted status parameter must also be correct for inactive rows, so it cannot assume that every execution fits the filtered subset.

Inspect the actual parameterization and predicate, required SET options, available filtered statistics and plan choice. Do not force the index before proving correctness for all permitted inputs. Filtered indexes can reduce maintenance for a stable subset, but frequent transitions into and out of that subset still generate index work.

58. How do columnstore rowgroup states help explain disappointing analytical performance?

Columnstore efficiency depends partly on how data is grouped and compressed. Many small compressed rowgroups, a large delta-store population or a high proportion of deleted rows can reduce the benefit of scanning compressed segments. The presence of a columnstore index alone does not guarantee ideal layout.

Inspect rowgroup state, total and deleted rows, load batch sizes and memory conditions during loading. Small trickle inserts and updates have different effects from large bulk loads. Background processing may compress or merge eligible groups, so relate a snapshot to load timing before deciding that maintenance is required.

Use targeted maintenance only when it addresses the observed rowgroup problem and fits resource limits. Also verify segment elimination and query shape: a selective lookup workload may not be a good match for a broad columnstore scan. Available maintenance and performance features depend on version and edition, so avoid importing assumptions from a different deployment.

59. Why does partitioning a large table not automatically make queries faster?

Partitioning organizes data for manageability and can enable partition elimination when predicates and plans identify the relevant partitions. A query scanning every partition may do similar or greater work, and partitioning does not replace appropriate indexes or accurate statistics.

For a table partitioned by month, verify whether a date-restricted plan actually accesses only the expected partitions. A predicate on another attribute, a conversion on the partitioning expression or an unsuitable plan can prevent the intended pruning. Measure reads and accessed partitions rather than assuming the partition scheme is being used effectively.

Partitioning can still be valuable for retention and data movement even without faster reads. Plan boundary management, aligned indexes, statistics and operational procedures together. Excessive partitions add metadata and maintenance complexity, so the partition interval should follow the management requirement and workload rather than an arbitrary desire for more partitions.

60. How would you evaluate row or page compression for a production table?

Estimate the potential space reduction, then test representative reads and writes with the expected CPU headroom. Compression can reduce I/O and buffer usage but adds CPU work and may benefit some data patterns much more than others.

A large repetitive archive can be a good candidate for page compression, while a heavily updated table with little compressible repetition may gain less. Evaluate the relevant partitions or indexes separately instead of treating the entire database as one homogeneous workload. Include log generation, deployment time and temporary space in the change plan.

An estimator’s predicted savings are not a performance benchmark. Compare storage, logical and physical reads, CPU and latency under realistic concurrency, then document rollback options. Check SQL Server version, edition and object restrictions before promising that the chosen compression operation is available or can run online.

61. How would you investigate an implicit-conversion warning in a slow plan?

Locate the conversion in the plan and determine which side is converted, whether it affects a search predicate or join, and whether it changes cardinality estimation or index access. A warning is a clue, not proof that the conversion is the dominant cost.

A common operational case is an application binding a parameter with a type or length that differs from the indexed column. Compare the submitted parameter metadata with the schema and inspect the actual plan for representative values. Converting a column for every row can have different consequences from converting one parameter.

Correct the application binding or schema contract where appropriate, then compare logical reads, CPU and elapsed time. Do not add casts blindly: they may move the conversion to a worse location or alter comparison semantics. Keep this diagnosis separate from a conversion error that prevents a query from running at all.

62. How do estimated-versus-actual row discrepancies guide plan diagnosis?

Find where the first important estimate diverges from actual rows, not just the operator with the largest final discrepancy. An early error can influence join order, algorithm choice and memory grants throughout the rest of the plan.

For example, a scan estimated to return 50 rows may actually return 500,000 because of skew, stale statistics or a parameter value unlike the compiled one. A downstream join then amplifies the mismatch. Inspect the predicate, statistics and compilation context before changing the join operator.

Account for executions, loops and parallel-plan reporting when comparing row counts. An operator executed many times needs a different reading from one scan. A better estimate is useful only if it leads to improved workload behavior; validate the revised plan across representative inputs rather than optimizing solely for the captured incident.

63. How do you distinguish a memory-grant wait from a spill?

A grant wait means a query is waiting for execution workspace memory. A spill means an operator used temporary disk work because its available memory was insufficient for the operation. A workload can experience either or both, and their remedies are not automatically the same.

SELECT session_id, requested_memory_kb, granted_memory_kb,
       required_memory_kb, used_memory_kb, wait_time_ms
FROM

SELECT session_id, requested_memory_kb, granted_memory_kb,
       required_memory_kb, used_memory_kb, wait_time_ms
FROM

SELECT session_id, requested_memory_kb, granted_memory_kb,
       required_memory_kb, used_memory_kb, wait_time_ms
FROM

With the required server monitoring permission, inspect current grants alongside actual-plan spill warnings and concurrency. Excessively large grants can make other requests wait even if the large-grant query never uses all its reservation. Underestimates can yield too-small grants and spills despite free memory elsewhere.

Review cardinality, row width, sort or hash requirements and competing queries before increasing server memory or forcing a grant. Memory-grant feedback may help eligible workloads, but availability and behavior depend on version, compatibility level and execution mode; it is not a substitute for diagnosing a persistent workload mismatch.

64. Why should wait statistics be compared over an interval rather than ranked once?

Many server wait counters accumulate over time, so the largest total may describe yesterday’s workload or a benign background wait rather than the current incident. Capture a baseline and a later sample, then compare deltas for the same measurement interval.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM

If one wait has accumulated 200 hours since startup but increased only one second during the incident, while another increased 20 minutes across concurrent tasks, the second may be more relevant. Aggregated wait time can exceed wall-clock duration because many workers wait simultaneously.

Record server uptime or counter resets, filter known background waits thoughtfully and correlate deltas with query and resource evidence. A high signal-wait component may suggest scheduling pressure, but a wait name alone is not a root cause. Avoid clearing global counters merely to simplify a diagnosis when interval snapshots can preserve other observers’ history.

65. How can a sleeping session still be the head of a blocking chain?

A session can have no active request while retaining an open transaction and its locks. Looking only at currently running requests can therefore miss the session that blocks the rest of the chain. Combine request, session and transaction evidence.

SELECT session_id, status, open_transaction_count,
       login_name, host_name, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND open_transaction_count > 0

SELECT session_id, status, open_transaction_count,
       login_name, host_name, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND open_transaction_count > 0

SELECT session_id, status, open_transaction_count,
       login_name, host_name, program_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND open_transaction_count > 0

Investigate the oldest relevant transaction, lock ownership and the application’s last submitted work. Client metadata helps identify an owner but is not a trusted authentication record. A connection pool or abandoned error path can leave a transaction open while the session appears idle.

Coordinate with the application owner before terminating the session, and estimate rollback impact where possible. Killing the head blocker can initiate a long rollback rather than restore service instantly. Fix the transaction-lifetime problem after containment so the next pooled connection does not recreate it.

66. How would you read a deadlock graph captured by Extended Events?

Identify the victim, participating processes and resource ownership/wait edges, then reconstruct the cycle. The victim is the transaction SQL Server chose to roll back; it is not automatically the transaction responsible for the underlying design problem.

For example, process A owns a lock needed by B while B owns a different lock needed by A. Read the execution stacks, isolation context and object or index resources to establish which access paths created that order. The final statement text may not reveal earlier work in either transaction.

Use the system_health session’s deadlock evidence where available, or a targeted capture with appropriate retention. Compare several occurrences before concluding that one index or one transaction path explains them all. Test a structural fix such as reducing transaction duration or making access order consistent; the graph analysis should produce a causal hypothesis, not merely a recommendation to retry.

67. Why prefer a targeted Extended Events session over a broad Profiler trace?

Extended Events provides a modern event infrastructure with filters, actions and targets that can be scoped to a diagnostic question. SQL Trace and Profiler are deprecated for Database Engine tracing. A broad capture can impose overhead and generate more evidence than anyone can use.

Start with the symptom: for a specific slow database operation, capture only relevant completed events and selected context, using predicates where supported. Choose a bounded file target and retention plan, and measure overhead. Expensive actions or high-frequency events can still make an Extended Events session costly.

Verify that the session actually records a known test event before relying on it during an incident. Secure captured SQL and parameter text because it may contain sensitive data. Stop or narrow temporary diagnostics after the question is answered, and preserve enough timestamps and identifiers to correlate events with the incident.

68. How should Query Store plan forcing be used during a regression?

Use Query Store history to confirm that a plan change coincides with a meaningful regression for the same query and comparable workload. A previously successful plan can be a temporary mitigation, but plan forcing is not a guarantee that every parameter or future schema remains suitable.

Compare execution counts, duration, CPU and waits over relevant intervals, including parameter or workload shifts. After forcing an eligible plan, monitor whether forcing succeeded and whether the workload improved. SQL Server can report forcing failures, and a forced plan may still perform poorly for a different data distribution.

Define an unforce or rollback decision and investigate the underlying change, such as statistics, indexes or compatibility behavior. Check Query Store capture and storage health so missing evidence is not mistaken for absence of a regression. Do not force the fastest historical outlier without establishing that its context matches the current workload.

69. How do SQL Server Agent proxies differ from the Agent service account?

A proxy associates a credential with permitted job-step subsystems and lets authorized steps run under that identity instead of broadly extending the Agent service account’s access. The proxy must be allowed for the relevant subsystem and caller, and the underlying identity still needs access to external resources.

For a Windows CmdExec step that writes to a file share, check the selected proxy, credential identity and both share and filesystem permissions. Do not grant local administrator rights simply because the job succeeds interactively for an administrator. Interactive execution and scheduled execution may use different identities, working directories and environment variables.

Transact-SQL job steps do not use Agent proxies in the same way; their security context follows the documented job-owner and execution rules. Proxy and subsystem support also varies by platform. Establish the actual subsystem and deployment before prescribing a credential change.

70. What should you verify before enabling automatic retries on a SQL Server Agent job step?

Determine whether repeating the step can duplicate external effects and whether the step has a durable completion boundary. Agent retries repeat a job step; they do not automatically undo its previous partial work or make the entire job atomic.

Suppose a step exports a file and then records completion. If the export succeeds but the completion record fails, a retry might export or deliver the same file twice. Use a stable batch identifier, staged output and a verified completion protocol appropriate to the destination. Also consider what happens if the Agent service restarts between steps.

Choose retry count and interval for transient failures, and route exhausted retries to an observable failure path. Inspect success and failure transitions so a later “cleanup” step cannot make the overall job appear successful after critical work failed. Test rerunning from the failed step and from the beginning as separate operational procedures.

71. How do you verify that a failed Agent job will actually notify an operator?

Test the complete path: job failure detection, Agent notification configuration, operator assignment, Database Mail profile access, mail queue processing and receipt at the intended destination. A successful test email alone does not prove the job’s failure transition sends a notification.

Use a controlled test job or approved failure exercise, then inspect job history and Database Mail status. Distinguish a queued message, a successful SMTP handoff and actual recipient delivery. Mail filtering or an unavailable destination can break the final step even when SQL Server reports that it sent the message.

Keep a second monitoring route for failures of Agent or the mail system itself; a stopped scheduler cannot reliably report its own missed jobs. Agent and Database Mail support differs across SQL Server platforms and editions, so verify that the deployment provides the required components before designing the alerting path.

72. How would you choose a CHECKDB schedule for a large database?

Balance the maximum acceptable detection delay against execution cost, database size and available maintenance capacity. Measure actual CHECKDB runtime and resource use, then ensure retained backups extend far enough to recover from corruption discovered at the next check.

A reduced-cost physical check can be useful between fuller checks, but it does not provide the same coverage as a complete logical and physical integrity check. Running checks on a restored copy also exercises recovery and can offload work, while leaving questions about the live storage path that need separate monitoring.

Record successful completion, not merely job start, and alert on missed or incomplete runs. Account for temporary workspace and the internal snapshot behavior when planning capacity. A weekly schedule is not inherently correct if corruption could go undetected longer than the available good-backup retention window.

73. What should be the first response to a confirmed corruption report?

Preserve the error evidence, assess the affected objects and business impact, and investigate a restore from a known good backup as the primary recovery path. Retain the original files and backup chain while determining the extent of the problem and the likely storage or system cause.

Do not begin with a repair option that permits data loss. Such repair can remove damaged structures or rows to regain consistency, which is not the same as restoring business correctness. If no acceptable restore path exists, specialist evaluation and explicit acceptance of possible loss are needed before destructive repair.

After recovery, validate integrity and application invariants and address the underlying cause. A clean CHECKDB result does not prove that deleted business records were reconstructed. Document the recovery endpoint, any known loss and the backup or monitoring changes needed to reduce the chance of another unrecoverable incident.

74. What does TDE protect, and which keys matter during recovery?

TDE protects database data and log files at rest by encrypting pages written to storage. The database encryption key is protected through a server-side key hierarchy, commonly using a certificate in master. Recovery on another instance requires the corresponding protector and usable private-key material.

Keep certificate backups and their passwords under controlled custody and rehearse restoration on a different server. Retain old protectors needed by older backups. A healthy production instance does not prove the recovery environment has the keys, because the original instance may already have them loaded and accessible.

TDE does not stop an authorized SQL query from reading plaintext, replace TLS for network traffic or prevent all privileged server access. It also has edition and version availability constraints. Define the threat being addressed before treating “encryption enabled” as proof that every data path is protected.

75. When does Always Encrypted address a threat that TDE does not?

Always Encrypted can keep sensitive column plaintext and keys outside the ordinary Database Engine boundary by having supported client drivers perform encryption and decryption. TDE protects storage media while the engine ordinarily works with plaintext, so it does not provide the same separation from database operators.

The application needs compatible drivers, key-store access and supported query patterns. Encryption choice affects available comparisons and operations; deterministic encryption also exposes equality patterns. Secure-enclave capabilities expand supported operations but introduce additional deployment, attestation and trust considerations.

Test the exact client and server configuration with the intended workload, including parameter handling and key rotation. Always Encrypted does not protect plaintext after an authorized client decrypts it, nor does it automatically redesign reports that require unsupported server-side operations. Choose the boundary deliberately rather than treating it as a checkbox replacement for TDE.

76. Why is encrypting a SQL connection insufficient without certificate validation?

Transport encryption protects the connection’s contents, while certificate validation helps establish that the client reached the intended server. Bypassing trust validation can leave an encrypted connection vulnerable to an impersonated endpoint.

Provision a certificate meeting the server and driver requirements, ensure the client trusts its chain and connect using a name covered by the certificate. Check expiration, private-key access and intermediate certificates during deployment. Explicit connection settings are useful because defaults and strict-encryption options vary across driver generations.

Do not make TrustServerCertificate=true the permanent response to a certificate error. Diagnose whether the problem is an untrusted chain, hostname mismatch or unsupported configuration, then verify the application’s actual connection. Server-side enforcement and client validation need to work together; a connection flag in a configuration file is not evidence of the negotiated session.

77. How do you design a SQL Server Audit configuration that produces useful evidence?

Map the required evidence to server- or database-level actions, select the relevant audit specifications and configure a protected target with retention and failure behavior. Creating an audit object is not sufficient if the necessary specifications are disabled or the target cannot accept events.

For example, investigating permission changes requires different actions from tracking access to a specific sensitive table. Capture enough identity, object and timestamp context to answer the question without indiscriminately logging every high-volume event. Verify a controlled representative action appears in the target and that readers can retrieve it.

Protect audit files from alteration and plan for full disks or unavailable targets. The configured response to audit failure affects availability and evidence completeness, so it must be deliberate. SQL Server Audit supplies events; it does not itself establish that retention, access controls and review practices satisfy an organization’s obligations.

78. How do row-level security and dynamic data masking enforce different boundaries?

Row-level security applies predicates controlling which rows are visible or which writes are permitted. Dynamic data masking changes how selected column values appear to callers without the relevant unmasking privileges. Masking does not filter rows or encrypt the stored value.

Requirement

Relevant mechanism

Important limitation

Tenant sees only its permitted records

RLS filter and, where needed, block predicates

The execution context and policy administration must be trustworthy.

Reduce casual exposure of a sensitive field

Dynamic data masking with least privilege

Queries and inference can still reveal information; masking is not a strong isolation boundary.

Test under actual application identities rather than only an administrator session. Verify inserts and updates as well as SELECT behavior for an RLS design, and restrict who can change the policy. Use encryption or a stronger data-access boundary when the threat includes users allowed to issue arbitrary queries over sensitive values.

79. How do contained database users improve portability, and what still needs migration?

Contained database users can authenticate at the database boundary without depending on the same conventional instance login mapping. This can reduce login-SID problems when moving a database, provided containment is configured and clients specify the intended database.

The database does not automatically carry every operational dependency. Agent jobs, linked servers, server permissions, external credentials and instance configuration may still require separate migration. Inventory those dependencies and test application access on the destination using the actual connection string.

Containment also changes the administration boundary: principals permitted to manage contained users can affect who accesses that database. Review password and access policies accordingly. Do not treat containment as a universal answer for every authentication model or assume a successful database restore means the whole application is portable.

80. Why can a restored database user be orphaned even when a login with the same name exists?

Conventional login-to-user mapping relies on the security identifier, not just the displayed name. A destination login recreated with a different SID can leave the restored database user unmatched even though both are called the same thing.

SELECT dp.name, dp.type_desc, dp.authentication_type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.type = 'S'
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL

SELECT dp.name, dp.type_desc, dp.authentication_type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.type = 'S'
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL

SELECT dp.name, dp.type_desc, dp.authentication_type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.type = 'S'
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL

Run this in the restored database with enough metadata visibility; it focuses on instance-authenticated SQL users and is not a universal audit of every principal type. After verifying the intended identity, remap the user with the supported ALTER USER ... WITH LOGIN approach or migrate the login preserving its SID as appropriate.

Do not solve the mismatch by dropping and recreating users without reviewing permissions and ownership. Contained users and certificate-based principals need different handling. Test the application’s effective permissions after remediation, not merely whether the login can connect.

MS SQL Interview Questions and Answers for Experienced

Experienced answers examine availability design, recovery dependencies, engine resource behavior and operational controls. They distinguish technical configuration from the evidence needed to show that a service can meet its recovery and reliability goals.

81. How would you choose between an availability group and a failover cluster instance?

Choose according to the failure domain and recovery scope. An availability group maintains database copies on replicas; a failover cluster instance moves an instance’s service ownership between cluster nodes and depends on its clustered storage architecture. Neither design automatically protects every dependency needed by the application.

Design concern

Availability group

Failover cluster instance

Recovery unit

Selected availability databases

SQL Server instance

Storage dependency

Separate replica storage in the usual design

Clustered storage accessible to the active node

Instance objects

Plan logins, jobs and other external dependencies

Instance identity and system databases move with the instance

If the main risk is a failed compute node while shared storage remains healthy, an FCI can fit well. If a storage or site failure must leave an independent database copy, evaluate replicas in another failure domain. Check edition capabilities and platform-specific clustering requirements; SQL Server 2022 Standard basic AG capabilities are narrower than a full Enterprise AG.

82. What trade-off does synchronous commit make between latency and recovery point?

With a healthy synchronized synchronous-commit secondary, the primary waits for the required log hardening acknowledgement before acknowledging commit. This strengthens protection for acknowledged transactions but adds network and secondary-log latency. Asynchronous commit avoids waiting for that remote hardening, so acknowledged primary transactions may not yet exist at the secondary.

For example, a remote site with variable network delay may make synchronous writes exceed the application’s latency budget. Asynchronous replication may be acceptable if the business accepts a measured potential-loss window. Monitor actual send and hardening progress rather than treating a mode setting as evidence of current protection.

A disconnected or unhealthy secondary changes the situation, and required-synchronized-secondary settings can affect write availability. A no-loss claim needs the relevant replica state and failover prerequisites, not merely the word synchronous in configuration. Redo lag also matters to recovery time even after log records have been hardened.

83. What evidence is needed before a planned or forced AG failover?

A planned no-data-loss failover requires an eligible synchronized synchronous-commit target and the documented readiness conditions. A forced failover is an emergency decision that can lose transactions; it is not a shortcut for a planned switchover when synchronization is incomplete.

Before action

Reason

Record replica/database state and hardening progress

Establish what the target actually has

Control client writes and isolate the former primary if necessary

Avoid conflicting service ownership

Confirm application routing and instance dependencies

Database role change alone does not restore service

Approve potential loss and reconciliation plan for a forced action

Make the consequence explicit

If the primary is unavailable, the exact lost transaction set may be unknowable until surviving evidence is reconciled. Preserve logs and the former primary’s state where possible. After recovery, verify business transactions and reestablish redundancy; a reachable new primary is only the first recovery milestone.

84. Why can an AG listener failover succeed while clients still cannot reconnect?

The listener provides a stable connection endpoint, but client recovery also depends on DNS, driver behavior, network reachability, connection-pool state and the connection string. In a multisubnet configuration, a supported driver with MultiSubnetFailover=True can improve connection behavior; it does not repair blocked network paths or invalid credentials.

Test the real application from each client network, including a pooled connection that becomes invalid during failover. Capture time to reconnect and time to complete a successful transaction separately. A fresh administrative connection from the database subnet is not a substitute for this test.

Retries must handle uncertain transaction outcomes. If a connection drops after the server commits but before the client receives confirmation, blindly repeating a payment can duplicate work. Combine driver-appropriate retry handling with application operation identifiers or another safe reconciliation mechanism. Keep platform and driver versions in the failover runbook.

85. What consistency limits apply when routing reads to an AG secondary?

A readable secondary can lag the primary because received log records still need to be redone. Synchronous commit concerns log hardening, not a guarantee that every committed change is immediately visible to a query on another replica. Read-only routing also requires suitable listener, routing and client read-intent configuration.

A user who saves an order on the primary and immediately reads it through a reporting replica may briefly see no order. Route that read-after-write interaction to the primary or design an explicit consistency mechanism. A dashboard that tolerates a documented lag may be a better secondary workload.

Monitor redo backlog, query pressure and observed freshness together. Heavy reporting can compete for secondary resources needed to remain current and recover quickly. Test both ordinary load and a catch-up period after disconnection; a low-lag idle replica is weak evidence for peak-hour behavior.

86. Why does an AG backup preference not guarantee that backups run on the preferred replica?

The preference is advisory metadata that a backup job must honor. SQL Server does not automatically move a scheduled job or reject every backup that ignores the preference. Deploy appropriate jobs to candidate replicas and make their decision logic check whether the local replica is preferred.

For the SQL Server 2022 baseline, secondary full backups are copy-only and differential backups on secondaries are not supported. Design supported backup types and schedules together rather than assuming a primary backup script can be copied unchanged. Later-version capabilities must be checked separately.

Keep a recoverable catalog of actual backup files and log sequence coverage across replicas. After failover, prove that the new job placement still creates the required chain and that credentials can access the destination. A successful preference check with no runnable job is not backup coverage, and backup history stored on one instance is not a complete cross-replica recovery catalog.

87. What shared dependencies can defeat an FCI’s node redundancy?

An FCI protects against some node failures, but nodes can still share storage, network, identity and cluster dependencies. If the clustered storage becomes unavailable to every node, moving ownership does not restore the database files. The storage design may itself be resilient, but that protection must be evaluated separately.

Build a dependency map covering storage paths, cluster networks, listener/name resolution, service identities and the witness. During a node-failure exercise, verify that the surviving node has the required paths, permissions and capacity. A configuration that has never run production load on the secondary node can conceal asymmetric setup.

For site-level protection, assess whether the storage and cluster design spans the intended failure domains or needs a separate disaster-recovery mechanism. Do not describe every FCI as using a single physical shared disk; supported clustered storage architectures differ. The operational question is which dependencies fail together.

88. How does quorum affect availability during a cluster network partition?

Quorum determines which cluster membership can continue operating and helps avoid competing ownership after a partition. A witness contributes to the voting design; it is not a database replica and does not contain the missing transaction data. Losing quorum can intentionally stop service even when a SQL Server process or disk remains available.

Evaluate the Windows Server Failover Clustering voting model, dynamic quorum behavior and witness placement for the actual topology. A witness reachable only through the same failed site or network can provide less independence than expected. Count failure combinations, not just nominal node count.

Emergency force-quorum procedures require controlled isolation and the platform’s documented recovery sequence. Starting competing partitions without establishing authority can create a more serious incident. Linux deployments use different cluster-manager mechanics, so a Windows quorum runbook must not be presented as a universal SQL Server procedure.

89. How would you assess log shipping readiness for a disaster?

Inspect backup, copy and restore progress separately. A log backup present at the secondary but not restored is different from one still stranded at the primary site. An intentional restore delay can protect against some recent mistakes, but it increases the work and time needed to bring the secondary current.

For example, if backups occur every five minutes and the copy job has failed for forty minutes, the configured five-minute schedule does not establish a five-minute recovery point. Determine which files survived, whether the chain is intact and whether a final tail-log backup is possible before recovery.

Log shipping does not automatically perform application failover. Rehearse the decision to stop restore processing, apply the selected remaining logs, recover the database and redirect clients. Include logins, jobs and dependent systems. After making a secondary writable, rebuilding the shipping relationship may require a deliberate reinitialization plan.

90. Why is transactional replication not a complete disaster-recovery plan?

Replication distributes selected data and changes according to publication and subscription configuration; it does not inherently reproduce the entire instance, every database object or every recovery dependency. An accidental change can also propagate to subscribers. A subscriber being queryable is not proof that it can take over the application correctly.

Operationally, distinguish the Log Reader Agent, distribution backlog and Distribution Agent progress. The Snapshot Agent prepares initialization data; a failed initialization and a stalled incremental stream need different investigation. Monitor latency and retained commands so an outage does not silently exceed retention or require unexpected reinitialization.

If a subscriber is proposed as an emergency source, document exactly which tables, identity behavior, permissions and business writes it supports. Test the application’s full dependency set and the path back to normal operation. Keep independent backup and recovery controls rather than assuming replication preserves an uncontaminated historical recovery point.

91. What makes a distributed AG useful but delicate during a version migration?

A distributed AG connects two availability groups and can support migration patterns across infrastructure boundaries. It is not simply another ordinary replica within one AG, and its role changes and monitoring require the distributed topology’s documented procedures.

In a supported mixed-version migration, data movement toward the newer version can be possible before cutover. Once the newer environment becomes primary and database upgrade occurs, shipping changes back to an older engine is not a general rollback path. Reestablishing a healthy bidirectional operational posture may require upgrading the former environment too.

Validate the exact source/target version combination, endpoint security, seeding capacity and failover instructions. SQL Server 2022 introduced additional distributed-AG commit controls, so procedures written for older versions need review. Define the last reversible checkpoint before permitting writes on the new primary, and reconcile application changes independently of database transport.

92. How does database-level AG health detection differ from instance health monitoring?

An instance can be alive while one availability database is unusable. Database-level health detection adds selected database-state failures to the failover decision; it is not equivalent to checking whether every business query is correct or fast. Automatic failover still requires an eligible target and the other configured prerequisites.

For example, a database becoming suspect can require a different response from a single query timing out due to blocking. Tune the health policy around documented failure conditions and test the exact failure you intend it to detect. Do not promise failover for every corruption symptom or workload stall.

SQL Server 2022 database-level detection should also be distinguished from later enhanced-database-failover capabilities described in current documentation. Record engine version and enabled options in the design. A health monitor’s success criterion is the intended service outcome, not merely that it can switch a role under one synthetic failure.

93. What belongs in an instance recovery runbook for system databases?

The runbook must restore a usable instance before assuming user-database recovery can proceed normally. Preserve engine build information, startup parameters, file locations, service identities and backups of the relevant system databases. Their restore requirements differ from ordinary user database migration.

Component

Recovery concern

master

Instance metadata, logins and database registration

msdb

Jobs, schedules and operational history

model

Template settings used for newly created databases and tempdb

External material

Keys, certificates, credentials and executable dependencies

If damaged system databases prevent startup, documented rebuild procedures may be needed before restoring matching backups. System database backups must match supported engine-version/build requirements; do not treat a newer user-database restore rule as permission to restore arbitrary master backups. Recreate and verify external dependencies after the instance starts, then validate application service rather than stopping at database ONLINE status.

94. Why is restoring master more than copying a backup file onto a replacement server?

The engine must be able to start in the documented recovery configuration, and the backup must be compatible with the replacement instance. If master is too damaged to start SQL Server, rebuilding system databases may be necessary first. Restoring master is a special single-user recovery operation and shuts down the instance on successful completion.

The restored metadata can reference file paths, service identities and server-level objects from the original machine. Check those assumptions before restart. Preserve encryption material and the service master key recovery plan; restoring metadata does not guarantee that every protected credential can be decrypted under a different operating-system context.

Rehearse on an isolated replacement with a matching build and known paths. Control automated clients so they do not take the single-user connection intended for recovery. Avoid publishing a generic overwrite command as a complete runbook: the safety of the operation depends on the target identity, backup provenance and surrounding recovery state.

95. What is lost when msdb is unavailable, and what can still be recovered?

msdb contains SQL Server Agent job and schedule metadata and backup/restore history, among other operational information. Losing that history does not delete backup files already stored elsewhere. Conversely, restoring history does not make missing or corrupted backup files available.

Maintain independent job definitions, ownership and credential references plus a backup catalog outside the instance. During recovery, locate media, inspect its metadata and reconstruct the required restore chain from surviving evidence. Recreate schedules carefully so imports, maintenance and outbound integrations do not all start unexpectedly.

Verify the distinction between a job existing and being runnable: its owner, proxy, linked dependency or network share may be missing. Keep jobs disabled until their dependencies and intended recovery behavior are confirmed. The incident is not resolved by a green Agent service alone.

96. How can piecemeal restore recover critical filegroups before the whole database?

Piecemeal restore recovers a database in stages, beginning with a partial-restore sequence that includes the primary filegroup. Under the simple recovery model, the initial sequence must also include all read/write filegroups; read-only filegroups can be restored later. Full and bulk-logged recovery models provide more flexibility, subject to the required backups and log sequence.

For a database with current operational data and a large read-only archive in separate filegroups, this can bring the critical portion back sooner. The application must tolerate archive objects remaining unavailable; a routine that joins current and archived data can still fail. Filegroup placement and application dependencies determine whether staged availability is useful.

Recover each later filegroup to a state consistent with the database, checking its read-only history and required recovery material. Do not assume an old filegroup backup is sufficient merely because it is read-only now. SQL Server 2022 Enterprise supports online restore scenarios, while Standard uses offline restore; specialized features such as memory-optimized filegroups impose additional rules. Rehearse the exact supported sequence and access behavior.

97. What limits apply when restoring databases across SQL Server versions?

A supported older user-database backup can be restored to a newer engine, where the database is upgraded as required. A backup from a newer engine generally cannot be restored to an older engine. Lowering compatibility level changes selected query behavior; it does not downgrade the physical database format.

Before migration, verify the supported upgrade path, edition-dependent features and required keys or external components. A successful small restore does not establish that a database using unsupported features will work on a different edition. System databases have stricter matching requirements and should be planned separately.

For rollback after writes begin on the newer version, define how those writes would be preserved and moved through a supported path. Retaining the old server helps before cutover, but it does not magically make post-cutover changes available there. Rehearse the actual reversal or forward-fix plan and state any accepted loss window.

98. How do in-place and side-by-side upgrades change rollback planning?

An in-place upgrade changes the existing installation and can reduce infrastructure duplication, but rollback can involve restoring or rebuilding rather than simply switching a connection string. A side-by-side migration keeps a separate environment available for validation and controlled cutover, at the cost of duplicated capacity and synchronization work.

For either approach, define a reversible checkpoint before the new system accepts writes. After that point, the old environment may be stale, and returning to it needs a data-reconciliation strategy. Application schema changes and external integrations can independently make a simple rollback unsafe.

Compare the entire migration window: initial copy, catch-up, client cutover, performance observation and retirement. Side-by-side does not inherently mean zero downtime, and in-place does not inherently mean an unacceptable outage. Choose using measured timings, supported upgrade paths and an explicit failure plan rather than a preference for one label.

99. How would you stage a compatibility-level change after an engine upgrade?

Separate the engine migration from changes in optimizer behavior where the supported compatibility-level strategy allows it. Capture a representative Query Store baseline, validate storage and retention settings, then move a controlled workload to the intended compatibility level and compare outcomes.

Evaluate CPU, duration, logical reads, waits and resource use for important query families, not just an aggregate average. A reporting improvement can hide a regression in a short critical transaction. Record workload volume and parameter mix so a different traffic pattern is not mistaken for an engine effect.

Use supported Query Store and tuning controls for targeted mitigation, and keep a documented rollback decision for the compatibility change. This does not undo a database-format upgrade or every engine-level behavior change. Test correctness and application compatibility alongside performance; a fast plan is not sufficient evidence of a successful migration.

100. How would you roll out a cumulative update across an HA topology?

Use the documented procedure for the specific AG or FCI topology and the target update, including known issues and restart requirements. Common rolling patterns service secondary or passive capacity first, validate it, perform a controlled role change, and then service the former active node. Temporary mixed-build operation should remain within supported maintenance guidance.

Before each transition, verify synchronization, backup coverage, client routing and remaining redundancy. If the only eligible secondary is being restarted, the service may remain online while automatic recovery protection is reduced. State that exposure explicitly in the change plan.

After patching, test database health, Agent jobs, application transactions and a representative workload, then compare against the baseline. Major-version upgrade restrictions are not identical to a CU rollout, so do not copy failback assumptions between them. Keep a package-specific recovery plan instead of assuming every update can be safely uninstalled.

101. How does Accelerated Database Recovery change operational capacity planning?

ADR, introduced in SQL Server 2019 and improved in later releases, changes recovery and rollback behavior using row versions retained in a persistent version store within the database. It can reduce the impact of long-running transactions on recovery and log truncation, but it introduces storage and cleanup work that must be monitored.

A workload with long transactions and heavy updates can accumulate persistent versions. Track version-store growth, cleanup progress and the transactions retaining versions instead of interpreting every growth event as a log problem. SQL Server 2022 improves parts of this machinery, but does not eliminate the need for capacity planning.

ADR does not remove backup requirements or every reason that log reuse can be delayed, such as replication or availability-replica dependencies. Compare recovery benefits against workload-specific storage and I/O behavior in a representative test. A faster logical rollback does not mean all obsolete physical data is immediately removed.

102. How can a tighter indirect-checkpoint target affect a write-heavy workload?

Indirect checkpoints manage dirty-page flushing toward the database’s target recovery time. A tighter target can increase pressure to write dirty pages promptly, so a write-heavy workload near its storage limit may experience additional I/O contention. The target is not a promise that the complete application will recover within that interval.

Correlate checkpoint activity, dirty-page production, write latency and workload throughput before changing the setting. If storage is saturated, shortening the target may trade recovery work for worse foreground performance. If the application requires a shorter recovery time, capacity or workload changes may be needed as well.

Measure recovery under realistic dirty-page and transaction conditions, including other recovery dependencies. Do not tune solely from an idle restart or assume the server-level recovery interval and database-level target recovery time describe identical mechanisms. Record the previous setting and a rollback criterion for any change.

103. What does instant file initialization accelerate, and what are its limits?

For eligible data-file operations, instant file initialization avoids zero-filling newly allocated space. On Windows, data-file IFI depends on the service identity’s volume-maintenance privilege and other restrictions. It can improve creation, growth and restore-related allocation time, but it does not accelerate every part of a restore.

Historically transaction logs required zero initialization. Starting with SQL Server 2022, log autogrowth events up to 64 MB can benefit from IFI; larger log growth does not gain that benefit. This narrow exception should not be generalized into a claim that all log allocation is instantaneous. Data-file IFI and TDE also have different restrictions from the small-log-growth feature.

Review the security implications of reusing disk space without zeroing and grant the required privilege only through the approved service configuration. Pre-size files for expected demand instead of designing normal operation around frequent autogrowth. Measure allocation pauses with the real encryption and storage configuration.

104. How would you investigate storage latency without relying on a universal threshold?

Measure the affected operation, interval and workload baseline. SQL Server file-level I/O statistics expose cumulative counts and stall times; interval deltas can distinguish a current slowdown from old activity. Averages alone can hide burst latency, and SQL Server observations need correlation with operating-system and storage telemetry.

SELECT DB_NAME(database_id) AS database_name,
       file_id, num_of_reads, io_stall_read_ms,
       num_of_writes, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

SELECT DB_NAME(database_id) AS database_name,
       file_id, num_of_reads, io_stall_read_ms,
       num_of_writes, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

SELECT DB_NAME(database_id) AS database_name,
       file_id, num_of_reads, io_stall_read_ms,
       num_of_writes, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

Collect two snapshots with timestamps and account for a restart or counter reset. If read stall increases by 800 ms over 100 additional reads, the interval average is 8 ms per read; that calculation does not establish whether the workload meets its latency objective. For SQL Server 2022 and later, this DMV requires VIEW SERVER PERFORMANCE STATE. Investigate queueing, storage throttling, path issues and excessive I/O demand before attributing the problem to a single disk.

105. How do NUMA and runnable queues affect CPU-pressure diagnosis?

A runnable task is ready to execute but waiting for scheduler time. Persistent runnable queues can indicate CPU pressure, but the distribution across schedulers and NUMA nodes matters. One hot node or workload affinity issue can produce poor latency even when whole-server CPU averages look moderate.

Sample scheduler state over time and correlate it with operating-system CPU, virtualization limits, query CPU and workload placement. Check whether pressure is sustained or a short burst, and whether external processes are consuming CPU outside SQL Server. A single DMV snapshot is not a capacity study.

Do not immediately change affinity or parallelism settings because one queue is nonzero. Review the supported hardware topology and existing configuration first, then test a targeted change with a rollback plan. NUMA-local memory and scheduling behavior make aggregate percentages an incomplete description of available capacity.

106. Why can SQL Server run out of worker threads while CPU is not fully busy?

Workers can be occupied by blocked or waiting tasks, so thread exhaustion does not require every CPU to be saturated. Large connection bursts, parallel workloads and long blocking chains can consume the available workers and prevent new requests from progressing. THREADPOOL waits point to worker availability, not automatically to insufficient CPU.

Capture active requests, blocking relationships, connection growth and parallel-task patterns while the problem is occurring. Distinguish many sleeping connections from requests actually holding workers. If a head blocker is responsible, assess its transaction and rollback consequences before termination.

Increasing max worker threads without understanding the workload can add memory pressure and leave the cause unchanged. Consider application pool limits, admission control, shorter transactions and query changes where evidence supports them. Preserve an administrative access path and a minimal diagnostic procedure for incidents where normal connections struggle to start.

107. How does memory grant feedback change repeated-query resource use?

Memory grant feedback uses observed execution behavior to adjust later grants for an eligible cached plan. It can reduce excessive reservations or address insufficient grants that caused spills, but it does not retroactively resize a completed execution or replace investigation of poor estimates and workload design.

Batch-mode feedback arrived with SQL Server 2017 at compatibility level 140, and row-mode feedback with SQL Server 2019 at level 150. SQL Server 2022 adds persistence and percentile-based feedback; those capabilities require Query Store to be enabled and in read-write state. Check the documented compatibility and configuration prerequisites rather than assuming every query receives feedback after an engine upgrade.

A query alternating between tiny and very large inputs can make a single recent execution a poor guide to the next grant. Earlier feedback behavior can oscillate or disable itself for unstable requirements; percentile feedback addresses aspects of that variability but is not a guarantee of optimal allocation. Compare grant size, actual use, spills and concurrency over several representative executions. Plan replacement, cache eviction and Query Store state also affect whether learned information remains useful.

108. When should OPTIMIZE_FOR_SEQUENTIAL_KEY be tested for insert contention?

Test it when concurrent inserts converge on the last leaf page of an index with a sequential leading key and that page is a demonstrated bottleneck. The index option, introduced in SQL Server 2019, uses flow control to improve throughput under this contention pattern. It does not remove page latches or make every sequential-key workload faster.

For example, many writers inserting increasing order IDs can queue for the same index page. Identify the waiting page and index before changing the option: allocation-page contention in tempdb needs a different investigation. Compare completed inserts per second and application latency across realistic concurrency levels, rather than judging success only by whether a wait counter falls.

Observation

Decision implication

Low-concurrency inserts already meet latency targets

Do not enable the option merely because the key is sequential.

Throughput collapses as writers contend for one index leaf page

Test the option on that index with a controlled comparison.

BTREE_INSERT_FLOW_CONTROL waits appear after enabling it

Evaluate throughput and latency; this wait can reflect the intended admission mechanism.

Keep a reversal criterion if the workload does not benefit. Changing to a nonsequential key is another design choice, with consequences for locality, splits and existing access paths; it is not a free fix. Recheck both peak and ordinary traffic before retaining the configuration.

109. When should memory-optimized tempdb metadata be considered?

Consider it when measured contention is specifically on temporary-object metadata and materially limits the workload. The feature, introduced in SQL Server 2019, changes relevant tempdb system tables to memory-optimized structures. It does not convert ordinary temporary-table user data into memory-optimized tables or solve every tempdb bottleneck.

For the SQL Server 2022 baseline, check the edition feature matrix: memory-optimized tempdb metadata is an Enterprise capability, also available in the corresponding Developer feature set for nonproduction use. Confirm current-version and platform support rather than assuming that all products named SQL Server or Azure SQL expose the option.

Review documented limitations, memory consumption and the restart requirement before enabling it. Establish a before-and-after metadata-contention baseline and monitor the associated memory clerk. If the actual problem is allocation-page contention, storage latency or an oversized spill, this setting can add operational complexity without addressing the cause.

110. What can Resource Governor isolate, and what remains shared?

Resource Governor classifies sessions into workload groups and resource pools to manage supported CPU, query-memory and I/O consumption policies. It is useful for controlling competing workloads, but it is not a complete security or physical-isolation boundary. Some resources and background activity remain shared at the instance level.

For SQL Server 2022, Resource Governor is available in Enterprise and Developer editions; SQL Server 2025 adds support in Standard and its Developer edition and introduces additional tempdb governance. Do not present those later capabilities as a SQL Server 2022 Standard feature.

Test classifier behavior for real application connections and fail safely if classification fails. A CPU maximum is not the same as a hard cap under every configuration, and query-memory limits are not a universal limit on all memory used by a tenant. Verify each pool setting’s documented semantics and assess whether separate instances are needed for stronger isolation.

111. What operational costs remain when tables are memory-optimized?

Durable memory-optimized tables still require transaction logging and persistent checkpoint storage. Their rows must be loaded into memory during recovery, so sufficient memory and recovery throughput are part of the availability design. In-memory does not mean that backups, storage or restart planning disappear.

Distinguish SCHEMA_AND_DATA durability from SCHEMA_ONLY, where the table definition survives but row data does not survive restart. Use the latter only when the application can deliberately rebuild that state. An accidental durability assumption can turn a routine restart into data loss from the application’s perspective.

Plan for checkpoint-file lifecycle, backup size, memory growth and edition limits. Test recovery with representative data volume, not only steady-state query speed. A workload that performs well after warm-up may still miss its recovery objective if loading durable rows and rebuilding required structures takes too long.

112. How would you diagnose automatic AG seeding that does not complete?

Separate authorization, connectivity, capacity and throughput failures. Automatic seeding needs the AG’s documented permission to create databases on the secondary, working endpoints and sufficient storage. Successful replica connection does not prove the destination can create and populate the database.

Inspect seeding status and errors on the relevant replicas, destination paths, available space and competing I/O. A large database can consume substantial network and storage bandwidth while the primary continues generating log, so monitor catch-up pressure as well as the initial transfer. Avoid repeatedly restarting seeding without identifying why it fails.

After resolving the cause, use the supported retry procedure and verify the final synchronization state. Review the resulting database ownership and permissions rather than assuming every automatically created property matches the desired policy. For large or constrained environments, compare automatic seeding with a controlled backup-and-restore initialization plan.

113. What must a SQL Server ledger verification design protect outside the database?

SQL Server 2022 ledger provides tamper evidence, supported by previously generated database digests kept in independently protected storage. Verification recomputes hashes and compares them with those trusted digests. If an attacker can rewrite both database contents and the trusted digest record, that comparison loses its independent anchor.

For an order-history ledger, define who exports digests, where they are retained, who can change that storage and how verification failures are investigated. Test retrieving the historical digest after loss of the database host. Merely enabling a ledger table without an operational digest and verification process is incomplete evidence preservation.

Ledger is not a statement that every business transaction was legitimate. An authorized update to an updatable ledger table can remain correctly recorded in its history; verification does not decide whether the user should have approved that order. It also does not encrypt sensitive values or prevent all direct storage attacks. Retain access controls, auditing and backups, and state which ledger tables and historical digest points the verification covers.

114. How can certificate-signed modules support a least-privilege operational workflow?

A carefully designed signed module can receive specific permissions through a certificate-associated principal while callers receive only permission to execute the approved operation. This can reduce pressure to grant an automation account broad server roles simply to perform one maintenance or diagnostic task.

Define a narrow operation, validate its inputs and grant the certificate principal only the permissions required by that operation. Protect the signing key and review the module as privileged code. Altering the module removes its signature, so deployment must deliberately re-sign the reviewed definition and verify the signature afterward.

Test the workflow as the actual low-privilege execution identity, including forbidden alternatives. Keep service-account operating-system permissions, SQL execution permissions and certificate custody separate. Signing is not a blanket endorsement of arbitrary commands or a substitute for audit records and deployment control.

115. How would you detect configuration drift across SQL Server instances?

Maintain an approved baseline that records intentional differences by role, workload and version. Compare effective settings with that baseline and investigate changes rather than forcing every instance to identical values. A reporting server and an OLTP primary may legitimately need different configurations.

SELECT name, value AS configured_value,
       value_in_use AS running_value,
       is_dynamic, is_advanced
FROM sys.configurations
ORDER BY

SELECT name, value AS configured_value,
       value_in_use AS running_value,
       is_dynamic, is_advanced
FROM sys.configurations
ORDER BY

SELECT name, value AS configured_value,
       value_in_use AS running_value,
       is_dynamic, is_advanced
FROM sys.configurations
ORDER BY

Configured and running values can differ, including when a restart is required. Capture engine build, database-level settings, trace flags and relevant operating-system/service configuration separately; this catalog view is not a complete configuration export. Use an authorized monitoring identity with the applicable metadata visibility. Associate exceptions with an owner and review date, and test remediation before changing a production fleet.

116. How would you evaluate noisy-neighbor isolation for multiple tenants?

Start with the interference mechanism: CPU, query grants, tempdb, log throughput, storage I/O or shared administrative operations. A separate database can improve organization and permissions but still share instance resources. Stronger isolation may require separate instances or infrastructure, depending on the required service guarantee.

Isolation choice

Trade-off

Shared instance with workload controls

Efficient utilization but shared engine and some failure domains

Separate instances on shared host

More engine separation but host/storage contention remains

Separate capacity and failure domains

Stronger independence with greater operational cost

Test the largest tenant’s burst while measuring the smaller tenant’s critical latency, not only overall throughput. Define admission limits and a response when a tenant exceeds its budget. Backup, patching and restore operations also compete for capacity, so include them in the isolation assessment rather than benchmarking only queries.

117. How do physical throughput limits shape a very large database maintenance plan?

Estimate the volume that must be read, written or transferred and compare it with measured sustained throughput under realistic competing load. A maintenance window cannot be justified by CPU capacity alone when storage or network movement is the limiting factor.

For example, transferring 2 TiB at a sustained 250 MiB per second takes about 8,389 seconds, or 2.33 hours, before recovery, validation and setup. Compression can change transfer volume but adds CPU work, and restore throughput can differ from backup throughput. Treat this as a planning lower bound under the stated constant rate, not a delivery promise.

Plan file growth, log generation, replica catch-up and temporary space for the actual operation. Evaluate supported partition, online or resumable options where appropriate, with their edition and operation restrictions. Keep cancellation and recovery behavior in the runbook; stopping an operation is not always instantaneous or free.

118. What durability trade-off does delayed durability introduce?

Fully durable transactions, the default, acknowledge commit after the required transaction log records have been written to durable storage. Delayed durability can acknowledge commit while those records remain in memory, allowing batching that may reduce log-write latency. A crash can therefore lose transactions that clients were already told had committed.

A reconstructible telemetry workload might accept that loss in exchange for measured throughput, while a payment ledger may not. Establish whether log flush latency is actually the bottleneck and whether the business permits losing acknowledged work before considering the option. Periodic flushing should not be presented as an exact guaranteed maximum loss interval.

Review the database setting, transaction behavior and documented interactions with replication and availability features. Local delayed durability is a separate decision from synchronous versus asynchronous AG transport; the word synchronous in a replication configuration does not by itself prove that a delayed-durable commit was safely hardened. Validate the supported combination and failure semantics before deployment rather than enabling delayed durability as a general performance default.

119. How would you balance incident evidence collection with urgent mitigation?

Capture a small, time-bounded evidence set that can distinguish the leading causes, then apply the least disruptive supported mitigation with a clear owner and rollback criterion. Endless diagnostics can prolong an outage, while an immediate restart can erase the evidence needed to prevent recurrence.

Evidence

Purpose

Timestamped symptoms and affected clients

Define scope and correlate systems

Active waits, blocking and resource pressure

Separate concurrency, CPU, memory and I/O paths

Recent deployments and configuration changes

Identify reversible candidates

Logs and relevant session capture

Preserve error sequence before volatile state changes

Record the exact intervention and its time, then observe both service recovery and side effects. If a blocking session is terminated, monitor rollback rather than assuming capacity returns immediately. Protect sensitive query text and parameters in diagnostic artifacts, and defer expensive broad captures when their overhead would worsen the incident.

120. What evidence is needed before decommissioning a SQL Server instance?

Discover consumers and dependencies beyond currently active sessions: scheduled jobs, linked servers, reports, ETL, service accounts, monitoring and infrequent business processes. A quiet observation window does not prove that a month-end or annual workload no longer needs the instance.

Obtain ownership decisions for retained data, backups and encryption material, then verify that the replacement or archive can be restored and accessed through the approved process. Record engine/version requirements and the retention owner. Do not remove the only working key or installer knowledge needed to recover an old database later.

Use a controlled retirement sequence with a defined observation period and reversible connection cutover where appropriate. Disable or redirect dependencies deliberately, verify the outcome, and only then remove infrastructure and credentials according to the authorized plan. Final evidence should explain what moved, what remains recoverable and who owns future recovery requests.

MS SQL Documentation and Further Reading

These Microsoft references cover the engine architecture and operational features discussed above. Links select the SQL Server 2022 documentation view, while individual pages may also describe later releases. Check the applicability notes, edition matrix, platform restrictions and installed build before relying on a feature or permission requirement.

Team Sherlock.sh

Building fraud intelligence for live high-stake video interactions (financial discussions, hiring interviews, board meeting etc).

Catch candidate fraud before it costs you.

Sherlock AI helps you detect AI-assisted, proxy, and deepfake candidates, so every hiring decision is backed by evidence.

Ask AI about Sherlock.sh

Similar Posts