1. General SQL Server: Directly Using SQL Server Profiler#

• The most common traditional approach for SQL Server

• Can see in real-time:

  • SQL currently executing
  • Duration
  • CPU
  • Login User
  • Application Name

• Suitable for:

  • On-Prem SQL Server
  • VM SQL Server
  • Short-term real-time debugging Operation location:

• SSMS → Tools → SQL Server Profiler

Common events:

• RPC
• SQL
• SP Common Filters:

1
ApplicationName
2
LoginName
3
DatabaseName
4
Duration
5
TextData

In practice, filters are usually essential; otherwise, large systems will spew SQL like a waterfall, leading to immediate eye strain (´-ω-`)

2. Azure SQL Database: Using Extended Events (XEvent) Instead#

• Azure SQL Database does not support SQL Server Profiler
• The official recommendation is also gradually shifting to XEvent
• Better performance than Profiler
• Customizable tracing conditions Creating an XEvent Session:

1
CREATE EVENTSESSION MonitorSQL
2
ON DATABASE
3
ADD EVENT sqlserver.sql_batch_completed(
4
ACTION (
5
        sqlserver.client_app_name,
6
        sqlserver.client_hostname,
7
        sqlserver.username
8
    )
9
)
10
ADD TARGET package0.ring_buffer;
11
GO

Starting the Session:

1
ALTER EVENTSESSION MonitorSQL
2
ON DATABASE
3
STATE=START;
4
GO

Operation location:

• SSMS → XEvent Analysis Tools → TSQL

Here we use:

• sql_batch_completed
  • Monitor SQL execution completion
• ring_buffer
  • Temporary in-memory events
  • No additional file permissions required Many Azure SQL environments lack file system permissions, so ring_buffer is usually prioritized.

3. Using TSQL to Query Real-time SQL Content#

Once created, you can directly query XEvent content:

1
SELECT
2
    event_data.value('(event/@name)[1]','varchar(50)')AS EventName,
3
    event_data.value('(event/data[@name="duration"]/value)[1]','bigint')/1000AS DurationMs,
4
    event_data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(256)')AS AppName,
5
    event_data.value('(event/action[@name="username"]/value)[1]','nvarchar(256)')AS UserName,
6
    event_data.value('(event/data[@name="batch_text"]/value)[1]','nvarchar(max)')AS SQLText
7
FROM (
8
SELECTCAST(xet.target_dataAS XML)AS target_data
9
FROM sys.dm_xe_database_sessions s
10
JOIN sys.dm_xe_database_session_targets xet
11
ON s.address= xet.event_session_address
12
WHERE s.name='MonitorSQL'
13
) t
14
CROSS APPLY target_data.nodes('//RingBufferTarget/event')AS x(event_data);

You can see:

• SQL content

• Execution time

• User

• Calling App

• Host Name Extremely useful for API systems, especially for checking:

• Which API is overwhelming the DB

• What SQL EF Core generates

• Which scheduled job is running excessively