GilaMonster (9/13/2012)
SQLSACT (9/13/2012)
GilaMonster (9/13/2012)
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?
Am I on the right track here?
Thanks