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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4646 Visits: 2969
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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50658 Visits: 17653
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50658 Visits: 17653
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4646 Visits: 2969
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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50658 Visits: 17653
to TempDB

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

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

Group: General Forum Members
Points: 4646 Visits: 2969
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 (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211290 Visits: 46255
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 (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211290 Visits: 46255
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
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50658 Visits: 17653
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