100% Latest & Updated Microsoft DP-300 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 350
Three independently confirmed SQL platform issues require separate evidence checks. Case A: A recovery plan requires confidence that SQL Server database structures are consistent. Case B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Case C: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. Select exactly THREE distinct diagnostic checks, one for each case.
- Actual memory grant feedback or other IQP feature behavior in query diagnostics
- sys.dm_db_index_physical_stats and query latency baseline
- DBCC CHECKDB results and regular integrity-check history
- Resource Governor resource pools workload groups and classifier function
- Compression savings estimate together with measured update CPU and I/O
- Database-scoped configuration and observed parallel plan degree
Explanation
Correct Answers
C:DBCC CHECKDB results and regular integrity-check history directly tests the observation that a recovery plan requires confidence that SQL Server database structures are consistent. It helps the team evaluate whether it should run supported DBCC CHECKDB integrity checks with planned resource impact. The check has a boundary: DBCC CHECKDB does not replace backups or prove an application is correct.
D:Resource Governor resource pools workload groups and classifier function directly tests the observation that a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. It helps the team evaluate whether it should configure Resource Governor to classify and govern the appropriate sessions. The check has a boundary: Resource Governor features vary by SQL platform and are not a general Azure SQL Database setting.
F:Database-scoped configuration and observed parallel plan degree directly tests the observation that a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. It helps the team evaluate whether it should set a supported database-scoped MAXDOP configuration after testing. The check has a boundary: Lower MAXDOP is not universally faster and should follow plan measurement.
Incorrect Answers
A:Actual memory grant feedback or other IQP feature behavior in query diagnostics 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 3 cases are A: A recovery plan requires confidence that SQL Server database structures are consistent; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; C: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. This option is not the specific diagnostic evidence for any of those stated cases.
B:sys.dm_db_index_physical_stats and query latency baseline is appropriate for the separate condition that a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. The actual 3 cases are A: A recovery plan requires confidence that SQL Server database structures are consistent; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; C: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. This option is not the specific diagnostic evidence for any of those stated cases.
E:Compression savings estimate together with measured update CPU and I/O is appropriate for the separate condition that an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. The actual 3 cases are A: A recovery plan requires confidence that SQL Server database structures are consistent; B: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; C: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. This option is not the specific diagnostic evidence for any of those stated cases.
Question 351
The DBA must resolve this concrete requirement: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Which technical or administrative action most directly addresses this requirement?
- Configure Resource Governor to classify and govern the appropriate sessions
- Set a supported database-scoped MAXDOP configuration after testing
- Choose compression granularity based on actual storage and CPU tradeoffs
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
Explanation
Correct Answer
B: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:Configure Resource Governor to classify and govern the appropriate sessions applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
C:Choose compression granularity based on actual storage and CPU tradeoffs 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:Enable and evaluate supported intelligent query processing features at appropriate compatibility level 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.
Question 352
A migration or configuration change request describes the following problem: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. Which technical or administrative action most directly addresses this requirement?
- Run supported DBCC CHECKDB integrity checks with planned resource impact
- Evaluate enabling supported automatic CREATE INDEX tuning with monitoring
- Configure Resource Governor to classify and govern the appropriate sessions
- Set a supported database-scoped MAXDOP configuration after testing
Explanation
Correct Answer
C:Configure Resource Governor to classify and govern the appropriate sessions directly resolves the reported requirement that 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. The implementation must also respect this constraint: Resource Governor features vary by SQL platform and are not a general Azure SQL Database setting.
Incorrect Answers
A:Run supported DBCC CHECKDB integrity checks with planned resource impact 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.
B:Evaluate enabling supported automatic CREATE INDEX tuning with monitoring applies when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy, not when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
D:Set a supported database-scoped MAXDOP configuration after testing applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
Question 353
Before implementation the team records this technical condition: A query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. Which technical or administrative action most directly addresses this requirement?
- Set a supported database-scoped MAXDOP configuration after testing
- Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration
- Choose compression granularity based on actual storage and CPU tradeoffs
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
Explanation
Correct Answer
D:Enable and evaluate supported intelligent query processing features at appropriate compatibility level directly resolves the reported requirement that 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. The implementation must also respect this constraint: IQP capability depends on engine version compatibility level and feature support.
Incorrect Answers
A:Set a supported database-scoped MAXDOP configuration after testing 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:Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration 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.
C:Choose compression granularity based on actual storage and CPU tradeoffs applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
Question 354
The DBA must resolve this concrete requirement: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Which technical or administrative action most directly addresses this requirement?
- Choose compression granularity based on actual storage and CPU tradeoffs
- Configure Resource Governor to classify and govern the appropriate sessions
- Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration
- Scale the supported Azure SQL compute size to a measured capacity requirement
Explanation
Correct Answer
D:Scale the supported Azure SQL compute size to a measured capacity requirement directly resolves the reported requirement that a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Additional compute can support higher concurrent query demand. The implementation must also respect this constraint: Vertical scaling does not fix nonselective application queries automatically.
Incorrect Answers
A:Choose compression granularity based on actual storage and CPU tradeoffs applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
B:Configure Resource Governor to classify and govern the appropriate sessions applies when a SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
C:Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration applies when a batch import changes table distributions and query estimates degrade after the load, not when a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
Question 355
A migration or configuration change request describes the following problem: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. Which technical or administrative action most directly addresses this requirement?
- Choose compression granularity based on actual storage and CPU tradeoffs
- Evaluate enabling supported automatic CREATE INDEX tuning with monitoring
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
- Run supported DBCC CHECKDB integrity checks with planned resource impact
Explanation
Correct Answer
A:Choose compression granularity based on actual storage and CPU tradeoffs directly resolves the reported requirement that 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. The implementation must also respect this constraint: Compression benefits vary by data patterns and modification rate.
Incorrect Answers
B:Evaluate enabling supported automatic CREATE INDEX tuning with monitoring applies when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy, not when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
C:Enable and evaluate supported intelligent query processing features at appropriate compatibility level applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
D:Run supported DBCC CHECKDB integrity checks with planned resource impact 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.
Question 356
A post-change impact assessment considers the requirement that: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. What operational outcome follows from implementing the appropriate solution?
- The service can implement and validate eligible recommended index changes
- Fresh statistics can help the optimizer estimate selectivity
- Suitable compression can improve storage efficiency without unacceptable overhead
- Eligible queries within that database can use adjusted parallelism limits
Explanation
Correct Answer
A:The service can implement and validate eligible recommended index changes is the expected technical effect when the administrator chooses the relevant approach for a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. The needed action is to evaluate enabling supported automatic CREATE INDEX tuning with monitoring.
Incorrect Answers
B:Fresh statistics can help the optimizer estimate selectivity applies when a batch import changes table distributions and query estimates degrade after the load, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
C:Suitable compression can improve storage efficiency without unacceptable overhead applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
D:Eligible queries within that database can use adjusted parallelism limits applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
Question 357
The team needs to anticipate the behavior of a correct solution for: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. What operational outcome follows from implementing the appropriate solution?
- Corruption can be detected before a recovery event becomes urgent
- Eligible queries can adapt better to observed runtime cardinality or memory demand
- Maintenance can improve page organization where fragmentation is material
- Eligible queries within that database can use adjusted parallelism limits
Explanation
Correct Answer
C:Maintenance can improve page organization where fragmentation is material is the expected technical effect when the administrator chooses the relevant approach for a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. The needed action is to select appropriate index reorganize or rebuild maintenance based on workload evidence.
Incorrect Answers
A:Corruption can be detected before a recovery event becomes urgent applies when a recovery plan requires confidence that SQL Server database structures are consistent, not when a large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
B:Eligible queries can adapt better to observed runtime cardinality or memory demand applies when a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version, not when a large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
D:Eligible queries within that database can use adjusted parallelism limits applies when a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting, not when a large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
Question 358
An operational planning meeting reviews this deployment condition: A batch import changes table distributions and query estimates degrade after the load. What operational outcome follows from implementing the appropriate solution?
- Suitable compression can improve storage efficiency without unacceptable overhead
- One workload group can be constrained to reduce interference with others
- Fresh statistics can help the optimizer estimate selectivity
- The service can implement and validate eligible recommended index changes
Explanation
Correct Answer
C:Fresh statistics can help the optimizer estimate selectivity is the expected technical effect when the administrator chooses the relevant approach for a batch import changes table distributions and query estimates degrade after the load. The needed action is to schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration.
Incorrect Answers
A:Suitable compression can improve storage efficiency without unacceptable overhead applies when an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests, not when a batch import changes table distributions and query estimates degrade after the load.
B:One workload group can be constrained to reduce interference with others 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.
D:The service can implement and validate eligible recommended index changes applies when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy, not when a batch import changes table distributions and query estimates degrade after the load.
Question 359
Two independent Azure SQL administration tickets require separate actions. Case A: A recovery plan requires confidence that SQL Server database structures are consistent. Case B: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Select exactly TWO distinct implementation steps, one for each case.
- Run supported DBCC CHECKDB integrity checks with planned resource impact
- Select appropriate index reorganize or rebuild maintenance based on workload evidence
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
- Configure Resource Governor to classify and govern the appropriate sessions
- Scale the supported Azure SQL compute size to a measured capacity requirement
Explanation
Correct Answers
A:Run supported DBCC CHECKDB integrity checks with planned resource impact directly resolves the reported requirement that a recovery plan requires confidence that SQL Server database structures are consistent. Corruption can be detected before a recovery event becomes urgent. The implementation must also respect this constraint: DBCC CHECKDB does not replace backups or prove an application is correct.
B: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.
Incorrect Answers
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 recovery plan requires confidence that SQL Server database structures are consistent; B: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific implementation or corrective step for any of those stated cases.
D: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 recovery plan requires confidence that SQL Server database structures are consistent; B: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific implementation or corrective step for any of those stated cases.
E:Scale the supported Azure SQL compute size to a measured capacity requirement is appropriate for the separate condition that a steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. The actual 2 cases are A: A recovery plan requires confidence that SQL Server database structures are consistent; B: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. This option is not the specific implementation or corrective step for any of those stated cases.
Question 360
Three independently confirmed SQL platform issues require separate evidence checks. Case A: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Case B: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. 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
- Database-scoped configuration and observed parallel plan degree
- sys.dm_db_index_physical_stats and query latency baseline
- DBCC CHECKDB results and regular integrity-check history
- Compression savings estimate together with measured update CPU and I/O
- Resource Governor resource pools workload groups and classifier function
Explanation
Correct Answers
A: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.
B:Database-scoped configuration and observed parallel plan degree directly tests the observation that a database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. It helps the team evaluate whether it should set a supported database-scoped MAXDOP configuration after testing. The check has a boundary: Lower MAXDOP is not universally faster and should follow plan measurement.
E: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.
Incorrect Answers
C:sys.dm_db_index_physical_stats and query latency baseline is appropriate for the separate condition that a large SQL Server index exhibits heavy page fragmentation with measurable scan latency. The actual 3 cases are A: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; B: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy; 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.
D: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 database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; B: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy; 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.
F: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 database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting; B: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy; 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.
Administering Microsoft Azure SQL Solutions (DP-300) Practice Tests