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: 2102 Visits: 2931
GilaMonster (9/26/2012)
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.


Thanks

If my buffer pool is never under pressure and there is sufficient memory for even my heaviest workload, is it safe to say that pages are never taken out of memory again, they'll just stay there until SQL is restarted?

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

Group: General Forum Members
Points: 87499 Visits: 45272
Maybe, but maybe not. Older pages may still get tossed out.

Unless you have buffer pool > total size of all DBs in use, you can't assume that there will be no page evictions. Also, data cache is not the only cache in the buffer pool, there are quite a few others, and then there's workspace memory needed for queries, etc

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
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: 2102 Visits: 2931
GilaMonster (9/26/2012)
Maybe, but maybe not. Older pages may still get tossed out.

Unless you have buffer pool > total size of all DBs in use, you can't assume that there will be no page evictions. Also, data cache is not the only cache in the buffer pool, there are quite a few others, and then there's workspace memory needed for queries, etc



Thanks

This whole post is under the assumption that we're only querying data, we are not doing any Inserts, Updates or Deletes, right?

The process would be different?

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

Group: General Forum Members
Points: 87499 Visits: 45272
SQLSACT (9/26/2012)
This whole post is under the assumption that we're only querying data, we are not doing any Inserts, Updates or Deletes, right?


No, not really.

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


VastSQL
VastSQL
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4419 Visits: 5157
SQLSACT (9/26/2012)
GilaMonster (9/26/2012)
Maybe, but maybe not. Older pages may still get tossed out.

Unless you have buffer pool > total size of all DBs in use, you can't assume that there will be no page evictions. Also, data cache is not the only cache in the buffer pool, there are quite a few others, and then there's workspace memory needed for queries, etc



Thanks

This whole post is under the assumption that we're only querying data, we are not doing any Inserts, Updates or Deletes, right?

The process would be different?

Thanks



For the scenario in which the whole table is in buffer i dont think there wont be much difference . Please correct me if wrong.
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: 2102 Visits: 2931
GilaMonster (9/26/2012)
SQLSACT (9/26/2012)
This whole post is under the assumption that we're only querying data, we are not doing any Inserts, Updates or Deletes, right?


No, not really.


Thanks

If SQL needs to insert/delete a rows on a specific page, that page is brought into memory and the insert/delete is done in memory?

Does that page then stay in memory?

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

Group: General Forum Members
Points: 87499 Visits: 45272
Yes and yes.

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
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: 2102 Visits: 2931
GilaMonster (9/26/2012)
Yes and yes.


This is a lot to take in for a newbie Hehe

What happens in the case of a page split?

If the page in memory cannot accomodate the insert, does SQL then just bring an extra page blank page from disk into memory?

When SQL Server removes a page from memory, does SQL put it right back where it was?

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 14396
SQLSACT (9/26/2012)
GilaMonster (9/26/2012)
Yes and yes.


This is a lot to take in for a newbie Hehe

Yes, and it takes years to get to the bottom of it. Oh yeah, and Microsoft is on a schedule where they try to release a new version every 3-4 years :-)

Microsoft® SQL Server® 2008 Internals

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87499 Visits: 45272
SQLSACT (9/26/2012)
If the page in memory cannot accomodate the insert, does SQL then just bring an extra page blank page from disk into memory?


Why would it need to read a blank page off disk?

When SQL Server removes a page from memory, does SQL put it right back where it was?


Put it back? When a page is removed from memory, it's just removed, it doesn't need to be put anywhere. It's just a cached copy.

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


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