Capturing Statistics IO information
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?
I'm hoping Jonathan Kehayias will have some input on capturing this directly with XEvents on your regular workload.