SQL Query Confusion

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Query Confusion

14 posts, Page 1 of 2

1 2 »»

SQL Query Confusion

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Wednesday, September 12, 2012 9:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

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

GilaMonster

Posted Wednesday, September 12, 2012 9:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

SQLSACT

Posted Thursday, September 13, 2012 12:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

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?

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

GilaMonster

Posted Thursday, September 13, 2012 1:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

SQLSACT

Posted Thursday, September 13, 2012 1:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

GilaMonster (9/13/2012)

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

GilaMonster

Posted Thursday, September 13, 2012 1:36 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

SQLSACT

Posted Thursday, September 13, 2012 1:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

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

GilaMonster

Posted Thursday, September 13, 2012 1:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Perry Whittle

Posted Thursday, September 13, 2012 2:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201, Visits: 11,150

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" Wink

Post #1358382

SQLSACT

Posted Thursday, September 13, 2012 2:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

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

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions