Capturing Statistics IO information

GokhanVarol 2013-02-28 02:35:16

Can the information returned by SET STATISTICS IO be captured using Extended Events?

I am looking to identify queries that perform read-ahead (pre-fetching) on Nested Loops joins – that is, where the WithOrderedPrefetch or WithUnorderedPrefetch attribute appears. For these queries, I want to find those that are using a work table when pre-fetching LOB data, as it is causing a performance problem.

Can Plan Explorer do this? Does anyone have any other suggestions?

GokhanVarol 2013-02-28 14:32:00
I want to go trough a week of workload and query the data trying to find which queries I need to be modifying (in case they use lot's of physical reads and workloads) etc.
I can get this info per query from plan explorer but I want to analyze workload.
Aaron Bertrand 2013-02-28 14:33:33
Understood. Plan Explorer won't be able to help you there. I am confident that XEvents could help out but I don't have any code at the ready to hand off. Have you made any attempts using XEvents?
Jonathan Kehayias 2013-03-04 16:42:37
Why can't you find this information from the query stats DMV in SQL Server which tracks the physical reads information? If you persist that information with query_hash and query_plan_hash to a table you could easily start finding where you have issues with some simple querying of the data.
GokhanVarol 2013-03-04 18:43:16
I am trying to get the statistics per table and worktable used in the statement. I would like to go over the statements with high work table and if they did not use hash joins etc I would like to point them out.
Aaron Bertrand 2013-02-28 14:30:01
Plan Explorer captures runtime I/O metrics, much like SET STATISTICS IO ON, when you generate an actual plan from within the tool. And as Paul White demonstrated on another question of yours, it can also show you the I/O used for read-ahead, which gives you some idea about pre-fetch. If all you have is an existing plan captured elsewhere (in SSMS, an estimated plan, or pulled from plan cache), then runtime I/O metrics will not be available.

I'm hoping Jonathan Kehayias will have some input on capturing this directly with XEvents on your regular workload.

GokhanVarol 2013-02-28 15:33:48
I played with scan_stopped event but I could not fire it from my user session, not sure when it get's fired.

alt text