October 20, 2005 at 6:54 pm
I have a SQL server installation on a Win 2000 server which was in use time to time. Now we need to utilize this server as the main Prod box is too full. Today I start looking at the box and realized that the memory is in use more then 80% all the time - 8G - there's nothing running on the box then mmc.exe and sqlservr.exe with no users - only me... If I stop the sql server, the memory usage goes back to normal. The problem is that I need to install some applications and there's no memory for the install to run. In the server properties, I can see that SQL server uses fixed memory but this is much less then what the task Manager shows... Rebooting the box did not change the situation...
What else I can look to fix the situation.
Thanks a lot for the help.mj
October 20, 2005 at 7:37 pm
Please provide sp_configure results as I want to see max and min server memory values.
October 20, 2005 at 7:47 pm
Task Manager does not provide accurate memory usage information for AWE. Specificly, the memory indicated for sqlservr.exe is never correct.
You must use the Performance Monitor to retrieve information on SQL Server memory usage and available memory. Use the Total Server Memory (KB) performance counter to determine the actual used memory.
SQL = Scarcely Qualifies as a Language
October 20, 2005 at 9:15 pm
I'll run again sp_configure tomorrow morning.
The memory usage is very high even in the Perf Mon. The machine is all pegged - the installer from my app cannot complete as there's not enough memory...
I have another test server with the same configuration, but the memory usage there is much, much less... something is wrong with this box...
Thanks a lot, mj
October 21, 2005 at 9:09 am
Bellow are the values from sp_config. I have very similar values on another server and its memory showing much less then this one.
What else I can look to determine what is wrong with the machine:
Thanks a lot,mj
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 1 1
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 1024 1024
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 1024 1024
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 32767 32767
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
October 24, 2005 at 12:04 am
What sets my warning bells on is that you have AWE enabled and yet only max server memory configured to 1024. I am not sure what effects that will have. Have you tried setting max server memory to something larger, such as 4092? Then see if the server aquires that much memory but not more.
Also, just to be sure, you are not running multiple instances here are you?
October 24, 2005 at 10:38 am
No multiple instances. I was trying to install a 3rd party app and there was not enpugh memory - I stopped the SQL server and did it, but I have never had such problem - I have the same config on another similar server and no problems there like this one.
Looking at your suggestion - from BOL - "the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 4 MB". My max one is 1024... I guess somebody tried to use a fix memory for the SQL server and did not set it right... Let me experiment with this and I'll post my results.
Thanks a lot for the help.
mj
October 24, 2005 at 4:20 pm
Is SQL2K 'standard' or 'enterprise' ? If it's 'standard' then I cannot see how it can address more than 2 Gb (actually only use 1.6 to 1.8 Gb) ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply