主页 / user-255206

chandan kumar's questions

Martin Hope
chandan kumar
Asked: 2022-08-20 08:23:49 +0800 CST
  • -2

我们创建了一个扩展事件来捕获长时间运行的查询，目标是通过加入计划句柄列来获取执行计划，一些事件数据有一个带有 id 值的计划句柄，但是有些事件数据只有“0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000”并且无法根据这个零获取执行计划并产生Null。

这是 1 个示例，请指导这里有什么问题以及如何检索此类计划句柄的执行计划？

非常感谢您的时间。

<event name="sql_batch_completed" package="sqlserver" timestamp="2022-08-19T16:13:10.711Z">
  <data name="cpu_time">
    <value>34797000</value>
  </data>
  <data name="duration">
    <value>42259048</value>
  </data>
  <data name="physical_reads">
    <value>640</value>
  </data>
  <data name="logical_reads">
    <value>17204111</value>
  </data>
  <data name="writes">
    <value>79477</value>
  </data>
  <data name="row_count">
    <value>8180</value>
  </data>
  <data name="result">
    <value>0</value>
    <text>OK</text>
  </data>
  <data name="batch_text">
    <value>select  SYSDATETIMEOFFSET() AS [Current_System_DateTime_Offset],  event_data,  
  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) AS [ServerDateTime] 
   , (select top 1 query_plan from sys.dm_exec_query_plan(CONVERT(VARBINARY(64), '0x' + 
   CAST((CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)'))  AS varchar(4000)),1))) Query_plan  ,
   CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)') plan_handle
   
 FROM sys.fn_xe_file_target_read_file('C:\folder1\long_queries_long_queries_36c0ad49cf4e462fb7419312d0*xel', NULL, NULL, NULL) 
 where  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) &gt;  '2022-08-18 17:45:11.3280000'
  order by DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) desc</value>
  </data>
  <action name="collect_system_time" package="package0">
    <value>2022-08-19T16:13:10.711Z</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft SQL Server Management Studio - Query</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>CHANDAN-PC</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>SQLPlanner</value>
  </action>
  <action name="plan_handle" package="sqlserver">
    <value>0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>53</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>CHANDAN-PC\Chandan Kumar</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>select  SYSDATETIMEOFFSET() AS [Current_System_DateTime_Offset],  event_data,  
  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) AS [ServerDateTime] 
   , (select top 1 query_plan from sys.dm_exec_query_plan(CONVERT(VARBINARY(64), '0x' + 
   CAST((CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)'))  AS varchar(4000)),1))) Query_plan  ,
   CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)') plan_handle
   
 FROM sys.fn_xe_file_target_read_file('C:\folder1\long_queries_36c0ad49cf4e462fb7419312d0*xel', NULL, NULL, NULL) 
 where  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) &gt;  '2022-08-18 17:45:11.3280000'
  order by DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) desc</value>
  </action>
  <action name="transaction_id" package="sqlserver">
    <value>0</value>
  </action>
  <action name="transaction_sequence" package="sqlserver">
    <value>0</value>
  </action>
  <action name="username" package="sqlserver">
    <value>CHANDAN-PC\Chandan Kumar</value>
  </action>
</event>
sql-server extended-events