Exam Bundle

A controlled integrity check completes with a documented clean result or produces an actionable corruption report. That result is evidence for another corrective change: Schedule supported database integrity validation and define remediation and restore responses for corruption findings. The alternative problem is: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. The current Meadowbrook Finance issue is instead: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. Its decisive acceptance evidence would be: The host maintains acceptable available memory and paging while SQL workload throughput meets measured targets.

A controlled repeated query workload shows fewer memory spills with correct results and no unacceptable regression. That result is evidence for another corrective change: Evaluate supported intelligent query processing features such as memory grant feedback under a tested compatibility level. The alternative problem is: A report repeatedly underestimates intermediate row counts and spills to tempdb because the database uses an older compatibility level unnecessarily. The current Meadowbrook Finance issue is instead: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. Its decisive acceptance evidence would be: The host maintains acceptable available memory and paging while SQL workload throughput meets measured targets.

A controlled demand increase triggers an alert before service limits are breached and the approved scaling action is auditable. That result is evidence for another corrective change: Set proactive capacity alerts and governed scaling triggers based on CPU, I/O, storage and available tier capabilities. The alternative problem is: A team configures database auto-scaling only after a critical application has begun failing because it did not set any workload thresholds. The current Meadowbrook Finance issue is instead: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. Its decisive acceptance evidence would be: The host maintains acceptable available memory and paging while SQL workload throughput meets measured targets.

Choose appropriate compute or storage scaling rules and evaluate tier features and transition effects against the workload schedule. That relates to a separate SQL administration problem: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. The Meadowbrook Finance requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Update relevant statistics using suitable sampling and schedule triggers based on data-change patterns and affected queries. That relates to a separate SQL administration problem: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. The Meadowbrook Finance requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Benchmark index effects against representative reads and writes and adopt only a change with acceptable total workload impact. That relates to a separate SQL administration problem: An automatic tuning advisor proposes an index that speeds one query but increases insert cost substantially, and the DBA accepts it without validating overall load. The Meadowbrook Finance requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window. That result is evidence for another corrective change: Use measured fragmentation, page count, workload effects and supported maintenance guidance to choose a targeted index maintenance policy. The alternative problem is: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. The current Meadowbrook Finance issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

The test plan shows the supported inlining or refactor effect and CPU time falls without changing query results. That result is evidence for another corrective change: Check compatibility and eligibility for supported scalar UDF inlining or refactor the function to an efficient set-based expression. The alternative problem is: A query uses repeated scalar UDF calls and creates excessive row-by-row execution overhead even though intelligent query processing may help. The current Meadowbrook Finance issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

The host maintains acceptable available memory and paging while SQL workload throughput meets measured targets. That result is evidence for another corrective change: Reserve adequate operating-system and auxiliary-service memory and configure supported max server memory for the SQL instance. The alternative problem is: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. The current Meadowbrook Finance issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Update relevant statistics using suitable sampling and schedule triggers based on data-change patterns and affected queries. That relates to a separate SQL administration problem: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. The Westhaven Retail requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Evaluate and configure supported automatic tuning settings and monitor each action for workload benefit or rollback. That relates to a separate SQL administration problem: An Azure SQL Database with supported automatic tuning is left unmanaged despite repeated regressions and validated index recommendations. The Westhaven Retail requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Evaluate supported intelligent query processing features such as memory grant feedback under a tested compatibility level. That relates to a separate SQL administration problem: A report repeatedly underestimates intermediate row counts and spills to tempdb because the database uses an older compatibility level unnecessarily. The Westhaven Retail requirement is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. That approach does not directly establish the required fix; the requested acceptance evidence is: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan. That result is evidence for another corrective change: Choose appropriate compute or storage scaling rules and evaluate tier features and transition effects against the workload schedule. The alternative problem is: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. The current Westhaven Retail issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

The target database uses the intended setting while an unrelated database retains its original optimizer behavior. That result is evidence for another corrective change: Evaluate supported database-scoped configuration or query-level hints rather than unnecessary global engine setting changes. The alternative problem is: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. The current Westhaven Retail issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

A controlled integrity check completes with a documented clean result or produces an actionable corruption report. That result is evidence for another corrective change: Schedule supported database integrity validation and define remediation and restore responses for corruption findings. The alternative problem is: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. The current Westhaven Retail issue is instead: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. Its decisive acceptance evidence would be: A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target.

Set proactive capacity alerts and governed scaling triggers based on CPU, I/O, storage and available tier capabilities. That relates to a separate SQL administration problem: A team configures database auto-scaling only after a critical application has begun failing because it did not set any workload thresholds. The Westhaven Retail requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Check compatibility and eligibility for supported scalar UDF inlining or refactor the function to an efficient set-based expression. That relates to a separate SQL administration problem: A query uses repeated scalar UDF calls and creates excessive row-by-row execution overhead even though intelligent query processing may help. The Westhaven Retail requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Benchmark index effects against representative reads and writes and adopt only a change with acceptable total workload impact. That relates to a separate SQL administration problem: An automatic tuning advisor proposes an index that speeds one query but increases insert cost substantially, and the DBA accepts it without validating overall load. The Westhaven Retail requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target. That result is evidence for another corrective change: Configure Resource Governor workload groups and classification appropriate to the instance edition and workload objectives. The alternative problem is: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. The current Westhaven Retail issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

The post-change workload meets read latency while insert, update and maintenance overhead remain inside agreed limits. That result is evidence for another corrective change: Benchmark index effects against representative reads and writes and adopt only a change with acceptable total workload impact. The alternative problem is: An automatic tuning advisor proposes an index that speeds one query but increases insert cost substantially, and the DBA accepts it without validating overall load. The current Westhaven Retail issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

The host maintains acceptable available memory and paging while SQL workload throughput meets measured targets. That result is evidence for another corrective change: Reserve adequate operating-system and auxiliary-service memory and configure supported max server memory for the SQL instance. The alternative problem is: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. The current Westhaven Retail issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Plan selective index maintenance and log sizing with observed change volume and appropriate transaction log I/O capacity. That relates to a separate SQL administration problem: A database job performs frequent full-index rebuilds but the associated transaction log grows faster than its configured throughput can handle. The Brookside Manufacturing requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Set proactive capacity alerts and governed scaling triggers based on CPU, I/O, storage and available tier capabilities. That relates to a separate SQL administration problem: A team configures database auto-scaling only after a critical application has begun failing because it did not set any workload thresholds. The Brookside Manufacturing requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Configure Resource Governor workload groups and classification appropriate to the instance edition and workload objectives. That relates to a separate SQL administration problem: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. The Brookside Manufacturing requirement is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. That approach does not directly establish the required fix; the requested acceptance evidence is: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

A controlled integrity check completes with a documented clean result or produces an actionable corruption report. That result is evidence for another corrective change: Schedule supported database integrity validation and define remediation and restore responses for corruption findings. The alternative problem is: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. The current Brookside Manufacturing issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

The post-change workload meets read latency while insert, update and maintenance overhead remain inside agreed limits. That result is evidence for another corrective change: Benchmark index effects against representative reads and writes and adopt only a change with acceptable total workload impact. The alternative problem is: An automatic tuning advisor proposes an index that speeds one query but increases insert cost substantially, and the DBA accepts it without validating overall load. The current Brookside Manufacturing issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target. That result is evidence for another corrective change: Configure Resource Governor workload groups and classification appropriate to the instance edition and workload objectives. The alternative problem is: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. The current Brookside Manufacturing issue is instead: A database owner changes instance-wide optimizer settings to solve one compatibility-sensitive database problem that could be scoped locally. Its decisive acceptance evidence would be: The target database uses the intended setting while an unrelated database retains its original optimizer behavior.

Configure Resource Governor workload groups and classification appropriate to the instance edition and workload objectives. That relates to a separate SQL administration problem: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. The Brookside Manufacturing requirement is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. That approach does not directly establish the required fix; the requested acceptance evidence is: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.

Set proactive capacity alerts and governed scaling triggers based on CPU, I/O, storage and available tier capabilities. That relates to a separate SQL administration problem: A team configures database auto-scaling only after a critical application has begun failing because it did not set any workload thresholds. The Brookside Manufacturing requirement is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. That approach does not directly establish the required fix; the requested acceptance evidence is: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.

Benchmark index effects against representative reads and writes and adopt only a change with acceptable total workload impact. That relates to a separate SQL administration problem: An automatic tuning advisor proposes an index that speeds one query but increases insert cost substantially, and the DBA accepts it without validating overall load. The Brookside Manufacturing requirement is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. That approach does not directly establish the required fix; the requested acceptance evidence is: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.

A concurrency test constrains reporting resource consumption while the protected transactional group's latency stays within target. That result is evidence for another corrective change: Configure Resource Governor workload groups and classification appropriate to the instance edition and workload objectives. The alternative problem is: A shared SQL Server instance runs several resource-intensive reporting workloads with no protection for a latency-critical transactional group. The current Brookside Manufacturing issue is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. Its decisive acceptance evidence would be: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.

After adjustment the maintenance operation completes without sustained log saturation and log growth is bounded. That result is evidence for another corrective change: Plan selective index maintenance and log sizing with observed change volume and appropriate transaction log I/O capacity. The alternative problem is: A database job performs frequent full-index rebuilds but the associated transaction log grows faster than its configured throughput can handle. The current Brookside Manufacturing issue is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. Its decisive acceptance evidence would be: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.

The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window. That result is evidence for another corrective change: Use measured fragmentation, page count, workload effects and supported maintenance guidance to choose a targeted index maintenance policy. The alternative problem is: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. The current Brookside Manufacturing issue is instead: A large database is manually kept on expensive peak compute despite a predictable nightly analytics load and low daytime activity. Its decisive acceptance evidence would be: Planned scaling meets the overnight workload goal while daytime allocations and costs follow the documented capacity plan.