How can I find the source (hostname, etc.) of one Azure SQL Database query using Extended Events without having to capture every statement?

Question

I want to do this, but there doesn't seem to be a supported way to do it. What is a version that would work?

CREATE EVENT SESSION [MySession] ON DATABASE 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.num_response_rows,sqlserver.query_hash,sqlserver.username)
    WHERE [sqlserver].[query_hash] = 0xCFEEA417EDDB7075)
ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(1048576))
WITH (STARTUP_STATE=ON)

Is the question that you want to obtain the XE information for every invocation of that specific query hash, but you don't want the XE to check every single query which isn't that query hash? I'm a bit confused. — Sean Gallardy
– Sean Gallardy, Commented Nov 10 at 18:33
Is your question basically how can you convert the binary value 0xCFEEA417EDDB7075 to the unsigned int 64 value required for that predicate? Maybe 14983093432990003317 then? Untested by me — Martin Smith
– Martin Smith, Commented Nov 11 at 12:33
@SeanGallardy Yes, that's exactly correct. I don't want to capture every statement execution and search the results for the query_hash (or a LIKE on sql_text) I care about, as that is too heavy-weight. I can't predict the timing of the executions, so I can't just capture everything for 10 minutes and get what I need. They could be many hours apart. — Mark Freeman
– Mark Freeman, Commented Nov 11 at 18:23
@MartinSmith The format of the query_hash is not the issue, it is that sql_statement_completed does not support filtering on the query_hash, at least not that I've found. And neither does batch_completed. — Mark Freeman
– Mark Freeman, Commented Nov 11 at 18:24
What do you mean it doesn't support it? What happens when you try it (with an integer)? — Charlieface
– Charlieface, Commented Nov 11 at 18:34

Martin Smith · Accepted Answer · 2025-11-12 02:45:31Z

The code you put in the question fails with error

The value specified for event attribute or predicate source, "sqlserver.query_hash", event, "sql_statement_completed", is invalid.

This is because this attribute is defined as uint64.

If I try the query

SELECT *
FROM sys.objects

I see a query hash of 0x3051A61E567B7EF5. Getting the unsigned 64 bit integer equivalent with the below...

DECLARE @QueryHash BINARY(8) = 0x3051A61E567B7EF5

SELECT 
    CASE 
        WHEN CONVERT(BIGINT, @QueryHash) < 0 
        THEN CONVERT(DECIMAL(20,0), CONVERT(BIGINT, @QueryHash)) + 18446744073709551616
        ELSE CONVERT(DECIMAL(20,0), CONVERT(BIGINT, @QueryHash))
    END AS UnsignedInt64

Gives 3481746636164267765.

The below works fine and captures the query as expected

CREATE EVENT SESSION [MySession] ON DATABASE 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.num_response_rows,sqlserver.query_hash,sqlserver.username)
    WHERE [sqlserver].[query_hash] = 3481746636164267765 
  )
ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(1024))
WITH (STARTUP_STATE=ON)

So in your case you should be able to use 14983093432990003317

Stack Exchange Network

How can I find the source (hostname, etc.) of one Azure SQL Database query using Extended Events without having to capture every statement?

1 Answer 1

Your Answer

Hot Network Questions

How can I find the source (hostname, etc.) of one Azure SQL Database query using Extended Events without having to capture every statement?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions