Extended Events connection_id vs client_connection_id

Question

Hello guys I want to find a way to identify a query executed for Extended Events in Microsoft SQL Server (to filter the Extended Event with only that executed query)

If i query the system views in SQL Server like this:

SELECT  session_id, connection_id
FROM sys.dm_exec_requests
WHERE session_id = @@SPID

I get the connection_id of the current query executing which is unique until SQL Server restarts.

But Extended Events have a different value called 'sqlserver.client_connection_id' which is not the same identifier as 'connection_id' from the table 'sys.dm_exec_requests'.

Do you know where can I find the 'sqlserver.client_connection_id' in system tables? or another solution to unquely identify a executed query?

Answer 1

The client_connection_idin Extended Events (according to SSMS)

Provides the optional identifier provided at connection time by a client

and is the SqlConnection.ClientConnectionId, which is intended to support troubleshooting client connectivity issues.

You can locate the connection ID in the extended events log to see if the failure was on the server if the extended event for logging connection ID is enabled. You can also locate the connection ID in the connection ring buffer (Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer) for certain connection errors. If the connection ID is not in the connection ring buffer, you can assume a network error.

So this id correlates the client-side and server-side of the connection attempt. For successful connections a row in sys.dm_exec_connections and sys.dm_exec_sessions will be created with different id's.

I'm trying to create an Extended Event with error_reported of all queries. And then filter the results in .xel file using an identifier that tells me that this was from X query.

You can capture the query in the error_reported event, eg:

CREATE EVENT SESSION [errors] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION
    (
       sqlserver.client_app_name,
       sqlserver.session_id,
       sqlserver.sql_text
    )
WHERE ([severity]>=(11)))

Answer 2

Extended Evets by default tracks all of the connections and activity on the instance. Your filters in the definition will limit that down.

The sqlserver.client_connection_id includes all of the values from all of the queries - so if you DID know the client connection id then you could identify those results.

I'm not clear what you are trying to filter for with the Extended Event? Are you looking to see where a specific query was executed from or track all the queries on a specific connection?

The other places you can look to get the same connection info are :

SELECT * FROM sys.dm_exec_connections
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests

Looking at these might help you link the make the connection.