March 16, 2010 at 10:41 am
Ok, so, this is really a fairly easy question, and hopefully the answer is..
I know when letting SQL Server dynamically manage memory itself, it will eat up as much as it can (leaving up to 4-15 mb or so on average), and when it does hit this plateau, it starts to page, because the physical memory is gone, but I am not seeing that happen when using the MAX SERVER MEMORY option... I know using the MAX SERVER MEMORY setting is telling SQL Server, "hey, do not use more than this", but does that literally mean, do not use more than this, including do not eat any out of the page file if you do hit that max memory setting?..
Long story short, When SQL Server hits the ceiling on max server memory, will it then turn to the page file, like it does when memory is set to DYNAMIC?
Thanks,
March 17, 2010 at 4:45 am
The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine. The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only the memory required to initialize. As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.
See also Online-Help
March 17, 2010 at 6:14 am
As you may have guessed, that text is copied from Books Online (the SQL Server help system).
See http://msdn.microsoft.com/en-us/library/ms180797.aspx, and its references.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 22, 2010 at 10:02 am
Ok, but here's what I'm trying to understand..
The statement
"never acquires more memory than the level specified in max server memory."
Does that mean, that it will not ever use the page file then? That statement tells me, that it won't even try to go above that max server memory, and try to use the page file, like dynamic memory would? Is that what this is saying?
Thanks,
March 22, 2010 at 6:03 pm
swiedner (3/22/2010)
The statement"never acquires more memory than the level specified in max server memory."
Does that mean, that it will not ever use the page file then? That statement tells me, that it won't even try to go above that max server memory, and try to use the page file, like dynamic memory would? Is that what this is saying?
SQL Server tries very hard to avoid pushing the system as a whole into paging. Some of the details are complicated, but that is one of its primary aims.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 23, 2010 at 4:57 am
Paging is done by Windows. SQL Server cannot tell Windows to page or to not page any of the SQL Server memory. The 'lock pages in memory' right can tell Windows to prefer non-locked pages (ie non-SQL Server pages) when it is writing to the pagefile, but it cannot prevent Windows from paging out SQL Server memory.
Part of the skill in tuning a SQL Server machine is to use all the memory available but not over commit memory. If you use too much memory you will force Windows to write to the pagefile, which impacts performance.
You may see that Windows is always doing some paging, and this is normal. The counter you need to care about is pagefile write operations.
It is also worth considering setting a pagefile size of zero. This can improve performance because Windows is no longer managing the pagefile, and therefore cannot page out any SQL Server memory. IMHO any machine with more than 2GB memory should be tuned so it does not need to use a pagefile. However, if you allocate too much memory than Windows can become unstable if you do not have a pagefile. Also, no pagefile means no memory dump if Windows crashes, so you need to keep some free space available to use as a pagefile if you want to send a memory dump to Microsoft.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 23, 2010 at 5:50 am
EdVassie (3/23/2010)
Paging is done by Windows. SQL Server cannot tell Windows to page or to not page any of the SQL Server memory. The 'lock pages in memory' right can tell Windows to prefer non-locked pages (ie non-SQL Server pages) when it is writing to the pagefile, but it cannot prevent Windows from paging out SQL Server memory.
If the SQL Server process has the 'lock pages in memory' right, the buffer pool exclusively uses non-paged memory, allocated via the AWE mechanism (yes, even on 64-bit versions!) so it is even more 'locked' than allocations made via VirtualLock. An application that uses VirtualLock can still have its entire working set paged out (all at once). The OS has absolutely no power to interfere with allocations made via the AWE mechanism. Locked pages in SQL Server really are fully locked. 🙂
See http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx and http://blogs.msdn.com/slavao/archive/2005/01/29/363181.aspx and related entries.
It is also worth considering setting a pagefile size of zero.
I would never recommend this. Modern versions of Windows expect a page file, and odd things can start to happen if one is not available. I prefer to configure a server to avoid paging naturally, but to keep a sensible-sized page file anyway.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 20, 2010 at 10:57 am
Paul White NZ (3/23/2010)
EdVassie (3/23/2010)
Paging is done by Windows. SQL Server cannot tell Windows to page or to not page any of the SQL Server memory. The 'lock pages in memory' right can tell Windows to prefer non-locked pages (ie non-SQL Server pages) when it is writing to the pagefile, but it cannot prevent Windows from paging out SQL Server memory.If the SQL Server process has the 'lock pages in memory' right, the buffer pool exclusively uses non-paged memory, allocated via the AWE mechanism (yes, even on 64-bit versions!)
Then do I need to enable AWE on my 64-bit SQL Server 2008 Std/64-bit Server 2003 Std R2/9GB RAM machine? I have read that this was enabled automatically. I have the 'lock pages in memory' right set.
May 21, 2010 at 12:51 am
Maa421 (5/20/2010)
Then do I need to enable AWE on my 64-bit SQL Server 2008 Std/64-bit Server 2003 Std R2/9GB RAM machine? I have read that this was enabled automatically. I have the 'lock pages in memory' right set.
No, the 'AWE enabled' setting is ignored on 64-bit versions. Since this is Standard Edition, you do need to start the server with trace flag 845 enabled - see http://support.microsoft.com/kb/970070
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2010 at 7:35 am
Paul White NZ (5/21/2010)
Maa421 (5/20/2010)
Then do I need to enable AWE on my 64-bit SQL Server 2008 Std/64-bit Server 2003 Std R2/9GB RAM machine? I have read that this was enabled automatically. I have the 'lock pages in memory' right set.No, the 'AWE enabled' setting is ignored on 64-bit versions. Since this is Standard Edition, you do need to start the server with trace flag 845 enabled - see http://support.microsoft.com/kb/970070
Do I also need to install cumulative update 2 for SQL Server 2008 SP1?
May 21, 2010 at 7:44 am
Maa421 (5/21/2010)
Do I also need to install cumulative update 2 for SQL Server 2008 SP1?
Of course 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2010 at 8:07 am
At the risk of asking another dumb question:
Do you know if I have to install the CU's for the clients as well as the server? That is going to be incredibly inconvenient.
May 21, 2010 at 8:22 am
Maa421 (5/21/2010)
At the risk of asking another dumb question: Do you know if I have to install the CU's for the clients as well as the server? That is going to be incredibly inconvenient.
Just the server to enable this feature. (Cumulative updates tend not to include client updates anyway)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2010 at 12:42 pm
Ok, I've done the following:
installed SQL Server 2008 CU7
allow "locked pages in memory" right to the "network service" account, which is used to start sqlservr.exe
enabled the trace flag -T845 as startup parameter
According to MS KB970070, I should get the "using locked pages for buffer pool" message, but I do not.
What am I doing wrong?
May 24, 2010 at 12:56 pm
What version is reported by SELECT @@version?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply