SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Disk to Memory


SQL Disk to Memory

Author
Message
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2078 Visits: 2931
Hi all

I have a quick question regarding pages moving from disk into memory

If I select * from a table, are all pages that make up the table and indexes moved into memory?

So any subsequent query against that same table would not need to go to disk at all?

Thanks
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19768 Visits: 17242
SQLSACT (9/25/2012)
Hi all

I have a quick question regarding pages moving from disk into memory

If I select * from a table, are all pages that make up the table and indexes moved into memory?

So any subsequent query against that same table would not need to go to disk at all?

Thanks

for select * there is no index and yes, all data pages would be cached into memory

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
Perry Whittle (9/25/2012)
SQLSACT (9/25/2012)
Hi all

I have a quick question regarding pages moving from disk into memory

If I select * from a table, are all pages that make up the table and indexes moved into memory?

So any subsequent query against that same table would not need to go to disk at all?

Thanks

for select * there is no index and yes, all data pages would be cached into memory


Doesn't it depend on there being space for the whole table in cache?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19768 Visits: 17242
laurie-789651 (9/25/2012)
Perry Whittle (9/25/2012)
SQLSACT (9/25/2012)
Hi all

I have a quick question regarding pages moving from disk into memory

If I select * from a table, are all pages that make up the table and indexes moved into memory?

So any subsequent query against that same table would not need to go to disk at all?

Thanks

for select * there is no index and yes, all data pages would be cached into memory


Doesn't it depend on there being space for the whole table in cache?

yes of course, otherwise spill will happen

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2078 Visits: 2931
Perry Whittle (9/25/2012)
laurie-789651 (9/25/2012)
Perry Whittle (9/25/2012)
SQLSACT (9/25/2012)
Hi all

I have a quick question regarding pages moving from disk into memory

If I select * from a table, are all pages that make up the table and indexes moved into memory?

So any subsequent query against that same table would not need to go to disk at all?

Thanks

for select * there is no index and yes, all data pages would be cached into memory


Doesn't it depend on there being space for the whole table in cache?

yes of course, otherwise spill will happen


Thanks

Spill?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19768 Visits: 17242
to TempDB

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2078 Visits: 2931
Perry Whittle (9/25/2012)
to TempDB


Thanks

So if a query is submitted to SQL and the pages need to be brought into memory from disk and there is not enough memory to accomodate those pages, SQL pushes is into tempdb, right?

Does is just push the pages that wouldn't fit in memory into tempdb or does it fulfill the entire transactions pages?

Is this where memory pressure in SQL server comes from?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86536 Visits: 45244
Perry Whittle (9/25/2012)
to TempDB


The buffer pool never spills to TempDB.

If pages are read in from disk for a table and there's not space in the buffer pool, other pages will be removed from it, maybe even pages of the same table that have already been read by the query processor.

What spills to tempDB are sorts, hashes, exchanges, spools (though they're temp structures anyway, so they can't exactly be said to spool).
Temp tables will be written to TempDB if necessary

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86536 Visits: 45244
SQLSACT (9/25/2012)
So if a query is submitted to SQL and the pages need to be brought into memory from disk and there is not enough memory to accomodate those pages, SQL pushes is into tempdb, right?


No. If pages are needed in memory and there's not enough space in the buffer pool, other data pages will be removed from the buffer pool to make space. The algorithm that is used to determine which pages to evict is roughly an LRU-2, but there are complexities and cases where pages can be removed from cache immediately after they are read in and used.

It is entirely possible, for a scan of a large table on a server with little memory, for the pages of that table to be read in, the rows on those pages processed by the query processor and then those pages removed from memory to make space for more pages from the same table.

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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19768 Visits: 17242
thanks I stand corrected

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
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