|When someone says still uses SQL Profiler.|
Image is taken from here
SQL Server introduced Extended Events since 2008 version, and was meant to replace SQL Server profiler traces (that will be deprecated in the near future), and provides a lightweight, customizable event collection platform, and you should be using it now.
What is an Extended Event?
According to Microsoft official documentation, is a highly scalable and configurable architecture to be able to identify and troubleshoot any performance issue, it consists of:
Packages: Contains the required objects to collect and process data.
Sessions: Process to be executed to collect the data
Events: Info to be collected and later analyzed.
Actions: Fields common to all events.
Predicates: Filters to be applied when capturing data.
Targets: Where the collection results are stored.
We will make a very simple example so you can see how it works and you can start using it with confidence.
We just will collect all users sessions from our instance, along with the SQL text if exists.
Using the wizard
Just open SSMS and open Management > Sessions > New session wizard.
Specify the name you want for your session, also you can enable the session to start at server startup if you want.
You can select a template for a predefined set of events to achieve common tasks, but for this example, we will not use a template to keep it simple.
In the event library, search and select Login, in the description we can see when this event is fired: Occurs when a successful connection is made to the Server. This event is fired for a new connection or when connections are reused from a connection pool
Then in actions, select the data we want to capture, for this example: database_name, session_id, sql_text, username
In the next window, you can add predicates to filter the info you collect, to avoid over-collecting data and end with huge logs, for this example we will not apply any filter.
Then you select the target, or where you want to save your data. For ad-hoc or quick traces, you can use the ring buffer, and for large collects use the save to file option.
Review the options you have created and also you can script the trace if you want to reuse it.
You can start the session to run when the wizard ends, also see the data being collected.
We can achieve the same results running the following T-SQL
--Event Session CREATE EVENT SESSION [User Sessions] ON SERVER --Events to track ADD EVENT sqlserver.LOGIN ( ACTION(sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text, sqlserver.username ) -- If you want to filter events WHERE ([sqlserver].[nt_user] = N'domain\user_to_filter') ) -- TARGET to use, -- just choose Ring Buffer or Filename and comment the other -- Ring Buffer ADD TARGET package0.ring_buffer WITH ( MAX_MEMORY = 4096 KB ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,MAX_DISPATCH_LATENCY = 30 SECONDS ,MAX_EVENT_SIZE = 0 KB ,MEMORY_PARTITION_MODE = NONE ,TRACK_CAUSALITY = ON ,STARTUP_STATE = OFF ) -- Filename /* ADD TARGET package0.event_file(SET filename = N'User Sessions') WITH ( MAX_MEMORY = 4096 KB ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,MAX_DISPATCH_LATENCY = 30 SECONDS ,MAX_EVENT_SIZE = 0 KB ,MEMORY_PARTITION_MODE = NONE ,TRACK_CAUSALITY = ON ,STARTUP_STATE = OFF ) */ -- To start the session ALTER EVENT SESSION [User Sessions] ON SERVER STATE = START;
To watch the data
Just right-click on the new session created and select the Watch live data option
You can see some events already being recorded corresponding to the current session (in case you didn't apply a filter)
To test it, we just open a new window and connect with a different user.
We can see now the record in our session. If there is a query associated, we will be able to watch it also.
This very basic example will allow us to show more complex examples and how you can do cool thing using extended events.
Thanks, a simple example. Inspires me to try XE.ReplyDelete
I'll echo the prior comment. This was an excellent brief post on how to start using Extended Events to replace SQL Profiler.ReplyDelete
thanks for the feedback! give it a try... start small and then do more complex tasks :)ReplyDelete
This comment has been removed by a blog administrator.ReplyDelete