如何在SQL Server扩展会话中捕获传递的参数值？

Question

我已经设置了SQL Server扩展事件来捕获特定数据库中最近的1000个失败查询并将其存储在环形缓冲区中:

Create Event Session [Errors] on Server
Add Event sqlserver.error_reported (
    Action(
        sqlos.task_time,
        sqlserver.sql_text
    )
    Where sqlserver.database_name=N'MyDatabase'
    And error_number<>5701 -- Ignore changed db context messages
)
Add target package0.ring_buffer(Set max_memory=102400)
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=OFF,
    STARTUP_STATE=ON
)
Go

我现在可以这样查询这些错误：

Select 
    xmldata = Cast(xet.target_data as xml) 
Into #rbd
From sys.dm_xe_session_targets xet 
Join sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)
Where xe.name='errors' and target_name='ring_buffer'

; With Errors as (
    Select 
        e.query('.').value('(/event/@timestamp)[1]', 'datetime') as "TimeStamp",
        e.query('.').value('(/event/data[@name="message"]/value)[1]', 'nvarchar(max)') as "Message",
        e.query('.').value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as "SqlText",
        e.query('.').value('(/event/action[@name="task_time"]/value)[1]', 'bigint')/@@TIMETICKS  as "Duration"
    From #rbd
    Cross Apply XMLData.nodes('/RingBufferTarget/event') as Event(e)
)
Select * from Errors
Where SqlText is not null

Drop Table #rbd

目前为止，一切都很好：

这已经对跟踪应用程序级别抛出的错误细节有很大帮助了。

不过，如果我们能够像在SQL Profiler中一样看到传递给这些查询的参数值，那将会使它变得更加有用。因此，当我们看到这样的错误时...

Conversion failed when converting date and/or time from character string.   

对于这个SQL查询，如果你想要查找一个列中包含特定字符的行，可以使用LIKE运算符。例如，如果你想要查找名字中包含"John"的人，可以使用以下代码：

(@NewValue nvarchar(10),@KeyValue int)
Update SomeTable 
Set SomeField=@NewValue Where SomeTableID=@KeyValue

了解@NewValue和@KeyValue参数的值将很有帮助。

你有什么想法可以找到这些信息吗？

- Herb Caudill

---

这可能会有所帮助：http://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/ - Tanner

@Tanner - 不，那里面没有关于参数的内容。 - Herb Caudill

我建议只使用SQL Server Profiler。 - Brian

rcp_completed就是诀窍。从扩展事件会话中收集参数值。 - Jaime García Pérez

Answer 1

我认为只有 sqlserver.rpc_completed 事件才能提供完整的 SQL 命令（包括参数值）。

- Endrju

---

你能澄清一下在这种情况下它是如何工作的吗？例如，是否有一种方法只在查询失败时捕获rpc_completed？ - Herb Caudill

您可以根据RPC调用的结果来过滤“result”字段上的rpc_completed事件，该结果可以是OK、Error、Abort和Skipped。 - Endrju

我对扩展事件不熟悉。有人可以请发布捕获带参数存储过程的SQL脚本吗？ - Mahesh