Extended Events captures query_hash/query_plan_hash to 0 in some queries

Question

Extended Events captures query_hash/query_plan_hash to 0 in some queries

i1888

SSCommitted

Points: 1970
More actions
January 1, 2018 at 11:03 pm

#341549

Extended Events captures query_hash/query_plan_hash to 0 in dynamic query sometimes, anyone knows why? thanks!
XE:
CREATE EVENT SESSION [XE_CPUUsage] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text)
WHERE ([cpu_time]>=(500000)))
ADD TARGET package0.event_file(SET filename=N'c:\temp\XEventSessions\XE_CPUUsage.xel',max_file_size=(65536),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
Screenshot:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's an EXEC statement. The only things that have query hashes are the queries themselves, not exec of stored procedures (which this is)

Since you're capturing statement completed, you should have another event with the INSERT <obscured table name> alone, outside of the EXEC.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

i1888 SSCommitted Points: 1970 More actions · Answer 2

GilaMonster - Tuesday, January 2, 2018 3:27 AM
It's an EXEC statement. The only things that have query hashes are the queries themselves, not exec of stored procedures (which this is)
Since you're capturing statement completed, you should have another event with the INSERT <obscured table name> alone, outside of the EXEC.

I have converted/translated/imported the extended events file to a table in sql server.
I found those "exec ***" statement only has its own record in extended event, there is no other query/statement which is in the "exec [query part]"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Try adding sp_statement_completed event.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

i1888 SSCommitted Points: 1970 More actions · Answer 5

i1888

SSCommitted

Points: 1970

January 3, 2018 at 4:16 am

#1974180

Thanks!