August 19, 2008 at 7:29 pm
Hi,
I have the following setting on my server(64bit).
Total Physical Memory16,382.99 MB
Available Physical Memory 2.06 GB
Total Virtual Memory 17.40 GB
Available Virtual Memory 3.81 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys
can anyone tell me if there will be any issues with the pagefile being 2GB when Total Physical Memory is 16GB.
August 20, 2008 at 10:03 am
your paging file should be at least the amount of RAM
i think if it's too small then you may get a lot of paging and it will create a lot of I/O
never saw hard numbers for x64, but for x32 it should be twice the RAM to four times the RAM up to 4GB. there was some way to get it above 4GB that I can't remember now.
August 20, 2008 at 3:09 pm
Actually, you should be trying to avoid using the OS paging file on a database server. Database engines use a number of algorithms to determine if and when they should read a database page into the cache and remove it from that cache. If the operating system is performing paging, then something that the database thinks is only a logical read and not a physical read because it's in cache will still cause a physical read by the OS to bring that swapped memory back from the OS paging file. If it happens frequently, this will make your cache hit ratio practically meaningless and the system can have I/O bottlenecks.
If you have SQL Server 2005 Enterprise edition, you may want to check out turning on the "Lock Pages in Memory" option to help avoid this situation.
If you want to keep an eye on it, there is a "SQL Server Memory Manager Total Server Memory (KB)" performance counter that can be monitored.
August 20, 2008 at 8:51 pm
results of DBCC MEMORYSTATUS
VM Reserved23405480 kb
VM Committed327132 kb
AWE Allocated12484104 kb
Reserved Memory1024 kb
Reserved Memory In Use0 kb
my concern is, even the database is heavily used, the max memory used by SQL server is around 300mb. Total server memory allocated to SQL Server is 12GB out of 16GB.
I am not sure if changing the page file will allow SQL server to use more memory.
August 21, 2008 at 11:08 am
Do you know what your data cache hit ratio is? If it's in the mid to upper 90s, I wouldn't worry about SQL Server only using 300 Meg. The only thing that would restrict that is the Maximum Server Memory option in the Instance properties. If that setting is already more than 300 Meg, then changing your OS settings will have no effect. SQL Server will only grab as much memory as it needs, so if your cache hit ratio is high, it must not be needing any more than 300 Meg to satisfy the processes that are using it.
August 21, 2008 at 4:53 pm
cache hit ratio is > 99.86%
But my concern is with IO having very high %disk time and avg queue length
March 1, 2011 at 7:21 am
March 1, 2011 at 9:59 am
The use of AWE indicates that this is may be an x32 system with AWE enabled. When AWE is enabled, RAM is reported inaccurately in task manager or similar, however, the RAM is still actually being consumed by SQL Server. this can be seen in specific perfmon counters.
If you are running x64 and have AWE enabled, then the AWE pages are locked into RAM and won't swap. http://technet.microsoft.com/en-us/library/cc966540.aspx
Your memory status shows 12G used (AWE) so SQL Server is using much more than the apparent 300MB.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 2, 2011 at 4:54 am
Going back to the question about optimum page file size, I wish Microsoft would officially update their advice. There are a number of posts from PSS engineers that say you can safely cap your page file size at 4GB on systems with larger memory than this. Some people even say 2GB or zero page file is OK.
The logic behind this is twofold:
a) If you have a machine with over 4GB memory and you need to page out more than 4GB memory then you are overcommitting memory. You will get far better performance from reducing memory requirements than from increasing pagefile size.
b) Some people say you should have a large pagefile in case you need to take a full system dump. On W2003 and above there is a registry entry you can set to give the location of a dump file that is totally separate to the page file. Therefore page file size is no longer linked to what you might need for a dump. Also, PSS normally need no more than a Snap Dump to do their diagnostics, which can comfortably fit into a 4GB page file if you do not have a separate dump file configured. Also, if you really want the ability to have a full system dump on a machine with (say) 1TB memory (such as Dell are now selling), how are you planning to get this to Microsoft - you cannot attach it to an email - and will Microsoft ever need you to give them 1TB of dump?
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply