max server memory not using page file

  • 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,

  • 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

  • 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.

  • 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,

  • 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.

  • 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

  • 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 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.

  • 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 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?

  • Maa421 (5/21/2010)


    Do I also need to install cumulative update 2 for SQL Server 2008 SP1?

    Of course 🙂

  • 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.

  • 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)

  • 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?

  • What version is reported by SELECT @@version?

Viewing 15 posts - 1 through 14 (of 14 total)

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