Have a server with 4GB RAM, running Win2K server, SQL2000 SE. I have not
implemented any /3GB, PQE or AWE switches because of the obvious OS and SQL
version limitations.
When I open Enterprise manager > SQL Server Properties > General tab, I see
"OS memory: 3775(MB)", which seems to be close to the 4GB physically
installed. I figure the difference is overhead, etc.
I have tinkered with the Memory settings, including a "Dynamically
configure..." range, and a fixed memory size. I've tried just about every
permutation to try and cajole it into using more than the 1GB it seems to
only want to use. There are probably better places to check how much memory
it is using besides the Task Manager, however, the Task Manager shows 1069mb
(just over 1GB) for sqlserver.exe. So, I figure that even if Task Manager
isn't the most accurate, it probably isn't off by a factor of 2. How can I
get SQL to use the 2GB it's supposed to be able to use? I can tell you that
the performance of this server is really bad (and there are many other
opportunities for improvement - the subject of another interesting
discussion) but with such bad performance, wouldn't it try to use more memory
- especially when serving a 30GB+ database? There has to be something I'm
missing.
Thanks in advance, ...

Task Manager is not a reliable tool to tell you the amount of memory SQL
Server is using. Use performance monitor instead. In performance monitor,
select counter SQLServer:Memory Manager \ Total Server Manager (KB). This
counter shows the amount of memory used by the SQL Server buffer pool, which
accounts for most of the memory used by SQL Server.

Linchi

"Larry Epn" wrote:

Thanks. Did that.
Answer is "1023032", still looks like about a GB.
Server is currently very active.
Thanks,
Larry

Hi Larry,
Yes, your SQL Server was occupying 1GB memory. If you want to have your SQL
Server use a fixed 2GB memory, you can set min server memory and max server
memory to the same 2GB. You may refer to:
Server Memory Options
http://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx

I noticed that you said that the performance of the server was really bad.
I would recommend that you test your server with your SQL Server database
under an estimated workload to see if there are any performance issue. You
can also refer to this article to troubleshoot performance issue when you
encounter it:
How to troubleshoot SQL Server performance issues
http://support.microsoft.com/kb/298475/en-us

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Thank you for your post.
I had already also tried (as one of my many attempts at every permutation
possible in the SQL Ent Mgr memory configuration applet) to do that, and it
had no effect at raising the amount of CPU used.
This is one of those problems we've had for a while and I've made various
feeble attempts recently at finding a solution (e.g., research and forums
like this)....I am thinking I'm going to have to get Micro$oft involved with
a paid $upport call. I'm sure there will be a hidden configuration governor
somewhere and I'll be at fault.
Thanks for any additional insights,
Larry

The only governors are the ones that dictate how much memory a specific
edition can use. It sounds like you do not in fact have standard edition but
MSDE instead which will only use 1GB regardless of how much you have in
total. If you do in fact have Standard edition then all of the defaults
should allow you to use up to 2GB including the MemToLeave area which will
make it look like you are only using 1.7GB if you look in Task Manager. Make
sure you do not have the Max Memory set to anything other than the default.
SQL Server can swap memory out if the OS is calling for more. Are there any
apps running on that server other than SQL Server? Can you post the results
of sp_configure?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Interesting observation on it being MSDE, however if that were the case, I
don't think it would allow a 30Gb+ database size. Pretty sure it's standard
edition, but I was not the one who bought/installed it. As soon as I can log
into that server, Is this the info you wanted? HOpefully there's something
meaningful in there.

affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
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...

Just run select @@version in a query window. It will give you the version
and edition.

Chris

"Larry Epn" wrote in message
news:FD3A92AB-8C7F-41DE-B479-852686CB6AB9@microsoft.com...

Thanks that's good to know.
Here is the result:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
5.0 (Build 2195: Service Pack 4)

Which means you may be failing into this trap
http://support.microsoft.com/kb/899761/

Chris

Since MS08-040 came out I would be looking at build 2050 anyway.

"Larry Epn" wrote in message
news:626CD3C6-09FF-419F-AD8B-9D4DF7E3D41D@microsoft.com...