100% Latest & Updated Microsoft DP-300 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 330
Three independently confirmed SQL platform issues require separate evidence checks. Case A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Case B: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Case C: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. Select exactly THREE distinct diagnostic checks, one for each case.
- Automatic tuning recommendation state and validation outcome
- DBCC CHECKDB results and regular integrity-check history
- Compression savings estimate together with measured update CPU and I/O
- Sustained CPU utilization and workload throughput after tuning
- Database-scoped configuration and observed parallel plan degree
- sys.dm_db_index_physical_stats and query latency baseline
Explanation
Correct Answers
C:Compression savings estimate together with measured update CPU and I/O directly tests the observation that an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. It helps the team evaluate whether it should choose compression granularity based on actual storage and CPU tradeoffs. The check has a boundary: Compression benefits vary by data patterns and modification rate.
D:Sustained CPU utilization and workload throughput after tuning directly tests the observation that a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. It helps the team evaluate whether it should scale the supported Azure SQL compute size to a measured capacity requirement. The check has a boundary: Vertical scaling does not fix nonselective application queries automatically.
F:sys.dm_db_index_physical_stats and query latency baseline directly tests the observation that a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. It helps the team evaluate whether it should select appropriate index reorganize or rebuild maintenance based on workload evidence. The check has a boundary: Rebuilding every index on a schedule may waste resources or harm concurrency.
Incorrect Answers
A:Automatic tuning recommendation state and validation outcome is appropriate for the separate condition that a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. The actual 3 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized; C: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. This option is not the specific diagnostic evidence for any of those stated cases.
B:DBCC CHECKDB results and regular integrity-check history is appropriate for the separate condition that a recovery plan requires confidence that SQL Server database structures are consistent. The actual 3 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized; C: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. This option is not the specific diagnostic evidence for any of those stated cases.
E:Database-scoped configuration and observed parallel plan degree is appropriate for the separate condition that a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. The actual 3 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized; C: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. This option is not the specific diagnostic evidence for any of those stated cases.
Question 331
A database architecture review reports the following requirement: A batch import changes table distributions and query estimates degrade after the load. Which Azure SQL service or administration concept most precisely fits this situation?
- Resource Governor classification
- Database scoped MAXDOP setting
- Intelligent query processing feature
- Statistics maintenance policy
Explanation
Correct Answer
D:Statistics maintenance policy matches the precise requirement: a batch import changes table distributions and query estimates degrade after the load. Fresh statistics can help the optimizer estimate selectivity. Important scope limit: A blind fullscan of every table may produce costly unnecessary I/O.
Incorrect Answers
A:Resource Governor classification applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when a batch import changes table distributions and query estimates degrade after the load.
B:Database scoped MAXDOP setting applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when a batch import changes table distributions and query estimates degrade after the load.
C:Intelligent query processing feature applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when a batch import changes table distributions and query estimates degrade after the load.
Question 332
A Microsoft Azure SQL solution proposal begins with this condition: A recovery plan requires confidence that SQL Server database structures are consistent. Which Azure SQL service or administration concept most precisely fits this situation?
- Intelligent query processing feature
- Resource Governor classification
- Compression and index tradeoff
- Database integrity check
Explanation
Correct Answer
D:Database integrity check matches the precise requirement: a recovery plan requires confidence that SQL Server database structures are consistent. Corruption can be detected before a recovery event becomes urgent. Important scope limit: DBCC CHECKDB does not replace backups or prove an application is correct.
Incorrect Answers
A:Intelligent query processing feature applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when a recovery plan requires confidence that SQL Server database structures are consistent.
B:Resource Governor classification applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when a recovery plan requires confidence that SQL Server database structures are consistent.
C:Compression and index tradeoff applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a recovery plan requires confidence that SQL Server database structures are consistent.
Question 333
The data platform team is assessing this situation: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Which Azure SQL service or administration concept most precisely fits this situation?
- Database scoped MAXDOP setting
- Intelligent query processing feature
- Compression and index tradeoff
- Statistics maintenance policy
Explanation
Correct Answer
A:Database scoped MAXDOP setting matches the precise requirement: a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Eligible queries within that database can use adjusted parallelism limits. Important scope limit: Lower MAXDOP is not universally faster and should follow plan measurement.
Incorrect Answers
B:Intelligent query processing feature applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
C:Compression and index tradeoff applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
D:Statistics maintenance policy applies when a batch import changes table distributions and query estimates degrade after the load, not when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
Question 334
A database architecture review reports the following requirement: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. Which Azure SQL service or administration concept most precisely fits this situation?
- Resource Governor classification
- Statistics maintenance policy
- Database integrity check
- Compression and index tradeoff
Explanation
Correct Answer
A:Resource Governor classification matches the precise requirement: a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. One workload group can be constrained to reduce interference with others. Important scope limit: Resource Governor features vary by SQL platform and are not a general Azure SQL Database setting.
Incorrect Answers
B:Statistics maintenance policy applies when a batch import changes table distributions and query estimates degrade after the load, not when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
C:Database integrity check applies when a recovery plan requires confidence that SQL Server database structures are consistent, not when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
D:Compression and index tradeoff applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
Question 335
A Microsoft Azure SQL solution proposal begins with this condition: A query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. Which Azure SQL service or administration concept most precisely fits this situation?
- Database scoped MAXDOP setting
- Scale vCores for sustained load
- Statistics maintenance policy
- Intelligent query processing feature
Explanation
Correct Answer
D:Intelligent query processing feature matches the precise requirement: a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. Eligible queries can adapt better to observed runtime cardinality or memory demand. Important scope limit: IQP capability depends on engine version compatibility level and feature support.
Incorrect Answers
A:Database scoped MAXDOP setting applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
B:Scale vCores for sustained load applies when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized, not when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
C:Statistics maintenance policy applies when a batch import changes table distributions and query estimates degrade after the load, not when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
Question 336
The data platform team is assessing this situation: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Which Azure SQL service or administration concept most precisely fits this situation?
- Automatic tuning CREATE INDEX
- Index fragmentation maintenance
- Scale vCores for sustained load
- Intelligent query processing feature
Explanation
Correct Answer
C:Scale vCores for sustained load matches the precise requirement: a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Additional compute can support higher concurrent query demand. Important scope limit: Vertical scaling does not fix nonselective application queries automatically.
Incorrect Answers
A:Automatic tuning CREATE INDEX applies when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
B:Index fragmentation maintenance applies when a large SQL Server index exhibits heavy page fragmentation with measurable scan latency, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
D:Intelligent query processing feature applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
Question 337
A database architecture review reports the following requirement: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. Which Azure SQL service or administration concept most precisely fits this situation?
- Resource Governor classification
- Database integrity check
- Database scoped MAXDOP setting
- Compression and index tradeoff
Explanation
Correct Answer
D:Compression and index tradeoff matches the precise requirement: an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. Suitable compression can improve storage efficiency without unacceptable overhead. Important scope limit: Compression benefits vary by data patterns and modification rate.
Incorrect Answers
A:Resource Governor classification applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
B:Database integrity check applies when a recovery plan requires confidence that SQL Server database structures are consistent, not when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
C:Database scoped MAXDOP setting applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
Question 338
During SQL troubleshooting the team must test this hypothesis: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. Which diagnostic artifact would most directly validate the stated condition?
- Resource Governor resource pools workload groups and classifier function
- sys.dm_db_index_physical_stats and query latency baseline
- DBCC CHECKDB results and regular integrity-check history
- Automatic tuning recommendation state and validation outcome
Explanation
Correct Answer
D:Automatic tuning recommendation state and validation outcome directly tests the observation that a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. It helps the team evaluate whether it should evaluate enabling supported automatic CREATE INDEX tuning with monitoring. The check has a boundary: Automated tuning recommendations still need governance for production workloads.
Incorrect Answers
A:Resource Governor resource pools workload groups and classifier function applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
B:Sys.dm_db_index_physical_stats and query latency baseline applies when a large SQL Server index exhibits heavy page fragmentation with measurable scan latency, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
C:DBCC CHECKDB results and regular integrity-check history applies when a recovery plan requires confidence that SQL Server database structures are consistent, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
Question 339
Two independent Azure SQL administration tickets require separate actions. Case A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Case B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Select exactly TWO distinct implementation steps, one for each case.
- Run supported DBCC CHECKDB integrity checks with planned resource impact
- Configure Resource Governor to classify and govern the appropriate sessions
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
- Select appropriate index reorganize or rebuild maintenance based on workload evidence
- Set a supported database-scoped MAXDOP configuration after testing
Explanation
Correct Answers
D:Select appropriate index reorganize or rebuild maintenance based on workload evidence directly resolves the reported requirement that a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Maintenance can improve page organization where fragmentation is material. The implementation must also respect this constraint: Rebuilding every index on a schedule may waste resources or harm concurrency.
E:Set a supported database-scoped MAXDOP configuration after testing directly resolves the reported requirement that a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Eligible queries within that database can use adjusted parallelism limits. The implementation must also respect this constraint: Lower MAXDOP is not universally faster and should follow plan measurement.
Incorrect Answers
A:Run supported DBCC CHECKDB integrity checks with planned resource impact is appropriate for the separate condition that a recovery plan requires confidence that SQL Server database structures are consistent. The actual 2 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. This option is not the specific implementation or corrective step for any of those stated cases.
B:Configure Resource Governor to classify and govern the appropriate sessions is appropriate for the separate condition that a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. The actual 2 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. This option is not the specific implementation or corrective step for any of those stated cases.
C:Enable and evaluate supported intelligent query processing features at appropriate compatibility level is appropriate for the separate condition that a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. The actual 2 cases are A: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. This option is not the specific implementation or corrective step for any of those stated cases.
Question 340
Three independently confirmed SQL platform issues require separate evidence checks. Case A: A batch import changes table distributions and query estimates degrade after the load. Case B: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. Case C: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Select exactly THREE distinct diagnostic checks, one for each case.
- Resource Governor resource pools workload groups and classifier function
- DBCC CHECKDB results and regular integrity-check history
- sys.dm_db_index_physical_stats and query latency baseline
- Compression savings estimate together with measured update CPU and I/O
- Automatic tuning recommendation state and validation outcome
- sys.stats and modification counters with query-plan estimates
Explanation
Correct Answers
C:sys.dm_db_index_physical_stats and query latency baseline directly tests the observation that a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. It helps the team evaluate whether it should select appropriate index reorganize or rebuild maintenance based on workload evidence. The check has a boundary: Rebuilding every index on a schedule may waste resources or harm concurrency.
D:Compression savings estimate together with measured update CPU and I/O directly tests the observation that an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. It helps the team evaluate whether it should choose compression granularity based on actual storage and CPU tradeoffs. The check has a boundary: Compression benefits vary by data patterns and modification rate.
F:sys.stats and modification counters with query-plan estimates directly tests the observation that a batch import changes table distributions and query estimates degrade after the load. It helps the team evaluate whether it should schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration. The check has a boundary: A blind fullscan of every table may produce costly unnecessary I/O.
Incorrect Answers
A:Resource Governor resource pools workload groups and classifier function is appropriate for the separate condition that a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. The actual 3 cases are A: A batch import changes table distributions and query estimates degrade after the load; B: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests; C: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific diagnostic evidence for any of those stated cases.
B:DBCC CHECKDB results and regular integrity-check history is appropriate for the separate condition that a recovery plan requires confidence that SQL Server database structures are consistent. The actual 3 cases are A: A batch import changes table distributions and query estimates degrade after the load; B: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests; C: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific diagnostic evidence for any of those stated cases.
E:Automatic tuning recommendation state and validation outcome is appropriate for the separate condition that a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. The actual 3 cases are A: A batch import changes table distributions and query estimates degrade after the load; B: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests; C: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific diagnostic evidence for any of those stated cases.
Administering Microsoft Azure SQL Solutions (DP-300) Practice Tests