Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query Confusion


SQL Query Confusion

Author
Message
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search