DP-300 practice questions
You have an Azure SQL database named DB1. You run a query while connected to DB1. You review the actual execution plan for the query, and you add an index to a table referenced by the query. You need to compare the previous actual execution plan for the query to the Live Query Statistics. What should you do first in Microsoft SQL Server Management Studio (SSMS)?
A. For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
B. Run the SET SHOWPLAN_ALL Transact-SQL statement.
C. Save the actual execution plan.
D. Enable Query Store for DB1.
Discussion forum
Question
You deploy a database to an Azure SQL Database managed instance. You need to prevent read queries from blocking queries that are trying to write to the database. Which database option should set?
A. PARAMETERIZATION to FORCED
B. PARAMETERIZATION to SIMPLE
C. Delayed Durability to Forced
D. READ_COMMITTED_SNAPSHOT to ON
Discussion forum
Question
You have SQL Server on an Azure virtual machine that contains a database named DB1. You have an application that queries DB1 to generate a sales report. You need to see the parameter values from the last time the query was executed. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
A. Enable Last_Query_Plan_Stats in the master database
B. Enable Lightweight_Query_Profiling in DB1
C. Enable Last_Query_Plan_Stats in DB1
D. Enable Lightweight_Query_Profiling in the master database
E. Enable PARAMETER_SNIFFING in DB1
Discussion forum
Question
You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases. The databases perform slower than expected. You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases in sqlsrv1. What should you do?
A. Run Query Store-based queries
B. Review information provided by SQL Server Profiler-based traces
C. Review information provided by Query Performance Insight
D. Run dynamic management view-based queries
Discussion forum
Question
You have an Azure SQL database named sqldb1. You need to minimize the amount of space by the data and log files of sqldb1. What should you run?
A. DBCC SHRINKDATABASE
B. sp_clean_db_free_space
C. sp_clean_db_file_free_space
D. DBCC SHRINKFILE
Discussion forum
Question
You receive numerous alerts from Azure Monitor for an Azure SQL Database instance. You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
A. Set Threshold Sensitivity to High
B. Set the Alert logic threshold to Dynamic
C. Set the Alert logic threshold to Static
D. Set Threshold Sensitivity to Low
E. Set Force Plan to On
Discussion forum
Question
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory. You scale up the virtual machine to 8 vCPUs and 64 GB of memory. You need to reduce tempdb contention without negatively affecting server performance. What is the number of secondary data files that you should configure for tempdb?
A. 2
B. 4
C. 8
D. 64
Discussion forum
Question
You have an Azure SQL database named db1 on a server named server1. The Intelligent Insights diagnostics log identifies queries that cause performance issues due to tempDB contention. You need to resolve the performance issues. What should you do?
A. Implement memory-optimized tables.
B. Run the DBCC FLUSHPROCINDB command.
C. Replace the sequential index keys with nonsequential keys.
D. Run the DBCC DBREINDEX command.
Discussion forum
Question
You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2. You need to limit the number of IOPs that App2 queries generate on SQL1. Which two actions should you perform on SQL1? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
A. Enable query optimizer fixes.
B. Enable Resource Governor.
C. Enable parameter sniffing.
D. Create a workload group.
E. Configure In-memory OLTP.
F. Run the Database Engine Tuning Advisor.
G. Reduce the Max Degree of Parallelism value.
Discussion forum
Question
You have an Azure SQL database named db1 on a server named server1. The Intelligent Insights diagnostics log identifies that several tables are missing indexes. You need to ensure that indexes are created for the tables. What should you do?
A. Run the DBCC SQLPERF command.
B. Run the DBCC DBREINDEX command.
C. Modify the automatic tuning settings for db1.
D. Modify the Query Store settings for db1.
Discussion forum
Question
You have SQL Server on an Azure virtual machine. You need to add a 4-TB volume that meets the following requirements: ✑ Maximizes IOPs ✑ Uses premium solid state drives (SSDs) What should you do?
A. Attach two mirrored 4-TB SSDs.
B. Attach a stripe set that contains four 1-TB SSDs.
C. Attach a RAID-5 array that contains five 1-TB SSDs.
D. Attach a single 4-TB SSD.
Discussion forum
Question
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have SQL Server 2019 on an Azure virtual machine. You are troubleshooting performance issues for a query in a SQL Server instance. To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856. You need to improve system performance. Solution: You create additional tempdb files. Does this meet the goal?
A. Yes
B. No
Discussion forum
Question
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have SQL Server 2019 on an Azure virtual machine. You are troubleshooting performance issues for a query in a SQL Server instance. To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856. You need to improve system performance. Solution: You create additional tempdb files. Does this meet the goal?
A. Yes
B. No
Discussion forum
Question
You have an Azure SQL database named db1 on a server named server1. You need to modify the MAXDOP settings for db1. What should you do?
A. Connect to db1 and run the sp_configure command.
B. Connect to the master database of server1 and run the sp_configure command.
C. Configure the extended properties of db1.
D. Modify the database scoped configuration of db1.
Discussion forum
Question
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have SQL Server 2019 on an Azure virtual machine. You are troubleshooting performance issues for a query in a SQL Server instance. To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856. You need to improve system performance. Solution: You reduce the use of table variables and temporary tables. Does this meet the goal?
A. Yes
B. No
Discussion forum
Question