Extended Events is a lightweight performance monitoring system that enables users to collect data to troubleshoot performance issues in SQL Server. This monitoring tool is particularly useful in administering Microsoft Azure SQL solutions as it allows professionals to catch performance problems as they occur and analyze them in detail.
Key Concepts of Extended Events
The structure of Extended Events is based on four key concepts – events, packages, targets, and sessions.
- Events: These are defined points in the SQL Server code that return information when a specified action happens.
- Packages: These are containers for objects such as events, targets, maps, among others.
- Targets: These are the locations where event data is stored.
- Sessions: A session represents a collection of events, their storage destinations (targets), and filters.
Using Extended Events in Azure SQL Database
In the context of Azure SQL Database, Extended Events is essential because of its use in monitoring, debugging, and optimizing databases. Hence, understanding how to establish a new session to monitor events is crucial for the DP-300 exam.
Below is a simplified example of how to create a new session:
CREATE EVENT SESSION [MonitorSession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.database_id)
WHERE (sqlserver.database_id = (6)) –Replace with your Azure SQL Database ID
) ADD TARGET package0.event_file
(SET filename = N’MonitorSession.xel’)
WITH (MAX_MEMORY = 2048KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS, MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF);
GO
The code above monitors any SQL statement that was completed successfully on a given database.
Viewing Extended Events
Extended Event data can be accessed and viewed through different mechanisms including the SQL Server Management Studio (SSMS), Transact-SQL queries, or third-party tools.
In SSMS, you can view the Extended Event session data by doing the following:
- Navigate to the “Management” folder.
- Expand the “Extended Events” option.
- Right-click on the desired session and choose “Watch Live Data” to view the events as they happen in real-time.
Extended Events vs SQL Server Profiler
Finally, it’s good to understand how Extended Events compares with older methods like SQL Server Profiler. Here are some key points of comparison:
| Point of Comparison | Extended Events | SQL Server Profiler |
|---|---|---|
| Performance Impact | Low | High |
| Flexibility | Very customizable, can monitor a wide range of events | Limited to given event classes |
| Usability | Has a bit of a learning curve, especially compared to SQL Server Profiler | User-friendly, especially for ad hoc or quick investigations |
| Scalability | Provides better scalability for long-term tracking or monitoring larger databases | May have scalability issues |
In conclusion, Extended Events is a powerful tool for monitoring and diagnosing Azure SQL Databases. The performance impact is less significant as compared to SQL Profiler, and they offer more flexibility and scalability, making them a suitable tool for Azure SQL administration. Understanding the operation of Extended Events can significantly aid you in preparing for the DP-300 Administering Microsoft Azure SQL Solutions exam.
Practice Test
True/False: Extended Events is a lightweight performance monitoring system that allows developers to capture runtime information about SQL Server.
- True
- False
Answer: True
Explanation: Extended Events provide a detailed data capture and analysis of the behaviors of SQL Server, which developers can use to identify and troubleshoot issues.
In using Extended Events, which of the following can be achieved?
- a) Viewing real-time data
- b) Collecting specific types of events
- c) Creating customizable data views
- d) Providing direct solutions to troubleshoot SQL problems
Answer: a), b), c)
Explanation: Extended Events allow for real-time data view, collection of specific event types, and the creation of customizable data views. However, it does not provide direct solutions to issues.
True/False: In Azure SQL, there are no predefined templates for Extended Events sessions?
- True
- False
Answer: False
Explanation: Azure SQL provides predefined templates for Extended Events sessions, which lets users get started quickly.
Which of the following are Azure built-in data views for Extended Events?
- a) Live data view
- b) Event history view
- c) Event file target view
- d) All of the above
Answer: d) All of the above
Explanation: Azure offers built-in data views including live data, event history, and event file target views for Extended Events.
True/False: The Monitoring dashboard in Azure Portal allows you to view Extended Events in real-time?
- True
- False
Answer: True
Explanation: The Monitoring dashboard provides users with an insight into Extended Events as they occur in real-time.
Can Extended Events in Azure SQL capture query wait events?
- a) Yes
- b) No
Answer: a) Yes
Explanation: Extended Events in Azure SQL can indeed capture query wait events, helping in identifying and troubleshooting potential issues.
True/False: Extended Events in Azure SQL can be viewed and analyzed using SQL Server Management Studio (SSMS)?
- True
- False
Answer: True
Explanation: SQL Server Management Studio (SSMS) is a tool that can connect to Azure SQL to view and analyze Extended Events.
What types of data can be collected using Extended Events in Azure SQL Server?
- a) Execution information
- b) IO information
- c) Error information
- d) All of the above
Answer: d) All of the above
Explanation: Extended Events can collect a wide range of data like execution, IO, and error information.
True/False: Extended Events don’t have the capability of capturing SQL Server performance metrics.
- True
- False
Answer: False
Explanation: Extended Events are often used to capture SQL Server performance metrics that aid in troubleshooting performance issues.
Which of the following tools can be used to view Extended Events?
- a) Azure Portal
- b) SQL Server Management Studio
- c) Azure Data Studio
- d) All of the above
Answer: d) All of the above
Explanation: All these tools – Azure Portal, SQL Server Management Studio, and Azure Data Studio – have functionalities to view Extended Events.
Interview Questions
What is Extended Events in Azure SQL Database?
Extended Events is an event-handling system used to monitor and troubleshoot performance-related issues in Azure SQL Database.
How can Extended Events be used for monitoring in Azure SQL Database?
Extended Events can be used to capture and track events such as queries, deadlocks, and performance metrics to analyze and optimize database performance.
What are the steps to create an Extended Events session in Azure SQL Database?
To create an Extended Events session in Azure SQL Database, you need to define the session properties, specify the events to capture, configure actions to take, and start the session.
How can you view the events captured by an Extended Events session in Azure SQL Database?
You can view the events captured by an Extended Events session using SQL Server Management Studio (SSMS) or querying the system views like sys.fn_xe_file_target_read_file.
What is the purpose of defining Actions in an Extended Events session?
Actions in an Extended Events session define what specific information to capture along with the event, such as query details or performance metrics.
How can you troubleshoot performance issues using Extended Events in Azure SQL Database?
By capturing events related to performance metrics (like CPU usage, IO operations) using Extended Events, DBAs can analyze and troubleshoot performance issues in Azure SQL Database.
What are the advantages of using Extended Events for monitoring in Azure SQL Database?
Extended Events offer a lightweight and efficient way to capture events, consume fewer resources, provide in-depth details for analysis, and require minimal configuration.
How can you filter events in an Extended Events session in Azure SQL Database?
You can filter events in an Extended Events session based on criteria like event name, duration, SQL text, session ID, or any specific condition using predicates.
Can you create custom Extended Events in Azure SQL Database tailored to specific monitoring needs?
Yes, DBAs can create custom Extended Events in Azure SQL Database by defining custom events, actions, and filters based on their monitoring requirements.
What are some best practices for using Extended Events to monitor Azure SQL Database?
Best practices include defining specific events to capture, limiting the events to essential ones, properly configuring actions, setting appropriate filters, and regularly analyzing captured data for performance optimization.
extutorials.com