4

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.
ChrisF
  • 134,786
  • 31
  • 255
  • 325
user975383
  • 51
  • 4

1 Answers1

2

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.
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks! It looks like the correct direction but i have 2 problems here : 1. The "reads" column is not being updated. Maybe it is only the physical reads that are exposed here ? 2. When i'm making a join between 2 tables, the "set statistics io" option shows the number of logical reads the engine made for each of the tables. The technique you propose does not expose this kind of information. – user975383 Oct 02 '11 at 14:56
  • Yeah, especially if you run a query twice it will be cached and no `reads` are required. You can check `cpu_time` instead, but make sure the query uses a measurable amount of CPU. For logical reads, check the `logical_reads` column. – Andomar Oct 02 '11 at 14:59