获取“set statistics io on”会导致t-sql进行调优

I want to add monitoring capabilities to a complex process involving many stored procedures. In some cases I want to capture the number of logical reads produced by a single statement.

我想为涉及许多存储过程的复杂过程添加监视功能。在某些情况下，我想捕获单个语句产生的逻辑读取数。

In other words, I would like to turn on the set statistics io on, access (and save the results to a log table) what is usually displayed in the SSMS in the "messages" tab.

换句话说，我想打开set statistics io on，访问（并将结果保存到日志表中）通常在“messages”选项卡中显示在SSMS中。

I saw that it can be done in .Net with SqlInfoMessageEventHandler. I'm sure that it can also be done in T-SQL but i didn't find it yet.

我看到它可以在.Net中使用SqlInfoMessageEventHandler完成。我确信它也可以在T-SQL中完成，但我还没有找到它。

Thanks!

谢谢！

Logical_reads in sys.dm_exec_requests is not increasing as well...

sys.dm_exec_requests中的Logical_reads也没有增加......

The perfect solution for me would be a way of somehow capturing the "set statistics io on" information :

对我来说完美的解决方案是以某种方式捕获“set statistics io on”信息：

select name, id
    from   sysobjects
    union all 
    select name,id
    from   sysobjects  ;


(120 row(s) affected)
Table 'sysschobjs'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

1 个解决方案

#1

One way is to use dynamic management views, available in 2008 and up. For example, to determine the number of reads done by your query, you could:

一种方法是使用动态管理视图，可在2008年及以后使用。例如，要确定查询完成的读取次数，您可以：

declare @start_reads bigint
select @start_reads = reads from sys.dm_exec_requests where session_id = @@spid

-- Your query here 

select reads - @start_reads from sys.dm_exec_requests where session_id = @@spid

There's basically two types of counters:

基本上有两种类型的计数器：

The _session_ views have counters that are incremented after your current batch completes.
_session_视图具有在当前批次完成后递增的计数器。
The _exec_ counters start at 0 and increment while your batch is running.
_exec_计数器从0开始，并在批处理运行时递增。

#1