Scenario practice questions

A data engineer is designing a database for a sensor network that generates 20,000 readings per second. The schema is fixed, and each reading must be stored with minimal write latency while preserving the exact order of arrival for later time-series analysis. Which database structure should the engineer choose?

Trap 1: Document store with each sensor's readings embedded in a single…

Embedding readings in a growing document causes the document to be rewritten on every update, and document size limits will eventually be exceeded. Concurrent appends also create write contention on the same document. This approach cannot sustain 20,000 writes per second and does not guarantee the strict ordering needed for time-series analysis.

Trap 2: Graph database with sensor nodes connected by reading edges

A graph database excels at traversing complex relationships, but each reading would become a node or edge with its own storage and index overhead. At 20,000 readings per second, graph traversal structures add unnecessary cost, and the model does not naturally preserve linear arrival order for time-series queries, making it a poor fit.

Trap 3: Relational table with a composite primary key on sensor ID and…

A relational table with a composite key can enforce uniqueness and order logically, but row-by-row inserts at 20,000 per second introduce index maintenance and locking overhead. This design is better suited to moderate write volumes where transactional integrity matters more than raw ingest throughput, so it does not meet the minimal-latency requirement for this sensor stream.

A
Document store with each sensor's readings embedded in a single growing document
Why it fails: Embedding readings in a growing document causes the document to be rewritten on every update, and document size limits will eventually be exceeded. Concurrent appends also create write contention on the same document. This approach cannot sustain 20,000 writes per second and does not guarantee the strict ordering needed for time-series analysis.
B
Append-only log-structured store keyed by timestamp and sensor ID
An append-only log-structured store writes sequentially to disk, avoiding random I/O and in-place index updates, which keeps write latency low at high ingest rates. Keying entries by timestamp and sensor ID preserves arrival order and enables efficient range scans for time-series analysis, directly satisfying both the throughput and ordering requirements described.
C
Graph database with sensor nodes connected by reading edges
Why it fails: A graph database excels at traversing complex relationships, but each reading would become a node or edge with its own storage and index overhead. At 20,000 readings per second, graph traversal structures add unnecessary cost, and the model does not naturally preserve linear arrival order for time-series queries, making it a poor fit.
D
Relational table with a composite primary key on sensor ID and timestamp
Why it fails: A relational table with a composite key can enforce uniqueness and order logically, but row-by-row inserts at 20,000 per second introduce index maintenance and locking overhead. This design is better suited to moderate write volumes where transactional integrity matters more than raw ingest throughput, so it does not meet the minimal-latency requirement for this sensor stream.

Question 2hardmultiple choice

Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?

Trap 1: Key-Value

Key-value stores retrieve data by a single key with no traversal of relationships between records, so complex, frequently traversed relationships cannot be followed. It tempts for its speed on simple lookups, but graph databases are the correct choice for relationship-heavy queries.

Trap 2: Column-family

Column-family stores group related columns into families but do not model or traverse relationships between entities; graph databases do, using edges and index-free adjacency. It is tempting because column-family suits wide, sparse datasets queried by row key at scale, such as time-series or analytics workloads, not relationship traversal.

Trap 3: Document

Document stores group related fields into self-contained documents and query by document content, not by links between records, so traversing complex relationships is inefficient. It tempts because nested documents appear relational, but graph databases model traversals explicitly.

A
Graph
Graph databases store nodes and edges as first-class entities, so relationships are persisted directly rather than inferred through joins. Traversal follows pointers between adjacent nodes, making multi-hop queries across complex, frequently changing relationships far faster than relational or document alternatives.
B
Key-Value
Why it fails: Key-value stores retrieve data by a single key with no traversal of relationships between records, so complex, frequently traversed relationships cannot be followed. It tempts for its speed on simple lookups, but graph databases are the correct choice for relationship-heavy queries.
C
Column-family
Why it fails: Column-family stores group related columns into families but do not model or traverse relationships between entities; graph databases do, using edges and index-free adjacency. It is tempting because column-family suits wide, sparse datasets queried by row key at scale, such as time-series or analytics workloads, not relationship traversal.
D
Document
Why it fails: Document stores group related fields into self-contained documents and query by document content, not by links between records, so traversing complex relationships is inefficient. It tempts because nested documents appear relational, but graph databases model traversals explicitly.

Question 3mediummultiple choice

Which scenario best justifies the use of a relational database over a NoSQL database?

Trap 1: Big data analytics

Big data analytics favours NoSQL stores, which scale horizontally across commodity nodes and tolerate schema-less, high-volume writes. Relational engines enforce joins and ACID transactions on structured schemas, which constrains that scale. A relational database is the right choice for transactional workloads needing strict consistency, such as financial ledgers.

Trap 2: Rapid prototyping

Rapid prototyping benefits from schema-less NoSQL stores, which accept changing data shapes without migrations. Relational databases require predefined schemas and migration scripts before each structural change, slowing iteration. Relational databases are justified when the workload needs enforced relationships and ACID transactions across multiple tables.

Trap 3: Social media feeds

Social media feeds demand high write throughput and flexible, rapidly evolving schemas, which document or wide-column NoSQL stores handle through horizontal partitioning. Relational engines enforce fixed schemas and joins that bottleneck that access pattern. Relational databases suit scenarios requiring multi-row ACID transactions and referential integrity instead.

A
Financial transactions
Financial transactions demand ACID guarantees: atomicity, consistency, isolation and durability, enforced through strict schemas and multi-row transactions. A relational engine provides these natively, whereas most NoSQL stores trade full ACID compliance for horizontal scale and flexible schemas.
B
Big data analytics
Why it fails: Big data analytics favours NoSQL stores, which scale horizontally across commodity nodes and tolerate schema-less, high-volume writes. Relational engines enforce joins and ACID transactions on structured schemas, which constrains that scale. A relational database is the right choice for transactional workloads needing strict consistency, such as financial ledgers.
C
Rapid prototyping
Why it fails: Rapid prototyping benefits from schema-less NoSQL stores, which accept changing data shapes without migrations. Relational databases require predefined schemas and migration scripts before each structural change, slowing iteration. Relational databases are justified when the workload needs enforced relationships and ACID transactions across multiple tables.
D
Social media feeds
Why it fails: Social media feeds demand high write throughput and flexible, rapidly evolving schemas, which document or wide-column NoSQL stores handle through horizontal partitioning. Relational engines enforce fixed schemas and joins that bottleneck that access pattern. Relational databases suit scenarios requiring multi-row ACID transactions and referential integrity instead.

Question 4mediummultiple choice

An organization must ensure that PII in a legacy SQL Server database is masked before being accessed by the HR analytics team. Which SQL Server feature should the DBA implement to ensure the data is obscured at the query level without changing the underlying storage?

Trap 1: Transparent Data Encryption (TDE)

TDE encrypts data and log files at rest on disk, so queries still return cleartext PII to the HR analytics team. It is tempting because it protects backups and stolen media, and would be correct if the requirement were encryption at rest rather than query-level masking.

Trap 2: Row-Level Security (RLS)

RLS restricts which rows a user can see, not the content within the columns.

Trap 3: Always Encrypted

Always Encrypted encrypts column data at rest and in transit, with keys held outside SQL Server; it does not mask values at query level while leaving storage unchanged. It would be correct when the requirement is cryptographic protection of sensitive columns, not display masking.

A
Transparent Data Encryption (TDE)
Why it fails: TDE encrypts data and log files at rest on disk, so queries still return cleartext PII to the HR analytics team. It is tempting because it protects backups and stolen media, and would be correct if the requirement were encryption at rest rather than query-level masking.
B
Row-Level Security (RLS)
Why it fails: RLS restricts which rows a user can see, not the content within the columns.
C
Dynamic Data Masking (DDM)
Dynamic Data Masking applies masking rules at query execution, so the HR analytics team sees obfuscated values while the stored data remains unchanged on disk. This satisfies the stem's constraint of obscuring PII at the query level without altering underlying storage, unlike encryption or static masking, which modify data at rest.
D
Always Encrypted
Why it fails: Always Encrypted encrypts column data at rest and in transit, with keys held outside SQL Server; it does not mask values at query level while leaving storage unchanged. It would be correct when the requirement is cryptographic protection of sensitive columns, not display masking.

Question 5mediummulti select

Which TWO of the following are key considerations when configuring a database for high availability?

Trap 1: The color of the server chassis

Chassis colour is a physical aesthetic attribute with no relationship to replication, failover, or uptime. It is tempting because hardware selection genuinely matters for availability, so any server-related attribute can appear relevant, but colour never influences redundancy or recovery behaviour.

Trap 2: Number of database users

User count affects licensing and capacity planning, not the mechanisms that keep a database reachable, such as replication, clustering, or failover. It is tempting because scaling and load do influence performance, and user numbers are a common sizing input, but they do not deliver high availability.

Trap 3: The database name

The database name is an identifier used for connection strings and object qualification; it has no bearing on failover, replication, or redundancy. It is tempting because naming conventions matter during deployment, and the name is required when creating the database, but it does not affect availability.

A
The color of the server chassis
Why it fails: Chassis colour is a physical aesthetic attribute with no relationship to replication, failover, or uptime. It is tempting because hardware selection genuinely matters for availability, so any server-related attribute can appear relevant, but colour never influences redundancy or recovery behaviour.
B
Number of database users
Why it fails: User count affects licensing and capacity planning, not the mechanisms that keep a database reachable, such as replication, clustering, or failover. It is tempting because scaling and load do influence performance, and user numbers are a common sizing input, but they do not deliver high availability.
C
Network latency between nodes
Replication and failover between nodes depend on timely heartbeat and synchronisation traffic, so high network latency delays detection of failure and risks stale reads. This directly satisfies the high-availability requirement by ensuring rapid, consistent failover.
D
The database name
Why it fails: The database name is an identifier used for connection strings and object qualification; it has no bearing on failover, replication, or redundancy. It is tempting because naming conventions matter during deployment, and the name is required when creating the database, but it does not affect availability.
E
Storage subsystem performance
Storage subsystem performance directly determines whether replicas can keep pace with primary write throughput; insufficient IOPS or latency causes replica lag, undermining failover readiness. Satisfying the high-availability constraint therefore requires provisioning storage that sustains peak transaction load across all nodes, not merely the primary.

Question 6hardmulti select

Which TWO of the following scenarios are best suited for using a 'View'?

Trap 1: Storing large binary objects

Views execute stored SELECT queries returning rows and columns; they cannot hold binary large objects, which belong in tables or dedicated storage. A view would be correct when exposing a restricted, pre-joined or aggregated subset of table data to users without granting base-table access.

Trap 2: Improving write speed

A view is a stored query that resolves to a SELECT at runtime, adding no write path, so it cannot improve INSERT, UPDATE or DELETE throughput. It is tempting because views can simplify data access, and would be correct when you need to present a stable, filtered interface over underlying tables.

Trap 3: Generating unique sequences

A view is a stored SELECT statement, so it cannot generate unique sequences; that requires a sequence object or an IDENTITY column. It is tempting because views do encapsulate reusable query logic, and would be correct when you need to simplify recurring joins or restrict exposed columns.

A
Simplifying complex joins
A view stores a predefined SELECT statement, so the join logic is written once and reused. This directly satisfies the scenario's need to simplify complex joins, hiding multi-table syntax behind a single named object that users query as if it were one table.
B
Storing large binary objects
Why it fails: Views execute stored SELECT queries returning rows and columns; they cannot hold binary large objects, which belong in tables or dedicated storage. A view would be correct when exposing a restricted, pre-joined or aggregated subset of table data to users without granting base-table access.
C
Improving write speed
Why it fails: A view is a stored query that resolves to a SELECT at runtime, adding no write path, so it cannot improve INSERT, UPDATE or DELETE throughput. It is tempting because views can simplify data access, and would be correct when you need to present a stable, filtered interface over underlying tables.
D
Implementing security access
A view can expose only selected columns or rows while restricting access to the underlying base tables, letting administrators grant query permissions without revealing sensitive data. This satisfies the stem's security requirement, unlike using views purely for simplifying complex joins or formatting output.
E
Generating unique sequences
Why it fails: A view is a stored SELECT statement, so it cannot generate unique sequences; that requires a sequence object or an IDENTITY column. It is tempting because views do encapsulate reusable query logic, and would be correct when you need to simplify recurring joins or restrict exposed columns.

Question 7hardmultiple choice

An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?

Trap 1: Automatic indexing of the result set

A CTE is an inline named result set evaluated at query time; the database creates no indexes on it automatically. It is tempting because materialisation can occur, but that stores rows without indexing them. The actual benefit is improved readability and reuse of the pre-aggregated result within the query.

Trap 2: Performance is always superior to subqueries

A CTE is inlined and evaluated like a derived table, so the optimiser may produce an identical plan; no guarantee of superiority exists. The claim is tempting because CTEs improve readability and can be reused, which would justify them when the same aggregate feeds several joins.

Trap 3: Lower memory consumption during execution

A CTE is typically materialised or inlined by the optimiser, consuming comparable or greater memory than an equivalent subquery, so reduced memory is not guaranteed. They are tempting because materialisation can cache a reused result set, which helps when the same aggregate is referenced repeatedly rather than once.

A
Better readability and recursive capability
A CTE names the pre-aggregated result set, separating it from the join logic so the query reads top-down and is easier to maintain. It also supports recursion, unlike a plain subquery, which satisfies the readability and recursive capability requirement.
B
Automatic indexing of the result set
Why it fails: A CTE is an inline named result set evaluated at query time; the database creates no indexes on it automatically. It is tempting because materialisation can occur, but that stores rows without indexing them. The actual benefit is improved readability and reuse of the pre-aggregated result within the query.
C
Performance is always superior to subqueries
Why it fails: A CTE is inlined and evaluated like a derived table, so the optimiser may produce an identical plan; no guarantee of superiority exists. The claim is tempting because CTEs improve readability and can be reused, which would justify them when the same aggregate feeds several joins.
D
Lower memory consumption during execution
Why it fails: A CTE is typically materialised or inlined by the optimiser, consuming comparable or greater memory than an equivalent subquery, so reduced memory is not guaranteed. They are tempting because materialisation can cache a reused result set, which helps when the same aggregate is referenced repeatedly rather than once.

Question 8hardmultiple choice

A database administrator is designing a table to store product inventory. The table must enforce that each product has a unique SKU and that the quantity_on_hand column never drops below zero. The administrator adds a PRIMARY KEY constraint on SKU and a CHECK constraint on quantity_on_hand. A developer then tries to insert a row with a NULL SKU and a negative quantity. Which statement describes the outcome?

Trap 1: The insert succeeds because CHECK constraints are evaluated only on…

CHECK constraints are evaluated on both INSERT and UPDATE operations. They ensure that data meets the specified condition whenever a row is added or modified. In this scenario, the negative quantity_on_hand would violate the CHECK constraint regardless of the NULL SKU, so the insert would not succeed. This option misrepresents the timing of CHECK constraint enforcement in standard SQL databases.

Trap 2: The insert fails only because of the negative quantity; the NULL…

This option contains two errors: PRIMARY KEY does enforce NOT NULL, and the negative quantity is indeed rejected by the CHECK constraint. The insert fails for both reasons. The misconception that PRIMARY KEY allows NULLs is a common mistake, but it contradicts the SQL standard and the behavior of databases like PostgreSQL, SQL Server, Oracle, and MySQL, all of which reject NULLs in primary key columns.

Trap 3: The insert succeeds with a warning because NULL values bypass…

In standard SQL, PRIMARY KEY columns cannot contain NULL values. Some database systems may allow a single NULL in a UNIQUE column, but PRIMARY KEY is stricter and always enforces NOT NULL. Therefore, a NULL SKU would cause a constraint violation, not a warning. This option incorrectly suggests that PRIMARY KEY constraints are bypassed for NULLs, which is not true in any major relational database.

A
The insert succeeds because CHECK constraints are evaluated only on UPDATE, not INSERT.
Why it fails: CHECK constraints are evaluated on both INSERT and UPDATE operations. They ensure that data meets the specified condition whenever a row is added or modified. In this scenario, the negative quantity_on_hand would violate the CHECK constraint regardless of the NULL SKU, so the insert would not succeed. This option misrepresents the timing of CHECK constraint enforcement in standard SQL databases.
B
The insert fails only because of the negative quantity; the NULL SKU is allowed because PRIMARY KEY does not enforce NOT NULL.
Why it fails: This option contains two errors: PRIMARY KEY does enforce NOT NULL, and the negative quantity is indeed rejected by the CHECK constraint. The insert fails for both reasons. The misconception that PRIMARY KEY allows NULLs is a common mistake, but it contradicts the SQL standard and the behavior of databases like PostgreSQL, SQL Server, Oracle, and MySQL, all of which reject NULLs in primary key columns.
C
The insert fails because the PRIMARY KEY constraint rejects NULL values, and the CHECK constraint rejects negative values.
A PRIMARY KEY constraint implicitly includes NOT NULL, so a NULL SKU is rejected. Separately, the CHECK constraint on quantity_on_hand rejects the negative value. Both violations occur, and the database will report an error for at least one of them, preventing the insert. This is the correct behavior because both constraints are enforced at insert time in standard SQL, ensuring data integrity for unique and non-negative inventory values.
D
The insert succeeds with a warning because NULL values bypass PRIMARY KEY enforcement.
Why it fails: In standard SQL, PRIMARY KEY columns cannot contain NULL values. Some database systems may allow a single NULL in a UNIQUE column, but PRIMARY KEY is stricter and always enforces NOT NULL. Therefore, a NULL SKU would cause a constraint violation, not a warning. This option incorrectly suggests that PRIMARY KEY constraints are bypassed for NULLs, which is not true in any major relational database.

Question 9easymultiple choice

A retail company is implementing a new data classification policy. The security team needs to categorize data based on its sensitivity and the impact of unauthorized disclosure. Which data classification level typically requires the highest level of protection and is subject to the strictest access controls?

Trap 1: Public

Public data is intended for open disclosure and requires minimal protection. It is not sensitive and unauthorized disclosure would have little to no impact. Therefore, it does not require the highest level of protection or strict access controls. This classification is at the opposite end of the sensitivity spectrum from what the scenario describes.

Trap 2: Internal

Internal data is meant for use within the organization and may cause minor embarrassment or operational impact if disclosed. However, it is not typically subject to the strictest access controls or the highest level of protection. It represents a moderate level of sensitivity, below confidential or restricted data.

Trap 3: Confidential

Confidential data is sensitive and requires protection, but it is often not the highest level. In many classification schemes, 'Confidential' is a middle tier, above Internal but below Restricted or Top Secret. Unauthorized disclosure could cause significant harm, but the strictest controls are usually reserved for a higher classification level.

A
Public
Why it fails: Public data is intended for open disclosure and requires minimal protection. It is not sensitive and unauthorized disclosure would have little to no impact. Therefore, it does not require the highest level of protection or strict access controls. This classification is at the opposite end of the sensitivity spectrum from what the scenario describes.
B
Internal
Why it fails: Internal data is meant for use within the organization and may cause minor embarrassment or operational impact if disclosed. However, it is not typically subject to the strictest access controls or the highest level of protection. It represents a moderate level of sensitivity, below confidential or restricted data.
C
Restricted
Restricted data is typically the highest classification level in many data classification policies. It includes highly sensitive information such as trade secrets, personally identifiable information (PII), or protected health information (PHI). Unauthorized disclosure could cause severe legal, financial, or reputational damage. Therefore, it requires the strictest access controls, encryption, and monitoring.
D
Confidential
Why it fails: Confidential data is sensitive and requires protection, but it is often not the highest level. In many classification schemes, 'Confidential' is a middle tier, above Internal but below Restricted or Top Secret. Unauthorized disclosure could cause significant harm, but the strictest controls are usually reserved for a higher classification level.

Question 10mediummultiple choice

A data engineer is designing a table that stores sensor readings. Each row must include the sensor ID, a timestamp, and a numeric reading. The sensor ID and timestamp together uniquely identify each row, and the table will be queried frequently by sensor ID for the most recent reading. Which table design best supports this scenario?

Trap 1: Create a clustered index on reading_time and a separate…

A clustered index on reading_time orders the table by time rather than by sensor, which may help time-range queries but not per-sensor latest-reading queries. A separate nonclustered index on sensor_id alone does not include reading_time, so retrieving the latest reading per sensor still requires additional lookups or sorts. This design is less effective than a composite index.

Trap 2: Create a primary key on sensor_id only and rely on application…

A primary key on sensor_id alone prohibits multiple readings per sensor, which contradicts the requirement to store sensor readings over time. Relying on application logic for timestamp uniqueness is unreliable and violates data integrity principles. This design cannot store the required historical data.

Trap 3: Create a surrogate integer primary key and no additional indexes on…

A surrogate key provides uniqueness but does not enforce the natural uniqueness of sensor_id and reading_time. Without indexes on sensor_id or reading_time, queries for the latest reading per sensor require full table scans or inefficient sorts. This design fails to support the frequent query pattern and may allow duplicate sensor readings.

A
Create a clustered index on reading_time and a separate nonclustered index on sensor_id.
Why it fails: A clustered index on reading_time orders the table by time rather than by sensor, which may help time-range queries but not per-sensor latest-reading queries. A separate nonclustered index on sensor_id alone does not include reading_time, so retrieving the latest reading per sensor still requires additional lookups or sorts. This design is less effective than a composite index.
B
Create a primary key on sensor_id only and rely on application logic to prevent duplicate timestamps.
Why it fails: A primary key on sensor_id alone prohibits multiple readings per sensor, which contradicts the requirement to store sensor readings over time. Relying on application logic for timestamp uniqueness is unreliable and violates data integrity principles. This design cannot store the required historical data.
C
Create a composite primary key on (sensor_id, reading_time) and add a nonclustered index on (sensor_id, reading_time DESC).
A composite primary key on sensor_id and reading_time enforces uniqueness for each sensor reading. Adding a nonclustered index on sensor_id and reading_time descending allows the query optimizer to efficiently retrieve the most recent reading per sensor. This design balances data integrity with query performance for the stated access pattern.
D
Create a surrogate integer primary key and no additional indexes on sensor_id or reading_time.
Why it fails: A surrogate key provides uniqueness but does not enforce the natural uniqueness of sensor_id and reading_time. Without indexes on sensor_id or reading_time, queries for the latest reading per sensor require full table scans or inefficient sorts. This design fails to support the frequent query pattern and may allow duplicate sensor readings.

Question 11hardmulti select

A database administrator is planning a new SQL Server 2022 deployment that must support a read-only reporting workload without impacting the primary OLTP workload. The reporting queries are long-running and scan large tables. Which two features should the administrator implement to meet this requirement? (Choose two.)

Trap 1: Enable snapshot isolation on the primary database to reduce…

Snapshot isolation uses row versioning in tempdb to avoid reader-writer blocking, but the reporting queries still execute on the primary instance and consume its CPU, memory, and I/O. It reduces contention but does not isolate the reporting workload, so it does not meet the goal of protecting OLTP performance from long-running scans.

Trap 2: Configure transactional replication to push data to a separate…

Transactional replication can feed a separate reporting server, but it adds latency, requires ongoing maintenance of publications and subscriptions, and does not include the columnstore acceleration that makes large scans efficient. It is a valid alternative architecture but is more complex than an availability group readable secondary and does not by itself optimize the scan performance described.

Trap 3: Enable Resource Governor to cap CPU usage for reporting sessions.

Resource Governor limits the CPU and memory a workload group can consume, which prevents reporting from starving OLTP, but the queries still run on the primary instance and compete for I/O and tempdb. It is a throttling mechanism, not an isolation architecture, and it does not provide the dedicated read-only environment the scenario requires.

A
Configure an Always On availability group with a readable secondary replica.
An Always On availability group can host a secondary replica configured to allow read-only connections, offloading reporting queries from the primary. Long-running scans then execute on the secondary, preserving primary CPU and I/O for OLTP transactions. This directly satisfies the requirement to isolate reporting from the transactional workload without additional licensing for a separate reporting server.
B
Enable snapshot isolation on the primary database to reduce blocking.
Why it fails: Snapshot isolation uses row versioning in tempdb to avoid reader-writer blocking, but the reporting queries still execute on the primary instance and consume its CPU, memory, and I/O. It reduces contention but does not isolate the reporting workload, so it does not meet the goal of protecting OLTP performance from long-running scans.
C
Create a columnstore index on the large fact tables used by reporting.
A clustered or nonclustered columnstore index stores data column-wise and compresses it heavily, dramatically accelerating the large table scans typical of reporting queries. Combined with a readable secondary, it reduces the resources each scan consumes. It is a valid complementary feature for analytical workloads on SQL Server and directly supports efficient reporting.
D
Configure transactional replication to push data to a separate reporting server.
Why it fails: Transactional replication can feed a separate reporting server, but it adds latency, requires ongoing maintenance of publications and subscriptions, and does not include the columnstore acceleration that makes large scans efficient. It is a valid alternative architecture but is more complex than an availability group readable secondary and does not by itself optimize the scan performance described.
E
Enable Resource Governor to cap CPU usage for reporting sessions.
Why it fails: Resource Governor limits the CPU and memory a workload group can consume, which prevents reporting from starving OLTP, but the queries still run on the primary instance and compete for I/O and tempdb. It is a throttling mechanism, not an isolation architecture, and it does not provide the dedicated read-only environment the scenario requires.