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?
Answer(s): B
Event Tracing for Windows targetETW 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 sessionTo 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
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 databasesFive Azure SQL managed instancesFive instances of SQL Server on Azure Virtual MachinesYou 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?
Answer(s): D
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:
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview https://github.com/scautomation/Azure-Sql-Insights-Workbook
SIMULATIONYou 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.
Answer(s): A
Azure "Query Store" capture queriesMonitor performance by using the Query StoreEnable the Query StoreQuery 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 StudioStep 1: In Object Explorer, right-click a database, and then select Properties.NoteRequires 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.
https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the- query-store
SIMULATIONYou 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.
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 executionYou 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 StudioStep 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 ExecuteStored 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 MyDBGOEXEC sys.sp_cdc_enable_dbGO
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
SIMULATIONIn 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.
Enable compression on a table or indexUse SQL Server Management StudioStep 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 TypeSelect 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 optionsStep 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.)
https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table- or-index?
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.