SQLSACT (9/12/2012)So when the same query executes again, the results can be found in memory, right?

Results, no. The data pages that the previous execution used, yes (providing they haven't been aged out of cache)

So is it safe to say that when the same query executes again, it's not actually doing another Clustered Index Seekbecause the results are already in memory?

No, it would be completely incorrect to say that. When the query executes again it does a clustered index seek to find the data. The pages may be in memory rather than disk, but it's still a clustered index seek to find the matching rows.

SQLSACT (9/12/2012)So when the same query executes again, the results can be found in memory, right?

Results, no. The data pages that the previous execution used, yes (providing they haven't been aged out of cache)

So is it safe to say that when the same query executes again, it's not actually doing another Clustered Index Seekbecause the results are already in memory?

No, it would be completely incorrect to say that. When the query executes again it does a clustered index seek to find the data. The pages may be in memory rather than disk, but it's still a clustered index seek to find the matching rows.

First time:Query is compiledQuery is executedQuery processor asks for rows one by one from the storage engine according to the query operators in the planStorage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory firstQuery processor returns resultset of completed query.

Second timePlan is fetched from cacheQuery is executedQuery processor asks for rows one by one from the storage engine according to the query operators in the planStorage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory firstQuery processor returns resultset of completed query.

GilaMonster (9/13/2012)First time:Query is compiledQuery is executedQuery processor asks for rows one by one from the storage engine according to the query operators in the planStorage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory firstQuery processor returns resultset of completed query.

Second timePlan is fetched from cacheQuery is executedQuery processor asks for rows one by one from the storage engine according to the query operators in the planStorage engine returns the rows from the pages in memory. If the pages are not in memory, it gets them into memory firstQuery processor returns resultset of completed query.

Thanks

When SQL Server brings pages into Memory, the pages sit in memory in the same way as they were sitting on disk?

When the query is submitted for the second time, even though the pages are in memory, are there still reads happening on the disk? Because of the Clustered Index Seek?

SQLSACT (9/13/2012)When SQL Server brings pages into Memory, the pages sit in memory in the same way as they were sitting on disk?

Meaning?

When the query is submitted for the second time, even though the pages are in memory, are there still reads happening on the disk? Because of the Clustered Index Seek?

Is this right?

Only if some pages are needed that aren't in memory

Meaning?

In the same order as they were on disk

No.

Only if some pages are needed that aren't in memory

Ok this makes sense

What I'm struggling to grasp is why does SQL Server still worry with the Index if all the pages that it needs is already in memory.

Thanks

How else will it find what page a row is on? Indexes are about locating rows. If it didn't bother with an index, it'd have to do a full table scan to find rows needed for the query. Not exactly efficient.

SQLSACT (9/13/2012)When SQL Server brings pages into Memory, the pages sit in memory in the same way as they were sitting on disk?

Meaning?

When the query is submitted for the second time, even though the pages are in memory, are there still reads happening on the disk? Because of the Clustered Index Seek?

Is this right?

Only if some pages are needed that aren't in memory

Meaning?

In the same order as they were on disk

No.

Only if some pages are needed that aren't in memory

Ok this makes sense

What I'm struggling to grasp is why does SQL Server still worry with the Index if all the pages that it needs is already in memory.

Thanks

How else will it find what page a row is on? Indexes are about locating rows. If it didn't bother with an index, it'd have to do a full table scan to find rows needed for the query. Not exactly efficient.

Is this right,

When the query is submitted for the second time, SQL uses whichever Indexes and operators are in the compiled plan and only then SQL makes then realization that the pages needed are in memory and therefore doesn't need to fetch them from disk.

Am I right in that if the Query that is being submitted for the second time used a Clustered Index, SQL checks the non-leaf levels to find out which pages on the leaf it needs and then realizes that the pages in question are in memory?