First time:
Query is compiled
Query is executed
Query processor asks for rows one by one from the storage engine according to the query operators in the plan
Storage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory first
Query processor returns resultset of completed query.
Second time
Plan is fetched from cache
Query is executed
Query processor asks for rows one by one from the storage engine according to the query operators in the plan
Storage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory first
Query processor returns resultset of completed query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability