Extended Events captures query_hash/query_plan_hash to 0 in some queries

  • i1888

    SSCommitted

    Points: 1810

    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:
     

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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: 1810

    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: 1004484

    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: 1004484

    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: 1810

    Thanks!

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

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