DP-300 practice questions 191–200

1. Question 191 · 354 in bank

   You have an Azure SQL database named DB1 in the General Purpose service tier. The performance metrics for DB1 are shown in the following exhibit.

   

   You need to reduce the Log IO percentage. The solution must minimize costs. What should you do?

   • AChange Service tier to Business Critical.
   • BIncrease the number of vCores.
   • CPerform a checkpoint operation.
   • DChange Recovery model to Simple.

   Show answer

   Answer: A

   Explanation

   A. Change Service tier to Business Critical. - seems the only solution. B. Increase the number of vCores. - relates to MI being in the Business Critical already. it is not (https://learn.microsoft.com/en-us/answers/questions/603214/log-io-metrics-is-hitting-100) C. Perform a checkpoint operation. - usually for shrinking log files, not related to log performance being poor. if anything, will worsen the situation by flushing data from memory to disk. the operation has to be logged as well. D. Change Recovery model to Simple. - logging transactions is not related to recovery models, transaction log management is (log backups or not). irrelevant for the issue at hand.

   Source: Adapted from community discussion — testdumps2017

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

2. Question 192 · 354 in bank

   You have an Azure SQL database named DB1 that contains a nonclustered index named index1. End users report slow queries when they use index1. You need to identify the operations that are being performed on the index. Which dynamic management view should you use?

   • ASys.dm_exec_query_plan_stats
   • BSys.dm_db_index_physical_stats
   • CSys.dm_db_index_operational_stats
   • DSys.dm_db_index_useage_stats

   Show answer

   Answer: C

   Explanation

   D. sys.dm_db_index_usage_stats is correct. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16 Returns counts of different types of index operations and the time each type of operation was last performed. (This aligns more with the question than ys.dm_db_index_operational_stats).

   Source: Adapted from community discussion — Jibss

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

3. Question 193 · 354 in bank

   HOTSPOT - You have an Azure SQL managed instance named SQLMI1 that hosts multiple databases. You need to monitor the performance of SQLMI1 and identify which database uses the most memory and the most disk I/O. Which objects should you query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   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

   For memory is sys.dm_os_buffer_descriptors. You can use this query to prove: SELECT DB_NAME(database_id) AS database_name, COUNT(*) * 8/1024.0 AS cached_MB FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY cached_MB DESC; For I/O, the answer is correct. SELECT DB_NAME(database_id) AS database_name, SUM(num_of_reads + num_of_writes) AS total_io_operations FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS virt_file_stats GROUP BY database_id ORDER BY total_io_operations DESC;

   Source: Adapted from community discussion — U_C

   

   Next question

4. Question 194 · 354 in bank

   HOTSPOT - You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines. The virtual machine hosts a database named DB1. You need to monitor DB1 by using Extended Events. The solution must meet the following requirements: • Capture raw event data and store the data in Azure Storage. • Minimize the performance impact of capturing extended events. 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.

   

   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

   CREATE EVENT SESSION [YourSession] ON SERVER ADD EVENT sqlserver.sql_statement_completed ( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%') ) ) ADD TARGET package0.event_file (SET filename = N'C:\Junk\YourSession_Target.xel', max_file_size = (2), max_rollover_files = (2) ) WITH ( MAX_MEMORY = 2048 KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 3 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF ); GO https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16

   Source: Adapted from community discussion — igorclapa

   References

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

   

   Next question

5. Question 195 · 354 in bank

   You have several Azure SQL databases on the same Azure SQL Database server in a resource group named ResourceGroup1. You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any additional databases that are created on the Azure SQL server. Which resource type should you use to create the alert?

   • AResource Groups
   • BSQL Servers
   • CSQL Databases
   • DSQL Virtual Machines

   Show answer

   Answer: C

   Explanation

   There are resource types related to application code, compute infrastructure, networking, storage + databases. You can deploy up to 800 instances of a resource type in each resource group. Some resources can exist outside of a resource group. These resources are deployed to the subscription, management group, or tenant. Only specific resource types are supported at these scopes. Reference: https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/resource-providers-and-types

   Source: Adapted from community discussion — ramelas

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

6. Question 196 · 354 in bank

   You have an Azure SQL database named DB1. You need to query the fragmentation information of data and indexes for the tables in DB1. Which command should you run?

   • Asys.dm_db_index_usage_stats
   • BDBCC CHECKALLOC
   • CDBCC SHOWCONTIG
   • Dsts.dm_db_index_physical_stats

   Show answer

   Answer: D

   Next question

7. Question 197 · 354 in bank

   You have an Azure SQL managed instance named SQLMI1 that has the following settings: • vCores: 4 • Service tier: General Purpose • Hardware generation: Standard-series (Gen5) You discover that memory pressure on SQLMI1 is high. You need to reduce the memory pressure on SQLMI1. The solution must minimize costs. What should to do?

   • AEnable the Query Store.
   • BChange vCores to 8.
   • CChange Hardware generation to Premium-series.
   • DChange Service tier to Business Critical.

   Show answer

   Answer: B

   Explanation

   The asnwer doesn't make sense. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits?view=azuresql What we have from the Pricing Calculator: Central US GP 4 vCore Standard 533.37$, memory 4*5.1=20.4GB A. no comments B. vCores = 8, memory = 8*5.1 = 40.8 - greater, so fine, the cost 1066.74$ C. Premium vCores = 4, memory = 4*7 = 28 - greater, so fine, the cost 630.72$ D. BC vCores = 4, memory = 4*5.1 = 20.4 - the same, not ok. 28GB might not reduce the pressure, though 40.8GB might not reduce either. Therefore, 2 conditions of the task: 1. increase memory, 2. the lowest price. So the answer must be C.

   Source: Adapted from community discussion — hllwrld

   References

   • Microsoft Learn

   Next question

8. Question 198 · 354 in bank

   You have five instances of SQL Server on Azure Virtual Machines. You need to monitor Microsoft SQL Server performance for all the instances by consolidating metrics into a single graphic display. The solution must minimize administrative effort. What should you use?

   • AAzure Monitor
   • BLog Analytics
   • CSQL Insights
   • DAzure SQL Analytics

   Show answer

   Answer: A

   Explanation

   I think it's Azure Monitor Agent since: The Log Analytics agent is on a deprecation path and won't be supported after August 31, 2024. Any new data centers brought online after January 1 2024 will not support the Log Analytics agent. If you use the Log Analytics agent to ingest data to Azure Monitor, migrate to the new Azure Monitor agent prior to that date. https://learn.microsoft.com/en-us/azure/azure-monitor/agents/log-analytics-agent

   Source: Adapted from community discussion — pjfunner

   References

   • Microsoft Learn

   Next question

9. Question 199 · 354 in bank

   You have an instance of SQL Server on Azure Virtual Machines named SQL1. SQL1 contains an Extended Events session named session1 that captures Microsoft SQL Server events. You need to correlate the session events with events captured by Event Tracing for Windows (ETW). What should you do for session1?

   • AModify the Set Session Event Filters settings.
   • BAdd a target.
   • CAdd an action.
   • DModify the Specify Session Data Storage settings.

   Show answer

   Answer: B

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

10. Question 200 · 354 in bank

    You have an Azure subscription that contains the following resources: • 10 Azure SQL databases • Five Azure SQL managed instances • Five instances of SQL Server on Azure Virtual Machines You need to implement a centralized monitoring solution for all the Azure SQL resources. The solution must minimize administrative effort. What should you include in the solution?

    • ALog Analytics
    • BAzure SQL Analytics
    • CQuery Performance Insight
    • DSQL Insights

    Show answer

    Answer: D

    Explanation

    Answer is D. SQL Insights is the only option that monitors SQL Server on Azure VM, Azure SQL Database and Azure SQL Managed Instance. https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview?view=azuresql

    Source: Adapted from community discussion — JJJR

    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 191. 354 questions available.