问题[extended-events](dba)

我们创建了一个扩展事件来捕获长时间运行的查询，目标是通过加入计划句柄列来获取执行计划，一些事件数据有一个带有 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>

 

我正在尝试跟踪存储过程的所有执行（包括从其他存储过程内部）和该过程的语句 - 这可能与扩展事件有关吗？我目前的扩展活动是：

CREATE EVENT SESSION [EVENT_NAME] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%'))),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%'))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%')))
ADD TARGET package0.event_file(SET filename=N'TraceData')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

但这似乎不起作用，除非我手动执行该过程。有什么最佳实践方法可以做到这一点？

如何将查询哈希字符串“0x9F37D9B585242D49”转换为扩展事件过滤器所需的 uint64？当值变为负时，转换为 bigint 不起作用。

event
sql_statement_completed

filter
field : sqlserver.query_hash
operator : equal_uint64
value ???

我有一个扩展的事件会话来跟踪死锁，并为 Event_File 设置了数据存储，最大文件大小为 20 MB，最大文件数为 5，启用文件翻转为真。

看起来我无法更改数据存储，即使在我停止会话之后也是如此。这一切都变灰了。我需要能够增加文件的数量，因为由于某种原因它实际上并没有达到 20 MB 的最大文件大小。我已经有 4 个文件用于此会话，最大大小为 47 KB。

我怎样才能增加这个？我只需要重新创建会话吗？我已经有我想保留的历史。

我创建了一个扩展事件会话，它监视 module_start 事件类型并根据 object_name 进行过滤：equal_i_sql_unicode_string]([object_name])

本次会议的目的是在调用过滤器列表中的 proc 时简单地记录基本信息，以便我可以以 99.9% 的保证在生产中是否仍然调用 proc 来回答开发人员的问题。这个想法是 24/7 运行约 1 个月（是的，它不考虑每年运行的东西，但它就是这样）。

我遇到的问题是开发人员给我的 procs 列表大约有 90 个左右，EE 会话的过滤器列表限制为 3,000 个字符。为了提高我们可以跟踪 procs 的速率，我想出的唯一想法是拥有 2 个独立的 EE 会话，它们是相同的，只是过滤器谓词不同。

我不是在问“这会对 CPU 产生多大的影响”，而是他们或多或少地担心使用不同的过滤谓词运行 2 个相同的 EE 会话？对我来说奇怪的是，当“更多过滤 == 更好的性能”时，Microsoft 会将过滤器列表限制为 3,000 个字符，因为 EE 内置到引擎中的方式非常优化，不像跟踪更像代理而不是“触发器”基于一个事件”。

是否可以安全地假设无论运行 1 个会话对性能的影响是什么，我都可以将其乘以 2，还是我没有考虑他们进一步的担忧？

rpc_completed 持续时间 (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp 吗？就我而言，我看到了它们之间的巨大差异。持续时间只有几毫秒，其中 rpc_starting.timestamp - rpc_completed.timestamp 为 6 - 10 秒。

我在企业 2016 sp2-cu14 上，持续时间字段以微秒为单位。我为在 rpc_starting 和 rpc_completed 中捕获 sp_statement_starting 和 sp_statement_completed、sp_batch_starting/sp_batch_completed 做了类似的事情。它显示了相同的情况。我的问题是“哪一个告诉我查询实际花费了多少时间？是 sp_statement_completed 中的持续时间还是开始和完成之间的差异？”

我有一些客户有时会运行一些“不允许”的非常昂贵的查询。

我已经使用扩展事件追踪了它们。

是否可以自动拒绝这些查询？我知道 SQL 语句，因此可以使用 EE 中的“sql_text”事件来捕获它们。

但是我还没有找到一种方法来在 EE 中执行任何操作，例如发生特定事件时。

或者还有其他方法可以解决这个问题吗？

使用 Powershell 中的QueryTableXEventData类，我能够非常快速地解析 100 个 XEL 文件并使用SQLBulkCopy将它们的内容导出到 SQL Server 中的表

例子：

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData
("\\some_file_path\XELog_Files*.xel")

我没有看到 $fields 数组中的 File 属性。

$event | Select-Object -ExpandProperty Fields

is_cached     System.Boolean              True
is_recovered  System.Boolean             False
is_dac        System.Boolean             False
database_id   System.UInt32                 73
packet_size   System.UInt32               8000
options       System.Byte[]  {32, 0, 0, 40...}
options_text  System.String
database_name System.String

或者在 $events

Name      : login
UUID      : 13e22e12-3cb8-49bf-a3e1-131faa95601c
Package   : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropPackage
Metadata  : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropEventMetadata
Timestamp : 18/08/2020 03:41:37 +00:00
Fields    : {is_cached, is_recovered, is_dac, database_id...}
Actions   : {server_instance_name, database_name, database_id, client_hostname...}
Location  : Microsoft.SqlServer.XEvent.Linq.EventLocator

我可以在 SQLBulkCopy 或 QueryTableXEventData 中使用当前正在读取或导出文件名的属性吗？

我使用 rpc_completed 事件来捕获已执行的存储过程。我也是通过以下方式查看语句和传递的参数：

右键单击扩展事件 -> 观看实时数据，最后看到语句+参数

但是我想以表格格式查看捕获的数据，但我无法实现它。当我在下面运行查询时，它显示没有参数的语句。如何在表格中查看参数？需要你的建议。

IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL
DROP TABLE #capture_waits_data
SELECT CAST(target_data as xml) AS targetdata
INTO #capture_waits_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = 'LongRunningQueries'
AND xet.target_name = 'ring_buffer';
--*/
/**********************************************************/
SELECT
CONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS statement,
xed.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms,
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
FROM #capture_waits_data
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
WHERE 1=1 and xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') <> 'NT SERVICE\SQLSERVERAGENT'

这是实际使用的扩展事件：

CREATE EVENT SESSION [LongRunningQueries] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')) 
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(102400))
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=OFF)
GO

我第一次在托管实例（不是Azure SQL DB）中设置扩展事件跟踪。

这些是我仔细遵循的步骤：

1. 在我的数据库上创建了一个主密钥。
2. 使用正确的共享访问签名创建了数据库范围凭据。
3. 创建并开始跟踪。这有效，并且一切都按预期进行了跟踪。

现在，我正在尝试使用 T-SQL 查询 XEL 文件：

SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

但是，我得到两种不同的行为：

• 来自 SSMS：我只得到零行，没有显示错误或消息。

• 从 Azure DataStudio：我收到以下错误消息

消息 300，级别 14，状态 1，第 1 行查看服务器状态权限在对象“服务器”、数据库“主服务器”上被拒绝。Msg 297, Level 16, State 1, Line 1 用户无权执行此操作。

一些事实：

• 我可以使用 Azure 存储资源管理器下载 XEL 文件。
• 我已经检查了 XEL 文件的完整路径，它是正确的。我可以使用浏览器直接下载文件。
• 容器的访问级别为 Public。
• 我尝试过使用不同的文件（运行和停止的跟踪，所有这些都带有数据）。
• XEL 文件充满了数据，因为我在本地查询它们并且可以使用 SSMS 打开它们。
• 我真的不认为缺少密钥或范围凭据。否则，追踪将不起作用。无论如何，我放弃了一切，从头开始，行为没有改变。
• 我所有的测试都是使用托管实例管理员帐户进行的。

所以...

• 托管实例真的支持读取 XEL 文件吗？
• 关于如何使用 sys.fn_xe_file_target_read_file 函数访问文件有什么问题吗？