Lesson Learned #336: Using “Startup State” Option in an Extended Events Session
Introduction
As an experienced Cloud Architect, I have learned that extended events are an invaluable tool in troubleshooting and diagnosing performance issues. Extended events are lightweight and provide detailed information about server operations and user activities. The “Startup State” option allows the session to persist beyond a server restart and is a useful feature for any cloud architect. In this blog post, I will discuss how to set up an extended events session with the Startup State option.
What is Extended Events?
Extended events are an event-handling system that is used to collect, analyze and diagnose performance issues. It is a lightweight performance-monitoring system that allows the user to collect detailed information about server operations and user activities. The extended events system runs in the background and does not interfere with normal server operations.
How to Set Up an Extended Events Session with the Startup State Option
To set up an extended events session with the “Startup State” option, follow these steps:
Step 1: Create an Extended Events Session
The first step is to create an extended events session. To do so, open a new query window in SQL Server Management Studio and execute the following code:
CREATE EVENT SESSION [MySession]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
ADD EVENT sqlserver.sql_statement_starting
ADD TARGET package0.event_file
(SET filename=N’C:\MyFolder\MySession.xel’,
max_file_size=(2),
max_rollover_files=(2))
WITH (STARTUP_STATE=ON)
This code will create an extended events session named “MySession” that will track both the “sql_statement_completed” and “sql_statement_starting” events. The session will write the events to an event file in the “C:\MyFolder” folder. The “max_file_size” and “max_rollover_files” parameters will determine the size of the event file and the number of files that can be created. Finally, the “STARTUP_STATE=ON” parameter will ensure that the session will persist beyond a server restart.
Step 2: Start the Extended Events Session
Once the extended events session has been created, it must be started. To do so, execute the following code in a new query window:
ALTER EVENT SESSION [MySession]
ON SERVER
STATE=START
This code will start the extended events session and begin collecting events.
Step 3: Stop the Extended Events Session
When you are finished collecting events, the extended events session must be stopped. To do so, execute the following code in a new query window:
ALTER EVENT SESSION [MySession]
ON SERVER
STATE=STOP
This code will stop the extended events session and prevent it from collecting any further events.
Conclusion
As a Cloud Architect, understanding the “Startup State” option for extended events is essential. The “Startup State” option allows the session to persist beyond a server restart and is a useful feature for any cloud architect. In this blog post, I discussed how to set up an extended events session with the “Startup State” option. I hope that this blog post has been helpful and has given you a better understanding of how to use the “Startup State” option for extended events.
References:
Lesson Learned #336: Using STARTUP_STATE option in an extended event in Azure SQL Database
.
1. Azure SQL Database Extended Event
2. Using STARTUP_STATE in
