December 29, 2015 at 10:28 pm
Hello All,
I have a simple question:
reading a book called "Pro SQL Server Administration" on page 47 it reads down by the Lock Pages to Memory portion it says: "if windows is experiencing memory pressure, it will attempt to page data from RAM into virtual memory on disk. this can cause an issue within sql server. In order to provide acceptible performance, SQL server caches recently used Data pages in the buffer cache , which is an area of memory reserved by the Database Engine. In Fact, all data pages are read from the buffer cache, even if they need to be read from disk first. If windows decides to move pages from the buffer cache out to disk, the performance of your instance will be severely impaired".
so stupid questions below:
1.for the first part it says this will happen "if windows is experiencing memory pressure", does that mean that if you have a 256 gig memory server, and use lets say 64 gigs is for system memory and 192 (i know a little high but think if its an intense sql server for the example), if 64 of the gigs is at its peak 100 memory, what about the 192 set for SQL server (for the example you set MAX memory dedicated to sql 192)?? or is that something entirely different?
2. "All data pages are read from the buffer cache" - does that mean pages recently written/created/read, or frequently used are from the buffer cahce or do they really mean ALL pages are located in the buffer cache?
3. so adding the account that runs sql server engine, you add it to the lock pages in memory part of local security, got it, so does that mean windows will not put pages from memory to disk? NONE of the pages? even the ones not used or only keep the pages that are used most (read and written to)?
thanks in advanced 🙂
December 30, 2015 at 2:44 am
I don't really understand question 1. But the quote from the book's not entirely correct. If Windows is under memory pressure, first it will tell SQL (and other apps) to reduce their memory usage. SQL's really good about complying with this request. It's only if SQL doesn't (or can't) respond, that Windows may page SQL's memory out.
ALL data pages are read from the buffer pool. The query processor doesn't know what a disk is. For the query processor to use a page, it's got to be read into the buffer pool first.
If you give the service account locked memory permissions, Windows will not page SQL's memory to disk.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply