Capturing Statistics IO information
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?
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?
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.
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.
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.
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.
I can get this info per query from plan explorer but I want to analyze workload.