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 isPAGELATCH_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?
Answer(s): A
The following operations use tempdb extensively:* Repetitive create-and-drop operation of temporary tables (local or global).* Table variables that use tempdb for storage.* Etc.
https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation- contention
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?
Answer(s): D
If you determine that a MAXDOP setting different from the default is optimal for your Azure SQL Database workload, you can use the ALTER DATABASE SCOPED CONFIGURATION T-SQL statement.
https://docs.microsoft.com/en-us/azure/azure-sql/database/configure-max-degree-of-parallelism
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?
To improve the concurrency of tempdb, try the following methods:* Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures.* Etc.Note: SymptomsOn a server that is running Microsoft SQL Server, you notice severe blocking when the server is experiencing a heavy load. Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks] indicates that these requests or tasks are waiting for tempdb resources. Additionally, the wait type is PAGELATCH_UP, and the wait resource points to pages in Tempdb.
You have SQL Server on an Azure virtual machine.You need to add a 4-TB volume that meets the following requirements:Maximizes IOPsUses premium solid state drives (SSDs)What should you do?
Answer(s): B
For more throughput, you can add additional data disks and use disk striping.
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage-configuration? tabs=windows2016
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?
Answer(s): C
Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance.Automatic tuning for Azure SQL Database uses the CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN database advisor recommendations to optimize your database performance.
https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-300 content, but please register or login to continue.