获取“设置统计信息” t-sql 中的结果用于调优

发布于 2024-12-07 19:08:24 字数 645 浏览 0 评论 0原文

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

换句话说,我想打开set stats io on,访问(并将结果保存到日志表中)SSMS 中“消息”选项卡中通常显示的内容。

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

谢谢!


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

对我来说,完美的解决方案是以某种方式捕获“设置统计 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.

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.

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.

Thanks!


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

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

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

離人涙 2024-12-14 19:08:24

一种方法是使用动态管理视图, 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

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

  • _session_ 视图的计数器在当前批次完成后递增。
  • _exec_ 计数器从 0 开始,并在批处理运行时递增。

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:

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.
  • The _exec_ counters start at 0 and increment while your batch is running.
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文