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?

Can you explain in more detail what you mean by "unquely identify a executed query"? — David Browne - Microsoft, Jul 23 '20 at 12:58
From some of the comments, are you trying to: (1) Capture any errors, and (2) for those errors identify which query generated them, and optionally (3) work out if that was your query or somebody else's? Your question is unclear - can you maybe share your Extended Event definition and show how that relates to your query? — Martin Cairney, Jul 24 '20 at 12:06

David Browne - Microsoft · Answer 1 · 2020-07-23T14:48:35.740

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)))

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. So the correlation would be something like client_connection_id -> FooQuery and then another client_connection_id with same query (FooQuery). I dont know if I can pass a identifier or value or string to a Extended Event in some way to say this is from FooQuery. — , Jul 23 '20 at 14:10
I could also create an Extended Event session and filter it in where clause with the current @@SPID of my query, but after the execution (or error) of the query I have to stop the Extended Event session because the session id is only unique at time of execution, after the execution of my query anyone can take the same @@SPID. — , Jul 23 '20 at 14:16

score 0 · Answer 2 · answered Jul 23 '20 at 12:35

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.

Extended Events connection_id vs client_connection_id

2 Answers2