September 12, 2012 at 9:01 am
Hi All
I'm really confused about something, any help would be great
If I run a query and according to the execution plan, it reports a Clustered Index Seek
From what I understand, when you execute a query, SQL Server brings the data pages into memory, right?
So when the same query executes again, the results can be found in memory, right?
So is it safe to say that when the same query executes again, it's not actually doing another Clustered Index Seek
because the results are already in memory?
I have a feeling that I'm missing something huge here.
Thanks
September 12, 2012 at 9:16 am
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 Seek
because 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.
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
September 13, 2012 at 12:11 am
GilaMonster (9/12/2012)
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 Seek
because 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.
Thanks
Would the following be roughly what's happening
>> Select statement is submitted
>> Clustered Index Seek
>> Pages are read from disk and brought into Memory.
>> Same Select statement is submitted
>> Clustered Index Seek
After this point is where I begin to lose the plot.
Is the whole Clustered Index brought into memory when the first Select statement is submitted or just the pages that contain the results of the select statement?
Thanks
September 13, 2012 at 1:16 am
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
September 13, 2012 at 1:26 am
GilaMonster (9/13/2012)
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.
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?
Is this right?
Thanks
September 13, 2012 at 1:36 am
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
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
September 13, 2012 at 1:42 am
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
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
September 13, 2012 at 1:51 am
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.
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
September 13, 2012 at 2:07 am
SQLSACT (9/13/2012)
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
The index key values tell the database engine which pages to go read to get the required rows, if the page(s) is(are) not in memory then the engine will need to read them in from disk
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 2:13 am
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
September 13, 2012 at 2:17 am
SQLSACT (9/13/2012)
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?
No, and no. Not at all.
To find a row, SQL has to either execute an index seek or an index scan. There is no other way it'll determine which page has a particular row is on. The query execution engine executes the seek or scan and asks the storage engine for the rows it needs. (eg give me all the rows on page 1:5320 or give me the row at in slot 20 on page 1:34231)
The storage engine looks for the required page in memory, if it's not in memory, it issues an IO request to get it into memory. Once the page is in memory, the storage engine then returns the rows required to the query execution engine (one row at a time) so the query execution engine can execute whatever operation it was running (seek or scan likely at this point)
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
September 13, 2012 at 6:05 am
GilaMonster (9/13/2012)
SQLSACT (9/13/2012)
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?
No, and no. Not at all.
To find a row, SQL has to either execute an index seek or an index scan. There is no other way it'll determine which page has a particular row is on. The query execution engine executes the seek or scan and asks the storage engine for the rows it needs. (eg give me all the rows on page 1:5320 or give me the row at in slot 20 on page 1:34231)
The storage engine looks for the required page in memory, if it's not in memory, it issues an IO request to get it into memory. Once the page is in memory, the storage engine then returns the rows required to the query execution engine (one row at a time) so the query execution engine can execute whatever operation it was running (seek or scan likely at this point)
Thanks
I'm with you up until this point
the storage engine then returns the rows required to the query execution engine (one row at a time) so the query execution engine can execute whatever operation it was running (seek or scan likely at this point)
Once the desired pages are brought into memory, can't the results then be presented? Is there still work to be done?
Thanks
September 13, 2012 at 9:29 am
SQLSACT (9/13/2012)
Once the desired pages are brought into memory, can't the results then be presented? Is there still work to be done?
Do you want a query that's intended to fetch three columns from a single row to return all the columns and all the rows on the page (maybe a couple hundred of them)?
What about joins? Aggregations? Secondary filters? Order by?
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
September 13, 2012 at 10:25 am
GilaMonster (9/13/2012)
SQLSACT (9/13/2012)
Once the desired pages are brought into memory, can't the results then be presented? Is there still work to be done?Do you want a query that's intended to fetch three columns from a single row to return all the columns and all the rows on the page (maybe a couple hundred of them)?
What about joins? Aggregations? Secondary filters? Order by?
Got it..
So once the pages are in memory, then only SQL takes what it needs from those pages and does it's aggregations, sorts etc...
In basic terms
>> Query submitted
>> Plan produced or fetched from cache
>> Pages brought into memory from disk
>> Takes what it needs from the pages, aggregations, sorts etc
>> Results returned
That's a lot of work for one query
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply