SQL Disk to Memory

Question

SQL Disk to Memory

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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 SSC-Insane Points: 21838 More actions · Answer 3

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 SSC Guru Points: 182279 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Paul White SSC Guru Points: 150468 More actions · Answer 6

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?

No, there's no need to do that. SQL Server maintains a 'free list' of blank/reusable pages in the buffer pool. Anything that needs a fresh page takes one from the free list. There are background processes like CHECKPOINT and lazy writer which try to keep the free list at a reasonable level.

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

If a page has been modified in memory then yes it will be written back to disk before being discarded. If by 'right back where it was' you mean the same physical location in the data file on disk, then yes.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

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

Thanks

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

Wouldn't it need another page to accomodate the page split?

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

This is where I'm confused, I thought that the actual page is moved completely off the disk and placed into memory and then placed back onto the disk. If a page is modified in memory, doesn't the changes need to be reflected on disk?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

SQLSACT (9/27/2012)
Why would it need to read a blank page off disk?
Wouldn't it need another page to accomodate the page split?

Sure, but why would it need to read a blank 8k off disk? What would be the point in going to the data file to fetch 8k of nothing?

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

This is where I'm confused, I thought that the actual page is moved completely off the disk and placed into memory and then placed back onto the disk. If a page is modified in memory, doesn't the changes need to be reflected on disk?

[/quote][/quote]

Move off disk as in when it's read into memory the page is deleted from the data file? Why would it work that way?

When you open a text file in notepad, the file on disk isn't deleted.

If a page is modified in memory then the changes need to be written back to the data file, 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 SSC-Insane Points: 21838 More actions · Answer 9

GilaMonster (9/27/2012)
SQLSACT (9/27/2012)
Why would it need to read a blank page off disk?
Wouldn't it need another page to accomodate the page split?
Sure, but why would it need to read a blank 8k off disk? What would be the point in going to the data file to fetch 8k of nothing?
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
This is where I'm confused, I thought that the actual page is moved completely off the disk and placed into memory and then placed back onto the disk. If a page is modified in memory, doesn't the changes need to be reflected on disk?

[/quote]

Move off disk as in when it's read into memory the page is deleted from the data file? Why would it work that way?

When you open a text file in notepad, the file on disk isn't deleted.

If a page is modified in memory then the changes need to be written back to the data file, yes,

[/quote]

Move off disk as in when it's read into memory the page is deleted from the data file? Why would it work that way?
When you open a text file in notepad, the file on disk isn't deleted.

Understood, so any page in memory is just a representation of the actual page on disk

If a page is modified in memory then the changes need to be written back to the data file, yes

When a page is modified in memory, does SQL replace the corresponding page on disk with the changed page in memory?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

The changed page is written back, replacing the page that was there.

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