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 ««123»»

SQL Disk to Memory Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 3:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 1,377, Visits: 2,682
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
Post #1364508
Posted Wednesday, September 26, 2012 3:10 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:25 PM
Points: 43,008, Visits: 36,164
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 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 #1364512
Posted Wednesday, September 26, 2012 5:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 1,377, Visits: 2,682
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
Post #1364578
Posted Wednesday, September 26, 2012 7:29 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:25 PM
Points: 43,008, Visits: 36,164
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 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 #1364663
Posted Wednesday, September 26, 2012 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 2,609, Visits: 3,903
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.
Post #1364665
Posted Wednesday, September 26, 2012 8:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 1,377, Visits: 2,682
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
Post #1364753
Posted Wednesday, September 26, 2012 8:50 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:25 PM
Points: 43,008, Visits: 36,164
Yes and yes.



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 #1364757
Posted Wednesday, September 26, 2012 8:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 1,377, Visits: 2,682
GilaMonster (9/26/2012)
Yes and yes.


This is a lot to take in for a newbie

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
Post #1364760
Posted Wednesday, September 26, 2012 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
SQLSACT (9/26/2012)
GilaMonster (9/26/2012)
Yes and yes.


This is a lot to take in for a newbie

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
Post #1364819
Posted Wednesday, September 26, 2012 10:33 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:25 PM
Points: 43,008, Visits: 36,164
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 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 #1364825
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse