Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 206–210 (page: 43)

Updated On: 10-Feb-2026

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?

  1. Modify the Set Session Event Filters settings.
  2. Add a target.
  3. Add an action.
  4. Modify the Specify Session Data Storage settings.

Answer(s): B

Explanation:

Event Tracing for Windows target
ETW tracing is either used together with Extended Events or as an Extended Events event consumer.
The ETW target is a singleton target, although the target can be added to many sessions. If an event is raised on many sessions, the event will only be propagated to the ETW target one time per event occurrence. The Extended Events engine is limited to a single instance per process.
Add the target to a session
To add the ETW target to an Extended Events session, you must include the following statement when you create or alter an event session:
ADD TARGET package0.etw_classic_sync_target


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/event-tracing-for-windows-target



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?

  1. Log Analytics
  2. Azure SQL Analytics
  3. Query Performance Insight
  4. SQL Insights

Answer(s): D

Explanation:

Monitor your SQL deployments with SQL Insights (preview)
Applies to: SQL Server on Azure VM, Azure SQL Database, Azure SQL Managed Instance SQL Insights (preview) is a monitoring solution for products in the Azure SQL family. SQL Insights uses dynamic management views to expose the data that you need to monitor health, diagnose problems, and tune performance.
SQL Insights performs all monitoring remotely. Monitoring agents on dedicated virtual machines connect to your SQL resources and remotely gather data. The gathered data is stored in Azure Monitor Logs to enable easy aggregation, filtering, and trend analysis. You can view the collected data from the SQL Insights workbook template, or you can delve directly into the data by using log queries.
Example, Azure Monitor SQL Insights Workbook:


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview https://github.com/scautomation/Azure-Sql-Insights-Workbook



SIMULATION

You need to ensure that all queries executed against db1 are captured in the Query Store.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Azure "Query Store" capture queries
Monitor performance by using the Query Store
Enable the Query Store
Query Store is enabled by default for new Azure SQL Database and Azure SQL Managed Instance databases.

Use the Query Store page in SQL Server Management Studio

Step 1: In Object Explorer, right-click a database, and then select Properties.

Note
Requires at least version 16 of Management Studio.

Step 2: In the Database Properties dialog box, select the Query Store page.

Step 3: In the Operation Mode (Requested) box, select Read Write.

Note: The Query Store feature provides you with insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Query Store simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server.


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the- query-store



SIMULATION

You need to enable change data capture (CDC) for db1.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Note: Before you can create a capture instance for individual tables, you must enable change data capture for the database.

To enable change data capture, run the stored procedure sys.sp_cdc_enable_db (Transact-SQL) in the database context.

Stored procedure execution
You can use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.

Use SQL Server Management Studio

Step 1: In Object Explorer, connect to an instance of SQL Server or Azure SQL Database, expand that instance, and then expand Databases.

Step 2: Expand the database that you want, expand Programmability, and then expand Stored Procedures.

Step 3: Right-click the stored procedure that you want to run [here: sys.sp_cdc_enable_db] and select Execute

Stored Procedure.

Step 4: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK.

Note: Enable Database for CDC in Transact-SQL
-- ====
-- Enable Database for CDC
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data- capture-sql-server https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure



SIMULATION

In an Azure SQL database named db1, you need to enable page compression on the PK_SalesOrderHeader_SalesOrderID clustered index of the SalesLT.SalesOrderHeader table.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Enable compression on a table or index
Use SQL Server Management Studio

Step 1: In Object Explorer, expand the database that contains the table that you want to compress and then expand the Tables folder.

Step 2: To compress an index, expand the table [Here: SalesLT.SalesOrderHeader table]. that contains the index that you want to compress and then expand the Indexes folder.

Step 3: In the Data Compression Wizard, on the Welcome to the Data Compression Wizard page, select Next.

Step 4: On the Select Compression Type page, select the compression type [Here: page compression] to apply to each partition in the table or index you want to compress.
When finished, select Next.

Step 4a: Compression Type

Select the compression option for each partition. Isn't available when Use the same compression type for all partitions is selected. List options are None, Row, and Page[This one].

Step 4b: Skip the other options

Step 5: Right-click the table or index [Here: PK_SalesOrderHeader_SalesOrderID] to compress, point to Storage and select Manage Compression....

Step 6: In the Select an Output Option page, specify how you want to complete your compression. Select Create Script to create a SQL script based the previous pages in the wizard. Select Run immediately to create the new partitioned table after completing all remaining pages in the wizard.

Step 7: On the Review Summary page, under Review your selections, expand all available options to verify that all compression settings are correct. If everything is as expected, select Finish.

(On the Compression Wizard Progress page, monitor status information about the actions of the Create Partition Wizard. Depending on the options that you selected in the wizard, the progress page might contain one or more actions. The top box displays the overall status of the wizard and the number of status, error, and warning messages that the wizard has received.)


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table- or-index?



Viewing page 43 of 85
Viewing questions 211 - 215 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!