HOTSPOT (Drag and Drop is not supported)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.Hot Area:
Answer(s): A
Box 1: sys.dm_os_performance_counters sys.dm_os_performance_counters returns a row per performance counter maintained by the server.These include:* SQLServer:Memory ManagerProvides information about SQL Server memory usage, such as the total number of lock structures currently allocated.Box 2: sys.dm_io_virtual_file_stats sys.dm_io_virtual_file_stats returns I/O statistics for data and log files.ExamplesA . Return statistics for a log fileApplies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database The following example returns statistics for the log file in the AdventureWorks2019 database.SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2); GOB . Return statistics for file in tempdbApplies to: Azure Synapse AnalyticsSELECT * FROM sys.dm_pdw_nodes_io_virtual_file_statsWHERE database_name = 'tempdb' AND file_id = 2;Incorrect:* sys.dm_os_buffer_descriptors returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. In SQL Server, this dynamic management view also returns information about the data pages in the buffer pool extension file.* sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server.The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.
https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-objects https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io- virtual-file-stats-transact-sql
HOTSPOT (Drag and Drop is not supported)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.Hot Area:
Box 1: event_fileSQL Server Extended Events tipsExample:We can obtain information about the active event sessions, with the help of the sys.dm_xe_sessions dynamic management view. At the same time, this view returns information about the dropped event and buffer counts. To understand this concept more clearly, we will create two different extended event sessions. The first one is retention mode is Multiple event loss and the second one is Single event loss. These events capture the locks and SQL statement executions.CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER ADD EVENT sqlserver.lock_acquired,ADD EVENT sqlserver.lock_released,ADD EVENT sqlserver.lock_request_priority_state,ADD EVENT sqlserver.query_pre_execution_showplan,ADD EVENT sqlserver.sql_statement_completed,ADD EVENT sqlserver.sql_statement_startingADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents \CaptureQuery_MultipleEventLoss.xel')SQL Server reserves buffer memory for the event sessions to store the event data temporarily and then dispatch them to the target storage. In some cases, the extended event sessions generate data incredibly fast. However, the collected event data can not be dispatched to the event storage as fast as the event data generation speed. To overcome these types of cases, the EVENT_RETENTION_MODE setting allows tolerance to be set for data loss when the extended event is under pressure. So that, the event session impact on the database engine performance is minimized.ALLOW_SINGLE_EVENT_LOSS: This option is the default option and allows single events to be dropped and lost from the session when the event Session memory buffer is full and can not be sent to the storage target.ALLOW_MULTIPLE_EVENT_LOSS: This option affects the performance minimally and allows the entire memory buffer containing multiple events to be dropped and lost when the memory buffers are full.NO_EVENT_LOSS: When we use this option, we can ensure that no event data can be lost but it causes to degrade database engine performance.Example continued:ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents \CaptureQuery_MultipleEventLoss.xel')WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)Box 2: ALLOW_MULTIPLE_EVENT_LOSSSQL Server Extended Events EVENT_RETENTION_MODE optionWe want to minimize the performance impact of capturing extended events.
https://www.sqlshack.com/sql-server-extended-events-tips/
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 use?
Answer(s): D
sys.dm_db_index_physical_stats (Transact-SQL)Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db- index-physical-stats-transact-sql
You have an Azure SQL managed instance named SQLMI1 that has the following settings:vCores: 4Service tier: General PurposeHardware 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?
Answer(s): B
The amount of memory available to Managed Instance is proportional to the number of cores assigned. As an example, in the Gen5 architecture you have 5.1GB of memory per vCore, meaning that 8-core instance will have 41GB memory. Periodically you should check if this amount of memory is the right match for your workload.
https://techcommunity.microsoft.com/t5/azure-sql-blog/do-you-need-more-memory-on-azure-sql-managed- instance/ba-p/563444
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?
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.