When someone says still uses SQL Profiler. Image is taken from here

An ancient, dark place, a source of many legends and histories, although some people have been able to get out alive, some others are stuck there, the locals refer to it as "The nosy one" but we know it for another name: SQL Server Profiler

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.

Using T-SQL

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.

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017