DP-300: Administering Microsoft Azure SQL Solutions

Question

You have an Azure subscription containing a SQL Server instance 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 these requirements:

Capture raw event data and store it in Azure Storage.
Minimize the performance impact of capturing extended events.

How should you complete the Transact-SQL statement?

Select

CREATE EVENT SESSION session1 ON DATABASE
ADD EVENT sqlserver.sql_statement_starting
(
    ACTION (sqlserver.sql_text)
    WHERE statement LIKE 'UPDATE gmTabEmployee%'
)
ADD TARGET
    package0.
(
    SET filename = 'https://gmstorageaccountexevent.blob.core.windows.net/gmcontainerxevent/anyfilenamexl242b.xel'
)
WITH
(
    MAX_MEMORY = 10 MB,
    EVENT_RETENTION_MODE = ,
    MAX_DISPATCH_LATENCY = 3 SECONDS
)

Explanation

The event_file target writes event-session output to an .xel file or an Azure Storage blob. ALLOW_MULTIPLE_EVENT_LOSS permits full event buffers to be lost when buffers fill, which avoids capture-induced workload waits and provides the lowest performance impact among the listed retention modes. NO_EVENT_LOSS can stall tasks until buffer space is available.

Learn more

DP-300: Administering Microsoft Azure SQL Solutions

AstroTutor

Notes

Readiness

QuestionQ61

Community Discussion