DP-300 practice questions 331–340

1. Question 331 · 354 in bank

   HOTSPOT - You have a 50-TB Microsoft SQL Server database named DB1. You need to reduce the time it takes to perform database consistency checks of DB1. Which Transact-SQL command should you run? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: NOINDEX - NOINDEX specifies that intensive checks of nonclustered indexes for user tables will not be performed. This choice decreases the overall execution time. NOINDEX doesn't affect system tables because integrity checks are always performed on system table indexes. Box 2: PHYSICAL_ONLY - Many new checks have been introduced to include the new features. Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

   Source: Adapted from community discussion — eric0718

   References

   • Microsoft Learn

   

   Next question

2. Question 332 · 354 in bank

   You have an Azure SQL database named DB1 that contains a private certificate named Sales. The private key for Sales is encrypted with a password. You need to change the password for the private key. Which Transact-SQL statement should you run?

   • AALTER CERTIFICATE Sales WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ' EWYx9Xk+$#');
   • BALTER CERTIFICATE Sales WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = ' 6YY9YcD!pV');
   • CALTER CERTIFICATE Sales WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'Mb^68K&*w%'), ENCRYPTION BY PASSWORD = ' 6YY9YcD!pV');
   • DALTER CERTIFICATE Sales WITH PRIVATE KEY (FILE = 'D:\importkeys\SalesNew, (DECRYPTION BY PASSWORD = ' Mb^68K&*w%');

   Show answer

   Answer: C

   Explanation

   Azure SQL database ALTER CERTIFICATE changes the password used to encrypt the private key of a certificate, removes the private key, or imports the private key if none is present. Example, changing the password that is used to encrypt the private key: ALTER CERTIFICATE Shipping11 - WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '95hkjdskghFDGGG4%', ENCRYPTION BY PASSWORD = '34958tosdgfkh##38'); Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-certificate-transact-sql

   References

   • Microsoft Learn

   Next question

3. Question 333 · 354 in bank

   HOTSPOT - You have a SQL Server on Azure Virtual Machines instance that hosts a 10-TB SQL database named DB1. You need to identify and repair any physical or logical corruption in DB1. The solution must meet the following requirements: ✑ Minimize how long it takes to complete the procedure. ✑ Minimize data loss. How should you complete the command? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: REPAIR_REBUILD - REPAIR_REBUILD - Performs repairs that have no possibility of data loss. This option may include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index. Box 2: PHYSICAL_ONLY - PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data. Incorrect: TABLOCK - Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but will decrease the concurrency available on the database while DBCC CHECKDB is running. EXTENDED_LOGICAL_CHECKS - If the compatibility level is 100 ( SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present. Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

   Source: Adapted from community discussion — Saffar

   References

   • Microsoft Learn

   

   Next question

4. Question 334 · 354 in bank

   HOTSPOT - You have an Azure SQL database named DB1. You need to identify how much unused space in megabytes was allocated to DB1. How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: 128.0 - -- Connect to database -- Database data space allocated in MB and database data space allocated unused in MB SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB FROM sys.database_files - GROUP BY type_desc - HAVING type_desc = 'ROWS' Box 2: sys.database_files - Reference: https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-get-azure-sql-database-size/ba-p/369189

   References

   • Microsoft Learn

   

   Next question

5. Question 335 · 354 in bank

   HOTSPOT - You have an Azure SQL database. You need to identify whether a delayed query execution is associated to a RESOURCE_SEMAPHORE wait. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: wait_type - Determine if a RESOURCE_SEMAHPORE wait is a top wait Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait SELECT wait_type, SUM(wait_time) AS total_wait_time_ms FROM sys.dm_exec_requests AS req JOIN sys.dm_exec_sessions AS sess ON req.session_id = sess.session_id WHERE is_user_process = 1 - GROUP BY wait_type - ORDER BY SUM(wait_time) DESC; Box 2: sys.dm_exec_requests - Use the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time. Azure SQL RESOURCE_SEMAPHORE wait "wait_time" is_user_process

   Source: Adapted from community discussion — TheDataGuy

   

   Next question

6. Question 336 · 354 in bank

   Select 2

   You have an Azure subscription that contains 50 instances of SQL Server on Azure Virtual Machines. The instances host 500 Azure SQL databases. You need to ensure that all the databases have the same configuration. The solution must meet the following requirements: ✑ Auditing must be enabled. ✑ Azure Defender must be enabled. ✑ Public network access must be disabled. ✑ Administrative effort must be minimized. Which two resources should you create in the subscription? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • Aan Azure Automation runbook
   • Ban Azure Policy initiative
   • Can Azure Policy assignment
   • Dan Azure Automation account
   • Ean Azure Policy definition

   Show answer

   Answer: CE

   References

   • Microsoft Learn

   Next question

7. Question 337 · 354 in bank

   You have an instance of SQL Server on Azure Virtual Machine named SQL1. You need to monitor SQL1 and query the metrics by using Kusto query language. The solution must minimize administrative effort. Where should you store the metrics?

   • AAzure Event Hubs
   • Ba Log Analytics workspace
   • CAzure SQL Database
   • Dan Azure Blob storage container

   Show answer

   Answer: B

   References

   • Microsoft Learn

   Next question

8. Question 338 · 354 in bank

   DRAG DROP - You have a resource group named App1Dev that contains an Azure SQL Database server named DevServer1. DevServer1 contains an Azure SQL Database instance named DB1. The schema and permissions for DB1 are saved in a Microsoft SQL Server Data Tools (SSDT) database project. You need to populate a new resource group named App1Test with the DB1 database and an Azure SQL server named TestServer1. The resources in App1Test must have the same configurations as the resources in App1 Dev. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   

   Next question

9. Question 339 · 354 in bank

   DRAG DROP - You have two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. VM1 hosts a database named db1. You plan to create a database availability group (DAG) for db1. The solution must use certificate authentication between VM1 and VM2. You need to configure authentication for the outbound connections of VM1. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 340 · 354 in bank

    You have an Azure subscription that contains the resources shown in the following table.

    

    You need to configure a connection between VM1 and MI1. The solution must meet the following requirements: ✑ The connection must be encrypted. ✑ Network latency must be minimized. What should you implement?

    • Aa site-to-site VPN
    • Bvirtual network peering
    • Cprivate endpoints
    • Dservice endpoints

    Show answer

    Answer: C

    Explanation

    I think the answer is correct because using Virtual network peering is without encryption. Virtual network peering No public Internet, gateways, or encryption is required in the communication between the virtual networks. https://learn.microsoft.com/en-us/azure/virtual-network/virtual-network-peering-overview

    Source: Adapted from community discussion — OneplusOne

    References

    • Microsoft Learn
    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 331. 354 questions available.