Exam Bundle

The identified lead blocker is corrected and the blocked sessions complete while unrelated transactions are left running. That result is evidence for another corrective change: Inspect active requests, session wait information and blocking chains using supported DMVs or Extended Events. The alternative problem is: Two SQL sessions are blocked for minutes and the incident handler kills random connections without identifying the lead blocking transaction. The current Westhaven Retail issue is instead: A high-concurrency app hits deadlocks but operations treats them as ordinary slow queries and fails to capture the deadlock graph. Its decisive acceptance evidence would be: A concurrency replay no longer produces the original deadlock cycle and the app handles any remaining retries predictably.

The affected query returns to the approved runtime profile and a forced-plan health check detects no unsupported plan behavior. That result is evidence for another corrective change: Use Query Store performance evidence and a supported plan forcing or regression correction after validating applicability. The alternative problem is: A monitoring team assumes all slow requests come from missing indexes but Query Store identifies a sudden plan regression without a schema change. The current Westhaven Retail issue is instead: A high-concurrency app hits deadlocks but operations treats them as ordinary slow queries and fails to capture the deadlock graph. Its decisive acceptance evidence would be: A concurrency replay no longer produces the original deadlock cycle and the app handles any remaining retries predictably.

The diagnosed metric and affected query recover in a follow-up observation while unrelated databases retain their original sizing. That result is evidence for another corrective change: Review the reported root-cause context and correlate it with Query Store and platform telemetry before implementing a targeted fix. The alternative problem is: Azure SQL Intelligent Insights reports degraded database performance but administrators ignore its diagnostic context and scale every database equally. The current Westhaven Retail issue is instead: A high-concurrency app hits deadlocks but operations treats them as ordinary slow queries and fails to capture the deadlock graph. Its decisive acceptance evidence would be: A concurrency replay no longer produces the original deadlock cycle and the app handles any remaining retries predictably.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Westhaven Retail requirement is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Westhaven Retail issue is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Brookside Manufacturing requirement is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Brookside Manufacturing requirement is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. That approach does not directly establish the required fix; the requested acceptance evidence is: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Brookside Manufacturing issue is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

The automatic recommendation reports implemented status and representative query performance improves without unacceptable write regression. That result is evidence for another corrective change: Evaluate and configure supported automatic tuning settings and monitor each action for workload benefit or rollback. The alternative problem is: An Azure SQL Database with supported automatic tuning is left unmanaged despite repeated regressions and validated index recommendations. The current Brookside Manufacturing issue is instead: A fragmented heavily updated index is rebuilt every hour regardless of its size or actual query benefit, causing unnecessary log and CPU activity. Its decisive acceptance evidence would be: The chosen index receives maintenance only when justified and application response times remain stable during the scheduled window.

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 Brookside Manufacturing requirement is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Brookside Manufacturing requirement is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Brookside Manufacturing issue is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Brookside Manufacturing issue is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Granite SaaS requirement is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Granite SaaS requirement is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

Reserve adequate operating-system and auxiliary-service memory and configure supported max server memory for the SQL instance. That relates to a separate SQL administration problem: An SQL Server VM uses default max server memory so database memory pressure causes the Windows host to page heavily under load. The Granite SaaS requirement is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. That approach does not directly establish the required fix; the requested acceptance evidence is: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

The automatic recommendation reports implemented status and representative query performance improves without unacceptable write regression. That result is evidence for another corrective change: Evaluate and configure supported automatic tuning settings and monitor each action for workload benefit or rollback. The alternative problem is: An Azure SQL Database with supported automatic tuning is left unmanaged despite repeated regressions and validated index recommendations. The current Granite SaaS issue is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Granite SaaS issue is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Granite SaaS issue is instead: A query optimizer chooses poor cardinality estimates because frequently modified key columns retain outdated statistics and no maintenance policy exists. Its decisive acceptance evidence would be: Estimated row counts more closely match actual results and the affected query plan improves after statistics maintenance.

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 Granite SaaS requirement is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. That approach does not directly establish the required fix; the requested acceptance evidence is: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.

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 Granite SaaS requirement is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. That approach does not directly establish the required fix; the requested acceptance evidence is: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.

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 Granite SaaS requirement is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. That approach does not directly establish the required fix; the requested acceptance evidence is: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.

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 Granite SaaS issue is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. Its decisive acceptance evidence would be: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.

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 Granite SaaS issue is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. Its decisive acceptance evidence would be: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.

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 Granite SaaS issue is instead: A production SQL Server has never run DBCC CHECKDB and a backup success email is treated as proof of database structural integrity. Its decisive acceptance evidence would be: A controlled integrity check completes with a documented clean result or produces an actionable corruption report.