100% Latest & Updated Microsoft DP-300 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
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.
Question 341
During SQL troubleshooting the team must test this hypothesis: A recovery plan requires confidence that SQL Server database structures are consistent. Which diagnostic artifact would most directly validate the stated condition?
- Actual memory grant feedback or other IQP feature behavior in query diagnostics
- DBCC CHECKDB results and regular integrity-check history
- sys.stats and modification counters with query-plan estimates
- Automatic tuning recommendation state and validation outcome
Explanation
Correct Answer
B: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.
Incorrect Answers
A:Actual memory grant feedback or other IQP feature behavior in query diagnostics 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.
C:Sys.stats and modification counters with query-plan estimates applies when a batch import changes table distributions and query estimates degrade after the load, not when a recovery plan requires confidence that SQL Server database structures are consistent.
D:Automatic tuning recommendation state and validation outcome applies when a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy, not when a recovery plan requires confidence that SQL Server database structures are consistent.
Question 342
A production database review needs evidence of this condition: A database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting. Which diagnostic artifact would most directly validate the stated condition?
- Resource Governor resource pools workload groups and classifier function
- Database-scoped configuration and observed parallel plan degree
- sys.dm_db_index_physical_stats and query latency baseline
- Actual memory grant feedback or other IQP feature behavior in query diagnostics
Explanation
Correct Answer
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.
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 database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
C: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 database with one unusually parallel workload must be limited without changing the entire SQL Server instance setting.
D:Actual memory grant feedback or other IQP feature behavior in query diagnostics 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 343
An operator is collecting proof for the following observation: A SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive. Which diagnostic artifact would most directly validate the stated condition?
- Database-scoped configuration and observed parallel plan degree
- sys.stats and modification counters with query-plan estimates
- Resource Governor resource pools workload groups and classifier function
- sys.dm_db_index_physical_stats and query latency baseline
Explanation
Correct Answer
C: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.
Incorrect Answers
A:Database-scoped configuration and observed parallel plan degree 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.
B:Sys.stats and modification counters with query-plan estimates 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.
D: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 SQL Server on Azure VM must limit analytical-session resource use so short transactions remain responsive.
Question 344
During SQL troubleshooting the team must test this hypothesis: A query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. Which diagnostic artifact would most directly validate the stated condition?
- Actual memory grant feedback or other IQP feature behavior in query diagnostics
- DBCC CHECKDB results and regular integrity-check history
- Sustained CPU utilization and workload throughput after tuning
- sys.dm_db_index_physical_stats and query latency baseline
Explanation
Correct Answer
A:Actual memory grant feedback or other IQP feature behavior in query diagnostics directly tests the observation that a query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version. It helps the team evaluate whether it should enable and evaluate supported intelligent query processing features at appropriate compatibility level. The check has a boundary: IQP capability depends on engine version compatibility level and feature support.
Incorrect Answers
B: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 query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
C:Sustained CPU utilization and workload throughput after tuning 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.
D: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 query repeatedly gets inappropriate memory grants on an eligible SQL Server or Azure SQL version.
Question 345
A production database review needs evidence of this condition: A steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized. Which diagnostic artifact would most directly validate the stated condition?
- DBCC CHECKDB results and regular integrity-check history
- Automatic tuning recommendation state and validation outcome
- Sustained CPU utilization and workload throughput after tuning
- Actual memory grant feedback or other IQP feature behavior in query diagnostics
Explanation
Correct Answer
C: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.
Incorrect Answers
A: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 steady OLTP workload saturates CPU even after high-cost queries and indexing are optimized.
B:Automatic tuning recommendation state and validation outcome 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.
D:Actual memory grant feedback or other IQP feature behavior in query diagnostics 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 346
An operator is collecting proof for the following observation: An old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests. 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
- Compression savings estimate together with measured update CPU and I/O
Explanation
Correct Answer
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.
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 an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
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 an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
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 an old warehouse table consumes extensive storage but its update workload becomes CPU bound during compression tests.
Question 347
Before implementation the team records this technical condition: A managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. Which technical or administrative action most directly addresses this requirement?
- Configure Resource Governor to classify and govern the appropriate sessions
- Choose compression granularity based on actual storage and CPU tradeoffs
- Evaluate enabling supported automatic CREATE INDEX tuning with monitoring
- Select appropriate index reorganize or rebuild maintenance based on workload evidence
Explanation
Correct Answer
C:Evaluate enabling supported automatic CREATE INDEX tuning with monitoring directly resolves the reported requirement that a managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy. The service can implement and validate eligible recommended index changes. The implementation must also respect this constraint: Automated tuning recommendations still need governance for production workloads.
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 managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
B: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 managed Azure SQL Database repeatedly shows an index opportunity and supported automatic tuning is permitted by change policy.
D:Select appropriate index reorganize or rebuild maintenance based on workload evidence 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.
Question 348
The DBA must resolve this concrete requirement: A large SQL Server index exhibits heavy page fragmentation with measurable scan latency. Which technical or administrative action most directly addresses this requirement?
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
- Run supported DBCC CHECKDB integrity checks with planned resource impact
- Select appropriate index reorganize or rebuild maintenance based on workload evidence
- Configure Resource Governor to classify and govern the appropriate sessions
Explanation
Correct Answer
C: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
A: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 large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
B: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 large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
D: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 large SQL Server index exhibits heavy page fragmentation with measurable scan latency.
Question 349
Two independent Azure SQL administration tickets require separate actions. 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. Select exactly TWO distinct implementation steps, one for each case.
- Scale the supported Azure SQL compute size to a measured capacity requirement
- Run supported DBCC CHECKDB integrity checks with planned resource impact
- Choose compression granularity based on actual storage and CPU tradeoffs
- Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration
- Enable and evaluate supported intelligent query processing features at appropriate compatibility level
Explanation
Correct Answers
C: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.
D:Schedule targeted UPDATE STATISTICS or maintain automatic statistics configuration directly resolves the reported requirement that a batch import changes table distributions and query estimates degrade after the load. Fresh statistics can help the optimizer estimate selectivity. The implementation must also respect this constraint: A blind fullscan of every table may produce costly unnecessary I/O.
Incorrect Answers
A: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 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. This option is not the specific implementation or corrective step for any of those stated cases.
B: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 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. This option is not the specific implementation or corrective step for any of those stated cases.
E: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 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. This option is not the specific implementation or corrective step for any of those stated cases.
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.
Administering Microsoft Azure SQL Solutions (DP-300) Practice Tests