December 19, 2005 at 12:15 am
SQL Server memory usage question: I have 10GB physical memory, on a dedicated SQL Server. The Target Server Memory and Total Server Memory are both 5.2 GB. Taskmgr.exe shows the sqlservr service is using 142,248K. The database is over 100GB with some very memory intensive operations.
SQL Server 2000 enterprise sp4 awe enabled
W2k3 sp1
Server BOOT.INI
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
SQL Instance Properties (sp_configure)
NAME | MIN | MAX | CONFIG | RUN |
max server memory (MB) | 4 | 2147483647 | 10238 | 10238 |
awe enabled | 0 | 1 | 1 | 1 |
min memory per query (KB) | 512 | 2147483647 | 1024 | 1024 |
min server memory (MB) | 0 | 2147483647 | 0 | 0 |
System Summary (msinfo32.exe)
ITEM | VALUE |
Total Physical Memory | 10,238.17 MB |
Available Physical Memory | 345.11 MB |
Total Virtual Memory | 3.79 GB |
Available Virtual Memory | 2.26 GB |
Page File Space | 10.00 GB |
December 19, 2005 at 5:14 am
Does the account used for the MSSQLSERVER service have "Lock Pages In Memory" privileges. Even if the account is an administrator it won't have these by default.
SQL 2000 SP4 is build 2039. There is a bug that only allows half the memory to be read in AWE machines so you need to move to hotfix 2040.
December 19, 2005 at 7:21 am
A couple of other things.
1) When you use AWE memory, disregard the value in Task Manager. You cannot rely on it to be correct.
2) If Target and Total Memory are equal and you think you are having issues, check the cache hit counters as well. Typically, if these are good, no worries. However, as David pointed out, there is a known bug with AWE usage in SP4.
K. Brian Kelley
@kbriankelley
December 19, 2005 at 12:02 pm
Roger,
where's actually an issue with task manager showing the wrong amount of memory used by sql server if your using more than 3gb. Use Performance monitor and you see the real amount used.
Markus
[font="Verdana"]Markus Bohse[/font]
December 19, 2005 at 12:17 pm
Thanks for the info.
Researching "How to configure memory for more than 2 GB in SQL Server" http://support.microsoft.com/kb/274750/ revealed the following "The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM".
Is it correct to enable AWE in SQL Server without /PAE in the Boot.ini for Windows Server 2003 enterprise edition (SP1) running SQL Server 2000 enterprise edition (SP4 + KB899761) with 10GB of physical memory?
P.S. The sysadmins are checking the "lock pages in memory" permissions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply