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-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0111
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364710241024
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364710241024
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)0327673276732767
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
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