Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Query Confusion Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 9:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1358037
Posted Wednesday, September 12, 2012 9:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1358046
Posted Thursday, September 13, 2012 12:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1358352
Posted Thursday, September 13, 2012 1:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1358359
Posted Thursday, September 13, 2012 1:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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

Post #1358363
Posted Thursday, September 13, 2012 1:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1358364
Posted Thursday, September 13, 2012 1:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1358366
Posted Thursday, September 13, 2012 1:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1358373
Posted Thursday, September 13, 2012 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #1358382
Posted Thursday, September 13, 2012 2:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1358385
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse