sql server - Understanding Number of Executions in Execution plan -


below simple execution plan below query.

query:

select top (25) orderid, custid, empid, shipperid, orderdate, filler dbo.orders order orderid; 

execution plan: enter image description here

my question how make sense of number of executions

below number of executions operators

nested loops:
estimated number of executions:1
actual number of executions:1

index scan:
estimated number of executions:1 actual number of executions:1

key lookup: estimated number of executions:25 actual number of executions:25

my questions 1.why nested loop showing 1 execution count

2.index scan shows 1 execution count,but gets 25 rows in 1 execution.are these rows stored in rowset cache or cache ?.will nested loop take 1 row cache , invoke key lookup 25 times each row

below explanation itzik ben-gan

for example, how index scan iterator know stop after 25 rows if top iterator tells stop appears later in plan? answer internal api calls start root node (select iterator in our case). root node invokes top iterator. top iterator invokes method 25 times asks row nested loops iterator. in turn, nested loops iterator invokes method 25 times asks row index scan iterator. hence, index scan iterator doesn't proceed beyond 25 first rows scans. in short, although it's more intuitive follow data flow order interpret plan

but why execution count shows 1.please let me know if missing thing.attached execution plan

update (january 2017) :

i have asked similar question on stack overflow..please see answer more details on same..

https://dba.stackexchange.com/questions/134172/set-statistics-i-o-for-nested-loops

  1. the nested loop executed once. 25 key lookups, that's not count how many times nested loop operation being executed.

  2. the 25 rows come index lookup , passes them along key lookup fetches missing data row, , passes row top operator. when top operator gets 25 rows tells others stop. didn't understand cache question. of course data fetched buffer pool, gets passed 1 operator next once can be.


Comments