December 5, 2018 at 11:31 pm
Hi,
I am researching about prefetching in Nested loops and can find some articles talk about that. Most of them mentions about this property WithUnorderedPrefetch/WithOrderedPrefetch = true
And using it to see if the Nested Loop enable prefetching or not.
But what i can't see or find with google is about when/how the property WithUnorderedPrefetch/WithOrderedPrefetch = false
Any help will be appreciated!
Thanks,
Sol,
December 9, 2018 at 12:24 am
sol.nt - Wednesday, December 5, 2018 11:31 PMHi,
I am researching about prefetching in Nested loops and can find some articles talk about that. Most of them mentions about this propertyWithUnorderedPrefetch/WithOrderedPrefetch= true
And using it to see if the Nested Loop enable prefetching or not.
But what i can't see or find with google is about when/how the propertyWithUnorderedPrefetch/WithOrderedPrefetch= false
Any help will be appreciated!
Thanks,
Sol,
Good day,
I assume that you are speaking about the Execution Plan, where you see something like:
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
The properties WithUnorderedPrefetch and WithOrderedPrefetch indicates whether prefetch of data is being used in the nested loops (Prefetching can be ordered or Unordered). If there is no used of prefetch then there is no reason to use these. he fact that you do not see theseproperties means that there is no used of prefetch, which is why you did not found cases of value false.
You can learn about these properties in this awesome post:
https://www.red-gate.com/simple-talk/sql/performance/sql-server-prefetch-and-query-performance/
Senior consultant and architect, data platform and application development, Microsoft MVP.
December 11, 2018 at 2:39 am
Hi @Ronen,
Thanks for your information.
I already went through the link you shared and some others such as
https://blogs.msdn.microsoft.com/craigfr/2008/10/07/random-prefetching/
and
https://sqlserverfast.com/epr/nested-loops/
and i agree with you that these properties never go with [false] value.
But i wonder if that information could come from BOL or we have a way to prove it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply