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,
Larry

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:

> 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,
> Larry

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!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to 
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for 
non-urgent issues where an initial response from the community 
or a Microsoft Support Engineer within 1 business day is acceptable. 
Please note that each follow up response may take approximately 
2 business days as the support professional working with you may 
need further investigation to reach the most efficient resolution. 
The offering is not appropriate for situations 
that require urgent, real-time or phone-based interactions or complex 
project analysis and dump analysis issues. Issues of this nature are best 
handled working with a dedicated Microsoft Support Engineer by 
contacting Microsoft Customer Support Services (CSS) at 
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

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


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:46BC6E17-A618-4372-86F9-FE489E9150A8@microsoft.com...
> 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

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	-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	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	32	0	0
max server memory (MB)	4	2147483647	3023	3023
max text repl size (B)	0	2147483647	15000000	15000000
max worker threads	32	32767	32767	32767
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	0	0
nested triggers	0	1	1	1
network packet size (B)	512	32767	4096	4096
open objects	0	2147483647	0	0
priority boost	0	1	1	1
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
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	1
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

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

Chris

"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:FD3A92AB-8C7F-41DE-B479-852686CB6AB9@microsoft.com...
> 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 -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 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 3023 3023
> max text repl size (B) 0 2147483647 15000000 15000000
> max worker threads 32 32767 32767 32767
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 32767 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> 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 1
> 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
>

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" <larryepn@noemail.noemail> wrote in message 
news:626CD3C6-09FF-419F-AD8B-9D4DF7E3D41D@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)
>

Man, you had me going for a minute, unfortunately it does not apply because 
AWE is not configured on this server:
name          	minimum	maximum	config_value	run_value
awe enabled	0	1	0               	0

Also, this computer has 4Gb, and I am just trying to get it to use 2Gb 
instead of the 1Gb it is using, so even 50% of the available memory would be 
a "good thing" in my particular case.  Right now it will only use 25%.

Larry

No you don't have AWE enabled but you do the the "set working set size" 
enabled and it should be off. Change that back to the default and you 
probably have to restart but I bet that will fix it.


-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:79448FCB-7570-4837-B5D4-AB2195C9FE4A@microsoft.com...
> Man, you had me going for a minute, unfortunately it does not apply 
> because
> AWE is not configured on this server:
> name          minimum maximum config_value run_value
> awe enabled 0 1 0               0
>
> Also, this computer has 4Gb, and I am just trying to get it to use 2Gb
> instead of the 1Gb it is using, so even 50% of the available memory would 
> be
> a "good thing" in my particular case.  Right now it will only use 25%.
>
> Larry
>
>

Thanks,
I made that change:
sp_configure 'set working set size',0
reconfigure
(restarted sqlserver.exe)
confirmed current runtime setting = 0
Ran a bunch of huge queries, large transactions, beat up the server, etc...
Memory usage still seems stuck at right at 1,062Mb.

Anyone, anyone, anyone ? :)
Larry

"Andrew J. Kelly" wrote:

> No you don't have AWE enabled but you do the the "set working set size" 
> enabled and it should be off. Change that back to the default and you 
> probably have to restart but I bet that will fix it.
> 
> 
> -- 
> Andrew J. Kelly    SQL MVP
> Solid Quality Mentors
> 
> 
> "Larry Epn" <larryepn@noemail.noemail> wrote in message 
> news:79448FCB-7570-4837-B5D4-AB2195C9FE4A@microsoft.com...
> > Man, you had me going for a minute, unfortunately it does not apply 
> > because
> > AWE is not configured on this server:
> > name          minimum maximum config_value run_value
> > awe enabled 0 1 0               0
> >
> > Also, this computer has 4Gb, and I am just trying to get it to use 2Gb
> > instead of the 1Gb it is using, so even 50% of the available memory would 
> > be
> > a "good thing" in my particular case.  Right now it will only use 25%.
> >
> > Larry
> >
> > 
> 
>

Well something certainly isn't adding up here. What are the values of both 
the target and total server memory for the SQL Server memory counters? How 
much free memory does Perfmon show?  If you really are querying more than 
2GB worth of different data you should see the memory usage go to about 
1.7GB with that configuration.  I see the Max Memory was set to ~3GB which 
is more than what you have but just to rule it out you may want to rest that 
back to the default of the max as well.  Are you sure there isn't some other 
app on the server that may be eating up memory?  Are there any settings in 
the boot.ini that are not default?  You should really think about upgrading 
the OS and SQL Server in the near future anyway. Std edition in 2005 & 2008 
can use the max memory that the OS can access and if there was anything 
funky with the OS or SQL builds an upgrade should surely fix that.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:A2B07884-5E2B-48EC-AA5B-897523AAD809@microsoft.com...
> Thanks,
> I made that change:
> sp_configure 'set working set size',0
> reconfigure
> (restarted sqlserver.exe)
> confirmed current runtime setting = 0
> Ran a bunch of huge queries, large transactions, beat up the server, 
> etc...
> Memory usage still seems stuck at right at 1,062Mb.
>
> Anyone, anyone, anyone ? :)
> Larry
>
> "Andrew J. Kelly" wrote:
>
>> No you don't have AWE enabled but you do the the "set working set size"
>> enabled and it should be off. Change that back to the default and you
>> probably have to restart but I bet that will fix it.
>>
>>
>> -- 
>> Andrew J. Kelly    SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Larry Epn" <larryepn@noemail.noemail> wrote in message
>> news:79448FCB-7570-4837-B5D4-AB2195C9FE4A@microsoft.com...
>> > Man, you had me going for a minute, unfortunately it does not apply
>> > because
>> > AWE is not configured on this server:
>> > name          minimum maximum config_value run_value
>> > awe enabled 0 1 0               0
>> >
>> > Also, this computer has 4Gb, and I am just trying to get it to use 2Gb
>> > instead of the 1Gb it is using, so even 50% of the available memory 
>> > would
>> > be
>> > a "good thing" in my particular case.  Right now it will only use 25%.
>> >
>> > Larry
>> >
>> >
>>
>>

Yes, it does not add up.
I just reconfirmed all of this.
Here is boot.ini:
--------------------------------------
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" 
/fastdetect
--------------------------------------
Perfmon shows available MBytes = 2148
so 2Gb are just sitting there.

I made the changes you suggested (set min = 0, max = 2Gb), and restarted sql 
service.  I run a halacious join query and watch the memory increase, 
increase,increase, and then in task manager it gets stuck at its usual 
1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, the 
%Disk Time goes through the roof.  This is what happens ALL the time, 
regardless of what settings are changed.

Your suggestion to move to later versions is valid, but I don't see why I 
can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!!

Does Microsoft have any ideas?

Thanks,
Larry

> I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> sql

Actually I said to put the max back at the default which is 2147483647. No 
tthat I think it is the problem but just wanted to eliminate it. If SQL is 
taking 1GB and there is 2GB left where is the other 1GB going? What does 
task manager say is using that other GB?

The only other thing I can think of is that someone set the -g startup 
option to a non-default value as well. If you look at the server properties 
in EM you can see the startup options. See if there is a -g and if so what 
the value is. If it is more than 256 you might want to question why and that 
will be the problem. If not I suggest you contact MS PSS so they can log 
into your system and have a look. Other than the AWE issue with SP4 I have 
not seen any bugs that would cause this behavior.  Please let us know what 
you come up with.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
> Yes, it does not add up.
> I just reconfirmed all of this.
> Here is boot.ini:
> --------------------------------------
> [boot loader]
> timeout=30
> default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server"
> /fastdetect
> --------------------------------------
> Perfmon shows available MBytes = 2148
> so 2Gb are just sitting there.
>
> I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> sql
> service.  I run a halacious join query and watch the memory increase,
> increase,increase, and then in task manager it gets stuck at its usual
> 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, the
> %Disk Time goes through the roof.  This is what happens ALL the time,
> regardless of what settings are changed.
>
> Your suggestion to move to later versions is valid, but I don't see why I
> can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!!
>
> Does Microsoft have any ideas?
>
> Thanks,
> Larry

Hi Larry,
I think that there are some misunderstandings among these posts. Though you set your SQL 
Server to use a fixed memory size, SQL Server will not initially allocate 2GB memory on startup 
or when your SQL Server does not really consume so much memory. Fixed memory means that 
your SQL Server can use the memory to the fixed size and will not release/increase the memory 
after the memory reaches to the fixed size.

From SQL Server Books Online, http://msdn.microsoft.com/en-us/library/aa196734
(SQL.80).aspx,  we can find the following description regarding "min server memory":
Use min server memory to guarantee a minimum amount of memory to an instance of SQL 
Server. SQL Server will not immediately allocate the amount of memory specified in min server 
memory on startup. However, after memory usage has reached this value due to client load, 
SQL Server cannot free memory from the allocated buffer pool unless the value of min server 
memory is reduced.

Then we look back to your issue, I noticed that you changed the your SQL Server to use min=0 
and max=2GB, in this case, SQL Server will dynamically allocate memory and it is usual that 
SQL Server will sometimes release memory though it has not reached to the maximum memory 
size. Please still set your SQL Server's min and max memory to the same size 2 GB and wait to 
see the memory situation when there are many heavy workload. 

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

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Startup Parameters:
-d, -e, -l
no -g.

sp_configure
max server memory (MG) = 2047
Now, that 2047 matches the setting I put into the Dynamic Memory, Max 
setting in Ent Mgr for SQL.  Of course, this doesn't match your setting of 
2147483647 which is some very large number (2Tb?), so I did this:

sp_configure 'max server memory', 2147483647
reconfigure

I will restart sqlservr.exe at the lunch break when I can kick everyone off. 
 I will repost near end of day.
Thanks




"Andrew J. Kelly" wrote:

> > I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> > sql
> 
> Actually I said to put the max back at the default which is 2147483647. No 
> tthat I think it is the problem but just wanted to eliminate it. If SQL is 
> taking 1GB and there is 2GB left where is the other 1GB going? What does 
> task manager say is using that other GB?
> 
> The only other thing I can think of is that someone set the -g startup 
> option to a non-default value as well. If you look at the server properties 
> in EM you can see the startup options. See if there is a -g and if so what 
> the value is. If it is more than 256 you might want to question why and that 
> will be the problem. If not I suggest you contact MS PSS so they can log 
> into your system and have a look. Other than the AWE issue with SP4 I have 
> not seen any bugs that would cause this behavior.  Please let us know what 
> you come up with.
> 
> -- 
> Andrew J. Kelly    SQL MVP
> Solid Quality Mentors
> 
> 
> "Larry Epn" <larryepn@noemail.noemail> wrote in message 
> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
> > Yes, it does not add up.
> > I just reconfirmed all of this.
> > Here is boot.ini:
> > --------------------------------------
> > [boot loader]
> > timeout=30
> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
> > [operating systems]
> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server"
> > /fastdetect
> > --------------------------------------
> > Perfmon shows available MBytes = 2148
> > so 2Gb are just sitting there.
> >
> > I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> > sql
> > service.  I run a halacious join query and watch the memory increase,
> > increase,increase, and then in task manager it gets stuck at its usual
> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, the
> > %Disk Time goes through the roof.  This is what happens ALL the time,
> > regardless of what settings are changed.
> >
> > Your suggestion to move to later versions is valid, but I don't see why I
> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!!
> >
> > Does Microsoft have any ideas?
> >
> > Thanks,
> > Larry 
> 
>

Thanks Charles.  I understand that I should not expect the memory to 
instantly reach the minimum.  That is not the problem.  The problem is that 
it never exceeds 1Gb even though the I/O to the F-drive (where the data files 
are stored) is totally maxxed-out.  Now, this is a big database, and even 2Gb 
will not be enough for some of the queries in this system (which are also 
being evaluated for performance as a separate project), but having 2Gb will 
certainly be better than 1Gb.  I can assure you that the problem is not that 
the memory is not needed.  It is needed.  I'm confident that this 30Gb+ 
database could use much more than even 2Gb to run at its best.  (And as a 
separate project, we're evaluating new hardware, Win2008 and SQL2008, so no 
need to mention those options.)  I'm trying to alleviate the pain in the 
short term.
The problem is that no matter which settings I put in place (so far), I 
can't get SQLSERVR to use more than 1Gb.  No matter which settings, it will 
never exceed 1Gb.  No matter what I put in for min or max, etc, it will never 
exceed 1Gb RAM even though there are complex queries running throughout the 
day.  I can run a single complex query on one of the bigger tables (250K 
rows) with lots of joins, etc, and I can literally watch the memory increase. 
 It goes 100M, 200M, ......800M, 900M, 1000M, 1069M and then it just stays at 
1069M even though the query is still running for several more minutes.  And 
during this time when the RAM is stuck at 1069M, the disk utilization goes to 
700%, which would indicate lots of paging from RAM to disk, so I'm sure that 
if SQL could get access to it, it would use more than 1GB, so I hope I have 
clearly shown that the problem is not that SQL does not need the RAM.
Regards,
Larry

""Charles Wang [MSFT]"" wrote:

> Hi Larry,
> I think that there are some misunderstandings among these posts. Though you set your SQL 
> Server to use a fixed memory size, SQL Server will not initially allocate 2GB memory on startup 
> or when your SQL Server does not really consume so much memory. Fixed memory means that 
> your SQL Server can use the memory to the fixed size and will not release/increase the memory 
> after the memory reaches to the fixed size.
> 
> From SQL Server Books Online, http://msdn.microsoft.com/en-us/library/aa196734
> (SQL.80).aspx,  we can find the following description regarding "min server memory":
> Use min server memory to guarantee a minimum amount of memory to an instance of SQL 
> Server. SQL Server will not immediately allocate the amount of memory specified in min server 
> memory on startup. However, after memory usage has reached this value due to client load, 
> SQL Server cannot free memory from the allocated buffer pool unless the value of min server 
> memory is reduced.
> 
> Then we look back to your issue, I noticed that you changed the your SQL Server to use min=0 
> and max=2GB, in this case, SQL Server will dynamically allocate memory and it is usual that 
> SQL Server will sometimes release memory though it has not reached to the maximum memory 
> size. Please still set your SQL Server's min and max memory to the same size 2 GB and wait to 
> see the memory situation when there are many heavy workload. 
> 
> Hope this helps. If you have any other questions or concerns, please feel free to let me know. 
> Have a nice day~!
> 
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================
> Delighting our customers is our #1 priority. We welcome your 
> comments and suggestions about how we can improve the 
> support we provide to you. Please feel free to let my manager 
> know what you think of the level of service provided. You can 
> send feedback directly to my manager at: msdnmg@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
> 
> 
> 
> 
>

Did you look to see what Task manager shows the other 1GB being used for? 
Are their any other apps (other than normal windows services etc.) running 
on this server?

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:069879B4-1C5C-4E2A-A4C2-C675E0998C07@microsoft.com...
> Startup Parameters:
> -d, -e, -l
> no -g.
>
> sp_configure
> max server memory (MG) = 2047
> Now, that 2047 matches the setting I put into the Dynamic Memory, Max
> setting in Ent Mgr for SQL.  Of course, this doesn't match your setting of
> 2147483647 which is some very large number (2Tb?), so I did this:
>
> sp_configure 'max server memory', 2147483647
> reconfigure
>
> I will restart sqlservr.exe at the lunch break when I can kick everyone 
> off.
> I will repost near end of day.
> Thanks
>
>
>
>
> "Andrew J. Kelly" wrote:
>
>> > I made the changes you suggested (set min = 0, max = 2Gb), and 
>> > restarted
>> > sql
>>
>> Actually I said to put the max back at the default which is 2147483647. 
>> No
>> tthat I think it is the problem but just wanted to eliminate it. If SQL 
>> is
>> taking 1GB and there is 2GB left where is the other 1GB going? What does
>> task manager say is using that other GB?
>>
>> The only other thing I can think of is that someone set the -g startup
>> option to a non-default value as well. If you look at the server 
>> properties
>> in EM you can see the startup options. See if there is a -g and if so 
>> what
>> the value is. If it is more than 256 you might want to question why and 
>> that
>> will be the problem. If not I suggest you contact MS PSS so they can log
>> into your system and have a look. Other than the AWE issue with SP4 I 
>> have
>> not seen any bugs that would cause this behavior.  Please let us know 
>> what
>> you come up with.
>>
>> -- 
>> Andrew J. Kelly    SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Larry Epn" <larryepn@noemail.noemail> wrote in message
>> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
>> > Yes, it does not add up.
>> > I just reconfirmed all of this.
>> > Here is boot.ini:
>> > --------------------------------------
>> > [boot loader]
>> > timeout=30
>> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
>> > [operating systems]
>> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 
>> > Server"
>> > /fastdetect
>> > --------------------------------------
>> > Perfmon shows available MBytes = 2148
>> > so 2Gb are just sitting there.
>> >
>> > I made the changes you suggested (set min = 0, max = 2Gb), and 
>> > restarted
>> > sql
>> > service.  I run a halacious join query and watch the memory increase,
>> > increase,increase, and then in task manager it gets stuck at its usual
>> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, 
>> > the
>> > %Disk Time goes through the roof.  This is what happens ALL the time,
>> > regardless of what settings are changed.
>> >
>> > Your suggestion to move to later versions is valid, but I don't see why 
>> > I
>> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) 
>> > !!!
>> >
>> > Does Microsoft have any ideas?
>> >
>> > Thanks,
>> > Larry
>>
>>

I'm following this thread and the problem is really interesting.

However, in your following reply, I wish you'd tell us the "Total Server 
Memory" and "Target Server Memory" counters' results.

I know, you gave us lots of proves about the memory consumption. But the 
results of these counters would definitely prove you had enough RAM but SQL 
Server can't take advantage of it.

-- 
Ekrem Önsoy



"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
> Yes, it does not add up.
> I just reconfirmed all of this.
> Here is boot.ini:
> --------------------------------------
> [boot loader]
> timeout=30
> default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server"
> /fastdetect
> --------------------------------------
> Perfmon shows available MBytes = 2148
> so 2Gb are just sitting there.
>
> I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> sql
> service.  I run a halacious join query and watch the memory increase,
> increase,increase, and then in task manager it gets stuck at its usual
> 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, the
> %Disk Time goes through the roof.  This is what happens ALL the time,
> regardless of what settings are changed.
>
> Your suggestion to move to later versions is valid, but I don't see why I
> can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!!
>
> Does Microsoft have any ideas?
>
> Thanks,
> Larry

Hi Larry,
Thank you for your detail response for clarifying the issue.

So your real concern is that your SQL Server can only consume maximum 1069MB physical memory on your server that has actually 4GB memory, though you had configured your SQL Server to 
use minimum 2GB memory. Your expectation is to have your SQL Server use almost 2GB memory.
If I have misunderstood, please let me know.

By design, Windows OS will cover 2GB memory for system use and the left 2GB memory is for all Windows applications. SQL Server is one of those applications and it should be normal that it 
cannot utilize the maximum 2GB memory since other applications or services will also consume some memory. Could you please check how much memory is used other applications running on 
your server?  

If there are some heavy load T-SQL queries, I recommend that you enable /3GB switch in the Boot.ini file on your Windows Server, for example:
[Operating Systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows Server 2003" /fastdetect /3GB

In this case, Windows OS will keep 1GB for OS and leave 3GB for user mode applications. After that configure your SQL Server minimum memory to 2GB.

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Yes, it appears you understand the situation perfectly.  
There are no other significant applications that run on this server.  It is 
not a domain controller, nor an IIS Server, nor a DNS server, etc.  The 
largest memory allocation in task manager is sqlservr.exe at 1,064Mb, with 
rtvscan.exe a distant second at 52Mb. 
Total Mem Usage (task manager): 1411372K / 5794148K (of course the amount 
above the 4Gb is the pagefile.)
In this case, if all apps were to share the 2Gb, then I would expect 
sqlserver.exe to at least be using around 1.7Gb calculated as 2Gb (available 
for all Windows applications) - 1411372 (current total usage) + (1067400 
current sql usage) = 1.66Gb for sqlservr.exe.  (Yes, I know I've rounded 
badly and it is all to the advantage of reduced space for sqlservr.exe for 
sake of argument, so no need to clarify that a Gb is > 1000Mb, etc) 
So after all this, there is still no explanation that sqlservr.exe is not 
using all the memory it should.
Next I will try the 3Gb option and see what happens....even though 
theoretically, I shouldn't need to do this - but I'm willing to try just 
about anything.
Larry

I was going to follow your suggestion and set the /3GB switch in boot.ini.  I 
decided to read a bit more of what this would do...seeing as I am working on 
a server around 1300 miles from my house and it's a really long drive to fix 
a boot problem...

I came across this article:
http://technet.microsoft.com/en-us/library/bb124810.aspx
In here it says to not use this switch on Windows 2000 Standard because it 
is not supported.

Plus, this really doesn't get to the root of the problem anyway, which is 
why the server won't use the 1.65Gb clearly available to it according to 
everything I've read on MSDN and other forums or within this thread.

Any last attempts before I call Microsoft for support?  
(Sorry for the attitude, but I am getting to the point of believing the 
conspiracy theory that Micro$oft burries little goodies like this to increase 
support revenue...)

Larry

Everything you're saying about how much memory is being used, seems to be
evidenced only from Task Manager.  A couple of questions have asked about
what performance monitor says about total server memory and target server
memory, but I haven't seen your answer to those questions.  I'd also be
curious about what is reported under the MSSQL:Memory Manager/Buffer Manager
counters.  (I never trust Task Manager to really tell me how much memory is
being used by SQL Server... it usually lumps the higher memory allocated to
SQL Server into PF usage instead.  Performance Monitor does not try to pull
the wool over your eyes like this.)

Also, are there any observable symptoms that make you believe that less RAM
is being used?  I didn't review every single message in the thread but I
don't see anything about actual performance problems.  I'm not always a fan
of "it ain't broke, don't fix it" but if you have no actual symptoms of
insufficient memory, and are chasing this just because you saw the number in
task manager and didn't like it...



On 7/22/08 10:35 PM, in article
9362B897-200B-4E44-8FF4-435928B82E0A@microsoft.com, "Larry Epn"
<larryepn@noemail.noemail> wrote:

> Yes, it appears you understand the situation perfectly.
> There are no other significant applications that run on this server.  It is
> not a domain controller, nor an IIS Server, nor a DNS server, etc.  The
> largest memory allocation in task manager is sqlservr.exe at 1,064Mb, with
> rtvscan.exe a distant second at 52Mb.
> Total Mem Usage (task manager): 1411372K / 5794148K (of course the amount
> above the 4Gb is the pagefile.)
> In this case, if all apps were to share the 2Gb, then I would expect
> sqlserver.exe to at least be using around 1.7Gb calculated as 2Gb (available
> for all Windows applications) - 1411372 (current total usage) + (1067400
> current sql usage) = 1.66Gb for sqlservr.exe.  (Yes, I know I've rounded
> badly and it is all to the advantage of reduced space for sqlservr.exe for
> sake of argument, so no need to clarify that a Gb is > 1000Mb, etc)
> So after all this, there is still no explanation that sqlservr.exe is not
> using all the memory it should.
> Next I will try the 3Gb option and see what happens....even though
> theoretically, I shouldn't need to do this - but I'm willing to try just
> about anything.
> Larry
>

Hi Larry,
Thank you for your response.

I am sorry that after long discussions under this thread, I omitted that your server was Windows 2000 Server. Indeed /3GB switch and PAE are not supported on Windows 2000 server. In this 
case, you can only enable AWE for your SQL Server to allow it to consume more than 2GB memory. I read through the posts again and noticed that Adrew had recommended that you enabled 
AWE, however it seemed that you did not do it but just configure "set working set size". Please try running the following command:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4000
RECONFIGURE
GO
sp_configure 'min server memory', 2000
RECONFIGURE
GO

If this still could not help you resolve the issue, please make a call to Microsoft Customer Support Services (CSS) for deeply troubleshooting this issue. Please feel free to let me know if you have 
any other questions or concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Earlier in the thread, all memory usage shown in task manager has been 
verified by using the PerfMon to look at memory usage in addition to many 
other parameters (paging, disk %, etc, typically used to troubleshoot a SQL 
server)  Because of the consistent correlation between what Task manager is 
showing and PerfMon is showing, I've just been referencing Task Manager since 
an earlier post in this thread because it is easier to pull up.  If total 
usage in the system is 1.4Gb (including all OS, etc), then it is clear that 
SQL can't be using all 2Gb (or even 1.7Gb) RAM.  But thanks for your input.  
The most difficult problems always turn out to be the most satisfying to 
solve.

"Aaron Bertrand [SQL Server MVP]" wrote:

> Everything you're saying about how much memory is being used, seems to be
> evidenced only from Task Manager.  A couple of questions have asked about
> what performance monitor says about total server memory and target server
> memory, but I haven't seen your answer to those questions.  I'd also be
> curious about what is reported under the MSSQL:Memory Manager/Buffer Manager
> counters.  (I never trust Task Manager to really tell me how much memory is
> being used by SQL Server... it usually lumps the higher memory allocated to
> SQL Server into PF usage instead.  Performance Monitor does not try to pull
> the wool over your eyes like this.)
> 
> Also, are there any observable symptoms that make you believe that less RAM
> is being used?  I didn't review every single message in the thread but I
> don't see anything about actual performance problems.  I'm not always a fan
> of "it ain't broke, don't fix it" but if you have no actual symptoms of
> insufficient memory, and are chasing this just because you saw the number in
> task manager and didn't like it...
> 
> 
> 
> On 7/22/08 10:35 PM, in article
> 9362B897-200B-4E44-8FF4-435928B82E0A@microsoft.com, "Larry Epn"
> <larryepn@noemail.noemail> wrote:
> 
> > Yes, it appears you understand the situation perfectly.
> > There are no other significant applications that run on this server.  It is
> > not a domain controller, nor an IIS Server, nor a DNS server, etc.  The
> > largest memory allocation in task manager is sqlservr.exe at 1,064Mb, with
> > rtvscan.exe a distant second at 52Mb.
> > Total Mem Usage (task manager): 1411372K / 5794148K (of course the amount
> > above the 4Gb is the pagefile.)
> > In this case, if all apps were to share the 2Gb, then I would expect
> > sqlserver.exe to at least be using around 1.7Gb calculated as 2Gb (available
> > for all Windows applications) - 1411372 (current total usage) + (1067400
> > current sql usage) = 1.66Gb for sqlservr.exe.  (Yes, I know I've rounded
> > badly and it is all to the advantage of reduced space for sqlservr.exe for
> > sake of argument, so no need to clarify that a Gb is > 1000Mb, etc)
> > So after all this, there is still no explanation that sqlservr.exe is not
> > using all the memory it should.
> > Next I will try the 3Gb option and see what happens....even though
> > theoretically, I shouldn't need to do this - but I'm willing to try just
> > about anything.
> > Larry
> > 
> 
>

I followed your directions.
No difference.
I'll post results from Microsoft tech support.
Larry

We also had similar situtaion where SqlServer 2000 was using the complete 
memeory installed in the server. We have dual core CPU with windows 2003 os.

If your machine is 32 bit your system will never  use more than 2 gb RAM per 
cpu. So server won't allow SQL to use more than 2gb per CPU.  And if it is 64 
bit machine than it will use more than 2 gb of RAM per CPU. 

As we have dual core machine and had installed 8 gb RAM but 4 gb is just 
lying and it is used at all. I may be out of synch but hope this helps to 
reduce your problem.


"Ekrem Önsoy" wrote:

> I'm following this thread and the problem is really interesting.
> 
> However, in your following reply, I wish you'd tell us the "Total Server 
> Memory" and "Target Server Memory" counters' results.
> 
> I know, you gave us lots of proves about the memory consumption. But the 
> results of these counters would definitely prove you had enough RAM but SQL 
> Server can't take advantage of it.
> 
> -- 
> Ekrem Önsoy
> 
> 
> 
> "Larry Epn" <larryepn@noemail.noemail> wrote in message 
> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
> > Yes, it does not add up.
> > I just reconfirmed all of this.
> > Here is boot.ini:
> > --------------------------------------
> > [boot loader]
> > timeout=30
> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
> > [operating systems]
> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server"
> > /fastdetect
> > --------------------------------------
> > Perfmon shows available MBytes = 2148
> > so 2Gb are just sitting there.
> >
> > I made the changes you suggested (set min = 0, max = 2Gb), and restarted 
> > sql
> > service.  I run a halacious join query and watch the memory increase,
> > increase,increase, and then in task manager it gets stuck at its usual
> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, the
> > %Disk Time goes through the roof.  This is what happens ALL the time,
> > regardless of what settings are changed.
> >
> > Your suggestion to move to later versions is valid, but I don't see why I
> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!!
> >
> > Does Microsoft have any ideas?
> >
> > Thanks,
> > Larry 
> 
>

You may want to check out   /3GB, AWE and /PAE (32bit).
http://support.microsoft.com/kb/274750
http://technet.microsoft.com/en-us/library/bb124810.aspx
http://msdn.microsoft.com/en-us/library/ms810461.aspx
http://www.microsoft.com/whdc/system/platform/server/PAE/default.mspx

Setting min = max is "fixed memory" in SQL Server 2000.

The OS takes ~300mb off the top for OS functions.
Assuming you did not limit SQL Server memory...
On a 3GB or 4GB system SQL Server will take ~1.7gb.
If you apply the /3GB switch to the boot.ini on a 4gb system SQL will take 
~2.7gb.

If you have more than 4gb of physical memory the extended memory is 
non-paged pool.



"Tomar"  wrote in message 
news:10DC38B4-106F-4B31-A0AC-7E576A4CFB46@microsoft.com...
> We also had similar situtaion where SqlServer 2000 was using the complete
> memeory installed in the server. We have dual core CPU with windows 2003 
> os.
>
> If your machine is 32 bit your system will never  use more than 2 gb RAM 
> per
> cpu. So server won't allow SQL to use more than 2gb per CPU.  And if it is 
> 64
> bit machine than it will use more than 2 gb of RAM per CPU.
>
> As we have dual core machine and had installed 8 gb RAM but 4 gb is just
> lying and it is used at all. I may be out of synch but hope this helps to
> reduce your problem.
>
>
> "Ekrem Önsoy" wrote:
>
>> I'm following this thread and the problem is really interesting.
>>
>> However, in your following reply, I wish you'd tell us the "Total Server
>> Memory" and "Target Server Memory" counters' results.
>>
>> I know, you gave us lots of proves about the memory consumption. But the
>> results of these counters would definitely prove you had enough RAM but 
>> SQL
>> Server can't take advantage of it.
>>
>> -- 
>> Ekrem Önsoy
>>
>>
>>
>> "Larry Epn" <larryepn@noemail.noemail> wrote in message
>> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
>> > Yes, it does not add up.
>> > I just reconfirmed all of this.
>> > Here is boot.ini:
>> > --------------------------------------
>> > [boot loader]
>> > timeout=30
>> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
>> > [operating systems]
>> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 
>> > Server"
>> > /fastdetect
>> > --------------------------------------
>> > Perfmon shows available MBytes = 2148
>> > so 2Gb are just sitting there.
>> >
>> > I made the changes you suggested (set min = 0, max = 2Gb), and 
>> > restarted
>> > sql
>> > service.  I run a halacious join query and watch the memory increase,
>> > increase,increase, and then in task manager it gets stuck at its usual
>> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, 
>> > the
>> > %Disk Time goes through the roof.  This is what happens ALL the time,
>> > regardless of what settings are changed.
>> >
>> > Your suggestion to move to later versions is valid, but I don't see why 
>> > I
>> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) 
>> > !!!
>> >
>> > Does Microsoft have any ideas?
>> >
>> > Thanks,
>> > Larry
>>
>>

First of all, it's not me who is experiencing this problem. The OP is 
someone else.

Regarding to your scenario, you said your SQL Server 2000 instance is 
installed on a Windows Server 2003 OS.

If your SQL Server 2000 is Enterprise Edition and your Windows Server 2003 
is also Enterprise Edition then you can take advantage of your 8GB of RAM.

How to configure SQL Server to use more than 2 GB of physical memory:
http://support.microsoft.com/kb/274750/en-us

Memory Support and Windows Operating Systems:
http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

-- 
Ekrem Önsoy




"Tomar"  wrote in message 
news:10DC38B4-106F-4B31-A0AC-7E576A4CFB46@microsoft.com...
> We also had similar situtaion where SqlServer 2000 was using the complete
> memeory installed in the server. We have dual core CPU with windows 2003 
> os.
>
> If your machine is 32 bit your system will never  use more than 2 gb RAM 
> per
> cpu. So server won't allow SQL to use more than 2gb per CPU.  And if it is 
> 64
> bit machine than it will use more than 2 gb of RAM per CPU.
>
> As we have dual core machine and had installed 8 gb RAM but 4 gb is just
> lying and it is used at all. I may be out of synch but hope this helps to
> reduce your problem.
>
>
> "Ekrem Önsoy" wrote:
>
>> I'm following this thread and the problem is really interesting.
>>
>> However, in your following reply, I wish you'd tell us the "Total Server
>> Memory" and "Target Server Memory" counters' results.
>>
>> I know, you gave us lots of proves about the memory consumption. But the
>> results of these counters would definitely prove you had enough RAM but 
>> SQL
>> Server can't take advantage of it.
>>
>> -- 
>> Ekrem Önsoy
>>
>>
>>
>> "Larry Epn" <larryepn@noemail.noemail> wrote in message
>> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
>> > Yes, it does not add up.
>> > I just reconfirmed all of this.
>> > Here is boot.ini:
>> > --------------------------------------
>> > [boot loader]
>> > timeout=30
>> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
>> > [operating systems]
>> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 
>> > Server"
>> > /fastdetect
>> > --------------------------------------
>> > Perfmon shows available MBytes = 2148
>> > so 2Gb are just sitting there.
>> >
>> > I made the changes you suggested (set min = 0, max = 2Gb), and 
>> > restarted
>> > sql
>> > service.  I run a halacious join query and watch the memory increase,
>> > increase,increase, and then in task manager it gets stuck at its usual
>> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, 
>> > the
>> > %Disk Time goes through the roof.  This is what happens ALL the time,
>> > regardless of what settings are changed.
>> >
>> > Your suggestion to move to later versions is valid, but I don't see why 
>> > I
>> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) 
>> > !!!
>> >
>> > Does Microsoft have any ideas?
>> >
>> > Thanks,
>> > Larry
>>
>>

It is Sql 200 enterprise, 2003 Enterprise edition. And we have a cluster of 
dual core server(32 bit). Both machines are equipped with 8gb of RAM. We have 
tried all sort of option but still sql is unable to use more 1.5 - 1.7 gb per 
CPU.

The userbase of our application is 1500+ and it is database oriented 
application so we see heavy traffic in the application. As such we don't have 
any issues with anything but I don't know why it does not go more than 2gb.

I will try the URL sent by you. Hopefully it will solve the problem.

--Hemant

"Ekrem Önsoy" wrote:

> First of all, it's not me who is experiencing this problem. The OP is 
> someone else.
> 
> Regarding to your scenario, you said your SQL Server 2000 instance is 
> installed on a Windows Server 2003 OS.
> 
> If your SQL Server 2000 is Enterprise Edition and your Windows Server 2003 
> is also Enterprise Edition then you can take advantage of your 8GB of RAM.
> 
> How to configure SQL Server to use more than 2 GB of physical memory:
> http://support.microsoft.com/kb/274750/en-us
> 
> Memory Support and Windows Operating Systems:
> http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
> 
> -- 
> Ekrem Önsoy
> 
> 
> 
> 
> "Tomar"  wrote in message 
> news:10DC38B4-106F-4B31-A0AC-7E576A4CFB46@microsoft.com...
> > We also had similar situtaion where SqlServer 2000 was using the complete
> > memeory installed in the server. We have dual core CPU with windows 2003 
> > os.
> >
> > If your machine is 32 bit your system will never  use more than 2 gb RAM 
> > per
> > cpu. So server won't allow SQL to use more than 2gb per CPU.  And if it is 
> > 64
> > bit machine than it will use more than 2 gb of RAM per CPU.
> >
> > As we have dual core machine and had installed 8 gb RAM but 4 gb is just
> > lying and it is used at all. I may be out of synch but hope this helps to
> > reduce your problem.
> >
> >
> > "Ekrem Önsoy" wrote:
> >
> >> I'm following this thread and the problem is really interesting.
> >>
> >> However, in your following reply, I wish you'd tell us the "Total Server
> >> Memory" and "Target Server Memory" counters' results.
> >>
> >> I know, you gave us lots of proves about the memory consumption. But the
> >> results of these counters would definitely prove you had enough RAM but 
> >> SQL
> >> Server can't take advantage of it.
> >>
> >> -- 
> >> Ekrem Önsoy
> >>
> >>
> >>
> >> "Larry Epn" <larryepn@noemail.noemail> wrote in message
> >> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
> >> > Yes, it does not add up.
> >> > I just reconfirmed all of this.
> >> > Here is boot.ini:
> >> > --------------------------------------
> >> > [boot loader]
> >> > timeout=30
> >> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
> >> > [operating systems]
> >> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 
> >> > Server"
> >> > /fastdetect
> >> > --------------------------------------
> >> > Perfmon shows available MBytes = 2148
> >> > so 2Gb are just sitting there.
> >> >
> >> > I made the changes you suggested (set min = 0, max = 2Gb), and 
> >> > restarted
> >> > sql
> >> > service.  I run a halacious join query and watch the memory increase,
> >> > increase,increase, and then in task manager it gets stuck at its usual
> >> > 1062Mb, which is consistent with the PerfMon shows.  Then in PerfMon, 
> >> > the
> >> > %Disk Time goes through the roof.  This is what happens ALL the time,
> >> > regardless of what settings are changed.
> >> >
> >> > Your suggestion to move to later versions is valid, but I don't see why 
> >> > I
> >> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) 
> >> > !!!
> >> >
> >> > Does Microsoft have any ideas?
> >> >
> >> > Thanks,
> >> > Larry
> >>
> >> 
>

Yes if you really do have Enterprise editions of both the OS and SQL Server 
you can use AWE and PAE to access more than 2GB as per the documentation 
listed.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Tomar"  wrote in message 
news:1580B829-B63D-4D00-90DE-1EB87952713E@microsoft.com...
> It is Sql 200 enterprise, 2003 Enterprise edition. And we have a cluster 
> of
> dual core server(32 bit). Both machines are equipped with 8gb of RAM. We 
> have
> tried all sort of option but still sql is unable to use more 1.5 - 1.7 gb 
> per
> CPU.
>
> The userbase of our application is 1500+ and it is database oriented
> application so we see heavy traffic in the application. As such we don't 
> have
> any issues with anything but I don't know why it does not go more than 
> 2gb.
>
> I will try the URL sent by you. Hopefully it will solve the problem.
>
> --Hemant
>
> "Ekrem Önsoy" wrote:
>
>> First of all, it's not me who is experiencing this problem. The OP is
>> someone else.
>>
>> Regarding to your scenario, you said your SQL Server 2000 instance is
>> installed on a Windows Server 2003 OS.
>>
>> If your SQL Server 2000 is Enterprise Edition and your Windows Server 
>> 2003
>> is also Enterprise Edition then you can take advantage of your 8GB of 
>> RAM.
>>
>> How to configure SQL Server to use more than 2 GB of physical memory:
>> http://support.microsoft.com/kb/274750/en-us
>>
>> Memory Support and Windows Operating Systems:
>> http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
>>
>> -- 
>> Ekrem Önsoy
>>
>>
>>
>>
>> "Tomar"  wrote in message
>> news:10DC38B4-106F-4B31-A0AC-7E576A4CFB46@microsoft.com...
>> > We also had similar situtaion where SqlServer 2000 was using the 
>> > complete
>> > memeory installed in the server. We have dual core CPU with windows 
>> > 2003
>> > os.
>> >
>> > If your machine is 32 bit your system will never  use more than 2 gb 
>> > RAM
>> > per
>> > cpu. So server won't allow SQL to use more than 2gb per CPU.  And if it 
>> > is
>> > 64
>> > bit machine than it will use more than 2 gb of RAM per CPU.
>> >
>> > As we have dual core machine and had installed 8 gb RAM but 4 gb is 
>> > just
>> > lying and it is used at all. I may be out of synch but hope this helps 
>> > to
>> > reduce your problem.
>> >
>> >
>> > "Ekrem Önsoy" wrote:
>> >
>> >> I'm following this thread and the problem is really interesting.
>> >>
>> >> However, in your following reply, I wish you'd tell us the "Total 
>> >> Server
>> >> Memory" and "Target Server Memory" counters' results.
>> >>
>> >> I know, you gave us lots of proves about the memory consumption. But 
>> >> the
>> >> results of these counters would definitely prove you had enough RAM 
>> >> but
>> >> SQL
>> >> Server can't take advantage of it.
>> >>
>> >> -- 
>> >> Ekrem Önsoy
>> >>
>> >>
>> >>
>> >> "Larry Epn" <larryepn@noemail.noemail> wrote in message
>> >> news:2BD8FB94-7940-4707-A0FC-02CB9279BCCE@microsoft.com...
>> >> > Yes, it does not add up.
>> >> > I just reconfirmed all of this.
>> >> > Here is boot.ini:
>> >> > --------------------------------------
>> >> > [boot loader]
>> >> > timeout=30
>> >> > default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
>> >> > [operating systems]
>> >> > multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000
>> >> > Server"
>> >> > /fastdetect
>> >> > --------------------------------------
>> >> > Perfmon shows available MBytes = 2148
>> >> > so 2Gb are just sitting there.
>> >> >
>> >> > I made the changes you suggested (set min = 0, max = 2Gb), and
>> >> > restarted
>> >> > sql
>> >> > service.  I run a halacious join query and watch the memory 
>> >> > increase,
>> >> > increase,increase, and then in task manager it gets stuck at its 
>> >> > usual
>> >> > 1062Mb, which is consistent with the PerfMon shows.  Then in 
>> >> > PerfMon,
>> >> > the
>> >> > %Disk Time goes through the roof.  This is what happens ALL the 
>> >> > time,
>> >> > regardless of what settings are changed.
>> >> >
>> >> > Your suggestion to move to later versions is valid, but I don't see 
>> >> > why
>> >> > I
>> >> > can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?)
>> >> > !!!
>> >> >
>> >> > Does Microsoft have any ideas?
>> >> >
>> >> > Thanks,
>> >> > Larry
>> >>
>> >>
>>

After all the postings and several days of going back-and-forth with MS with 
a special SQL error monitor, the problem turns out to be a simple thing.  

First, let me reiterate the OS/SQL versions:
Win2000 Server Standard
SQL 2000 Standard
4GB RAM.
No /3GB switch (isn't supported by Win2000 STD anyway)
No AWE in SQL (wouldn't make a difference anyway due to OS and SQL version 
limitations)

The whole problem was the setting for Max Worker Threads.  The default is 
255, and someone (I don't think I did it...) set it to 32767.... Here is more 
from the Microsoft Tech:
-------------------------------------------------------------------------------------------
During startup SQL server reserves memory for MTL(MeM To leave) which is 
default and calculated this way.
-g is 256 + (number or worker threads default 255 ) which is equal to 384 MB 
and leaving behind 1.7 GB of address space for B pool.
 
So from previous logs it indicated that the Max worker threads is set to 
maximum which is 32767 and each worker thread needs 1/2 MB of memory.
if we do the math which would be 256+(32767x1/2mb) which is closer to 16 GB 
which is not possible.

So I think what was happening is that was calculating something where the 
MTL is reserving more space may be like a 1 GB and SQL was not able to 
address more than 1 GB.

so I requested you to change back the min to 0 and Max to close to 1.7 GB .

-----------------------------------------------------------------------------------

And I did change it back and my SQL is now using 1.7GB according to PerfMon, 
etc.
Simple problem, simple solution.  Too bad I had to use a Microsoft support 
call to get to the bottom of this.  But, as you can see, I wasn't alone in my 
ignorance of this setting :)
Larry

Ahh, I should have looked at that. I was sure it would have been the -g 
switch but when you said it was not set I spaced and didn't catch the 
setting was changed in sp_configure. I knew each worker thread uses .5MB in 
2000 but I have to say I have never run across a server in which this was 
set so absurdly high so it didn't come to mind. Glad to see you got it and 
sorry you had to go to PSS. I used to had a spreadsheet that I plopped in 
the results of sp_configure and it would highlight any changed settings. I 
rarely do anything with 2000 anymore and seemed to have misplaced that. I 
have been asking MS for quite some time to add an extra column to the result 
set (in 2005 you use sys.configurations) which shows anything changed from 
the default but no luck so far.

-- 
Andrew J. Kelly    SQL MVP
Solid Quality Mentors


"Larry Epn" <larryepn@noemail.noemail> wrote in message 
news:B9D34902-A92B-4AC1-A2D7-39CD10B02EFD@microsoft.com...
> After all the postings and several days of going back-and-forth with MS 
> with
> a special SQL error monitor, the problem turns out to be a simple thing.
>
> First, let me reiterate the OS/SQL versions:
> Win2000 Server Standard
> SQL 2000 Standard
> 4GB RAM.
> No /3GB switch (isn't supported by Win2000 STD anyway)
> No AWE in SQL (wouldn't make a difference anyway due to OS and SQL version
> limitations)
>
> The whole problem was the setting for Max Worker Threads.  The default is
> 255, and someone (I don't think I did it...) set it to 32767.... Here is 
> more
> from the Microsoft Tech:
> -------------------------------------------------------------------------------------------
> During startup SQL server reserves memory for MTL(MeM To leave) which is
> default and calculated this way.
> -g is 256 + (number or worker threads default 255 ) which is equal to 384 
> MB
> and leaving behind 1.7 GB of address space for B pool.
>
> So from previous logs it indicated that the Max worker threads is set to
> maximum which is 32767 and each worker thread needs 1/2 MB of memory.
> if we do the math which would be 256+(32767x1/2mb) which is closer to 16 
> GB
> which is not possible.
>
> So I think what was happening is that was calculating something where the
> MTL is reserving more space may be like a 1 GB and SQL was not able to
> address more than 1 GB.
>
> so I requested you to change back the min to 0 and Max to close to 1.7 GB 
> .
>
> -----------------------------------------------------------------------------------
>
> And I did change it back and my SQL is now using 1.7GB according to 
> PerfMon,
> etc.
> Simple problem, simple solution.  Too bad I had to use a Microsoft support
> call to get to the bottom of this.  But, as you can see, I wasn't alone in 
> my
> ignorance of this setting :)
> Larry
>

Well, at least I got the answer, and I wanted to make sure I posted it here 
for the next poor soul who gets stuck on that one....
Thanks for everyone who tried to help.
Larry