Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong?
Tnx in advance!
/Magnus
Hi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong?
> Tnx in advance!
> /Magnus
>
>
|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong?
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/defaul...b;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus ?sterberg" wrote:

> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage i
n
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doin
g
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus sterberg"
<magnus.osterberg@.abo.fi> wrote:

>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory usage\Performance problem

I am experiencing the following problem;

I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;

select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC

There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong??

Tnx in advance!

/MagnusMagnus sterberg (magnus.osterberg@.abo.fi) writes:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
> in Task manager. It is ca 20 mb, everything is OK. Then I run this
> query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable
> contains ca 916.000 rows. I thought this query would still execute in
> only a few seconds, but it takes minutes. And the worst thing is that
> sqlserver.exe's memory usage grows to about 300-400 mb when the query
> runs. What am I doing wrong??

So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.

SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.

SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.

Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.

The query itself would benefit enormously by a non-clustered index on
postoffice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C183A0F9A0DYazorman@.127.0.0.1...
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> I am experiencing the following problem;
>>
>> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage
>> in Task manager. It is ca 20 mb, everything is OK. Then I run this
>> query;
>>
>> select postoffice, count(*) as counter from tblTable
>> where postoffice is not null
>> group by postoffice
>> order by counter DESC
>>
>> There's no index or constraint on field "postoffice" and tblTable
>> contains ca 916.000 rows. I thought this query would still execute in
>> only a few seconds, but it takes minutes. And the worst thing is that
>> sqlserver.exe's memory usage grows to about 300-400 mb when the query
>> runs. What am I doing wrong??
> So what is the average row size of this table? Say that is 300 bytes,
> then that is 300 MB of data to read. That is not very likely to be done
> instantly.
> SQL Server's memory consumption will increase, as it will read the entire
> table into cache, and the table will stay in the cache as long as no
> other data competes about the space. This means that if you resubmit the
> query, the response time will be significantly shorter.
> SQL Server is designed to grab as much memory it can, as the more data
> in can have in cache, the better the response times. If there are other
> applications competing for memory on the machine, SQL Server will yield,
> but in this case it may be better to configure how much memory you want
> SQL Server to use.
> Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
> to see what shape the table is in. To defragment it, you would have to
> create a clustered index on the table, and then drop that index.
> The query itself would benefit enormously by a non-clustered index on
> postoffice.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks for a well-written answer!

I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds.
One index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.

Isn't there any other ways of speeding up selects? Well, I guess not...

/Magnus|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I guess my expectations on selecting based on a non-indexed column were
> somewhat high. When I added an index, my query executes in seconds. One
> index solved this problem, but my table contains 30-40 similar columns,
> and I don't think indexing every column is a good idea.
> Isn't there any other ways of speeding up selects? Well, I guess not...

Well, once data is in cache it will be faster. Or at least less slow.

But if you need to do this on every column, it sounds to me like one
of those things Analysis Services is good for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Magnus sterberg wrote:
> I guess my expectations on selecting based on a non-indexed column
> were somewhat high. When I added an index, my query executes in
> seconds.
> One index solved this problem, but my table contains 30-40 similar
> columns, and I don't think indexing every column is a good idea.

Two things come to mind:

1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.

2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.

> Isn't there any other ways of speeding up selects? Well, I guess
> not...

Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, ... It all
depends. :-)

Kind regards

robert|||Thank you Robert, these all are things that I'll keep in mind.

One more thing though; why is the sqlservr.exe process consuming 395 mb RAM
when the entire server is idle?
My "cron job" is inserting a few 1000 rows of data at night, but now it is
late morning here in Finland, and the entire machine is more or less idle.
On my other servers, the RAM consumption on idle is about 20-30 mb only.
Anyone got any ideas?

/Magnus

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3ngkc3F1dnm7U1@.individual.net...
> Magnus sterberg wrote:
>> I guess my expectations on selecting based on a non-indexed column
>> were somewhat high. When I added an index, my query executes in
>> seconds.
>> One index solved this problem, but my table contains 30-40 similar
>> columns, and I don't think indexing every column is a good idea.
> Two things come to mind:
> 1. rethink your table design. If these columns are so similar you might
> be better off with a second table which is joined. But that of course
> depends on your data - just an idea.
> 2. Create indexes (possibly composite indexes) for most used queries. You
> might even get away with a single (or few) composite index if your queries
> only use a leading portion of this index's fields.
>> Isn't there any other ways of speeding up selects? Well, I guess
>> not...
> Well, there are tons of other options, like having data files on several
> disks, putting tx log on a separate disk, adding memory, ... It all
> depends. :-)
> Kind regards
> robert|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> One more thing though; why is the sqlservr.exe process consuming 395 mb
> RAM when the entire server is idle?

I believe that was in my first reply.

SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered without
reading from disk.

Thus, this is perfectly normal behaviour.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Magnus sterberg (magnus.osterberg@.abo.fi) writes:
>> One more thing though; why is the sqlservr.exe process consuming 395
>> mb RAM when the entire server is idle?
> I believe that was in my first reply.
> SQL Server is designed to get as much memory as it can, and only yield
> if an another application needs it. This is because it keeps data in
> cache so that future requests for the same data can be answered
> without reading from disk.
> Thus, this is perfectly normal behaviour.

Adding to that max memory consumption is easily configurable so if 400MB
is too much for you then simply turn that down.

Kind regards

robert

Memory usage\Performance problem

I am experiencing the following problem;
I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
Task manager. It is ca 20 mb, everything is OK. Then I run this query;
select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC
There's no index or constraint on field "postoffice" and tblTable contains
ca 916.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs. What am I doing
wrong'
Tnx in advance!
/MagnusHi
916'000 rows and no index? It is best to have an index for larger tables
else it has to load each row and look at it.
Have a look at http://www.sql-server-performance.com/ for some ideas.
INF: SQL Server Memory Usage:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Magnus Ã?sterberg" wrote:
> I am experiencing the following problem;
> I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
> Task manager. It is ca 20 mb, everything is OK. Then I run this query;
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 916.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs. What am I doing
> wrong'
> Tnx in advance!
> /Magnus
>
>|||On Mon, 29 Aug 2005 12:31:12 +0300, "Magnus Österberg"
<magnus.osterberg@.abo.fi> wrote:
>I am experiencing the following problem;
>I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in
>Task manager. It is ca 20 mb, everything is OK. Then I run this query;
>select postoffice, count(*) as counter from tblTable
>where postoffice is not null
>group by postoffice
>order by counter DESC
>There's no index or constraint on field "postoffice" and tblTable contains
>ca 916.000 rows. I thought this query would still execute in only a few
>seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
>memory usage grows to about 300-400 mb when the query runs. What am I doing
>wrong'
That's the entire query? Nobody else on the machine? Machine has how
much ram, 512mb? 1gb? More? Hmm. I'm going to guess your machine
is only 512mb, that that table totals more than 512mb, on a cold run
of course none of the data will be cached in RAM, and yes, SQLServer
can be quite slow when it has to do physical IO, that is, far slower
than when the data is in cache. SQLServer is designed to grab all
available memory if it has even the least interest in doing so, so the
growth to 400mb doesn't surprise me at all.
You may also be getting an "autostats" on the postoffice column which
will take some time, you can turn on the profiler, make sure you
capture the object/autostats event, and get some idea.
How long does it take if you immediately rerun the same query? Lot
faster?
J.
J.

Memory optimization

Hi
We are running full text search with 4GB RAM on SQL server 2000. Ihave the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
I read it in the manual that the optimal configuration for full text search
is
1. The virtual memory size to at least 3 times the physical memory installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5 times
the physical memory (half the virtual memory size setting).
Questions:
1. How do I set max sql server memory to 1.5 times physical memory? Max
memory that SQL server allows us to set is 4095 MB? Is this only possible
with AWE?
2. My observation when I made the VM 3X the physical, was that the full text
queries were slower.
What are the right settings and what am I doing wrong?
Matt
Hi
Can any body throw some ideas on this one?
MM
"ISTS" wrote:

> Hi
> We are running full text search with 4GB RAM on SQL server 2000. Ihave the
> /3G option on in boot.ini. The VM setting is set at 2048 to 4095.
> I read it in the manual that the optimal configuration for full text search
> is
> 1. The virtual memory size to at least 3 times the physical memory installed
> in the computer.
> 2. The SQL Server max server memory server configuration option to 1.5 times
> the physical memory (half the virtual memory size setting).
> Questions:
> 1. How do I set max sql server memory to 1.5 times physical memory? Max
> memory that SQL server allows us to set is 4095 MB? Is this only possible
> with AWE?
> 2. My observation when I made the VM 3X the physical, was that the full text
> queries were slower.
> What are the right settings and what am I doing wrong?
> --
> Matt

Memory Management: SQL Enterprise, 2000 Adv. Srv.

Questions
A) If I have the /3GB /PAE variables in the Win 2k Server boot.ini and only have 2 gig of memory will this cause issues with memory readings I am seeing in Performance Monitor? (I know it doesn't make sense to have but happened out of circumstance
B) From what I am reading if "SQLServer: Memory Manager -- Target Server Memory (KB)" provides the same value as "SQLServer: Memory Manager -- Total Server Memory (KB)" then SQL is content and has as much memory as it needs. What I am seeing on my SQL server is that the two numbers match but are using all the memory on the server which is 2 GB. I assume that this means increase the amount of memory on the box. Am I correct in this assumption
C) Again from reading my understanding is that when using AWE (under 16 GB), SQL at startup will grab the specified chunk of RAM you have alotted to it. I was wondering if there are any counters within Performance Monitor that will tell me exactly how much of that RAM (AWE) SQL Server is using
Thanks...Anyone?

Memory management

Hi,
I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6GB
ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
ram. But the sql server memory management tab shows false values (16MB and
0 values), and the sliders are disabled. Why is it? How can I check memory
usage for the sql server?
Thank you,
Chris
Message posted via http://www.sqlmonster.com
Hi
Run sp_configure 'max server memory' to see what is configured.
If you use SQL Server Standard Edition, all what it can use is 2GB as that
is an Edition limitation.
SQL Server Enterprise edition, IIRC is 8GB.
Regards
Mike
"Mary Poppins via SQLMonster.com" wrote:

> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6GB
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com
>
|||Do you have AWE enabled? If so then sql server does not dynamically manage
memory. It will grab all of what if there unless you set the max memory
setting to something less than the total available. In your case I recommend
around 5GB. You can use the Sql Memory counters in perfmon to see how much
it is actually using. Total memory and Target memory should be the same and
what ever you set your max memory setting to. Make sure you don't have the
Use Fixed Amount checked as that will disable the sliders.
Andrew J. Kelly SQL MVP
"Mary Poppins via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6ea9fff4366143f8b44320e5b0092e94@.SQLMonster.c om...
> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with
> 6GB
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com
|||AWE not enabled . (The extended memory option in SQL Server 7.0 is not
available on Windows 2000 Advanced Server). Sp_configure shows that the
min, max and the run server memories are 2147483647. In the server
properties memory tab the "Use fixed memory size" are checked, and I can
not change it, and it shows only min 16MB and max 0MB. However the in the
performance monitor the Target server memory shows 2082896KB, and the Total
server memory 144150 KB.
Can I ignore the memory panel, or could you advise something to correct the
wrong values?
Thank you,
Chris
Message posted via http://www.sqlmonster.com
|||Ignore the memory panel. The important thing is the sp_configure setting. The memory dialog is
probably just some kind of UI bug.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary Poppins via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:0b5fc3940c614ef79498116929c69874@.SQLMonster.c om...
> AWE not enabled . (The extended memory option in SQL Server 7.0 is not
> available on Windows 2000 Advanced Server). Sp_configure shows that the
> min, max and the run server memories are 2147483647. In the server
> properties memory tab the "Use fixed memory size" are checked, and I can
> not change it, and it shows only min 16MB and max 0MB. However the in the
> performance monitor the Target server memory shows 2082896KB, and the Total
> server memory 144150 KB.
> Can I ignore the memory panel, or could you advise something to correct the
> wrong values?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com
|||OK. Thank you.
Bye
Message posted via http://www.sqlmonster.com

Memory management

Hi,
I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6GB
ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
ram. But the sql server memory management tab shows false values (16MB and
0 values), and the sliders are disabled. Why is it? How can I check memory
usage for the sql server?
Thank you,
Chris
Message posted via http://www.droptable.comHi
Run sp_configure 'max server memory' to see what is configured.
If you use SQL Server Standard Edition, all what it can use is 2GB as that
is an Edition limitation.
SQL Server Enterprise edition, IIRC is 8GB.
Regards
Mike
"Mary Poppins via droptable.com" wrote:

> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6G
B
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.droptable.com
>|||Do you have AWE enabled? If so then sql server does not dynamically manage
memory. It will grab all of what if there unless you set the max memory
setting to something less than the total available. In your case I recommend
around 5GB. You can use the Sql Memory counters in perfmon to see how much
it is actually using. Total memory and Target memory should be the same and
what ever you set your max memory setting to. Make sure you don't have the
Use Fixed Amount checked as that will disable the sliders.
Andrew J. Kelly SQL MVP
"Mary Poppins via droptable.com" <forum@.droptable.com> wrote in message
news:6ea9fff4366143f8b44320e5b0092e94@.SQ
droptable.com...
> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with
> 6GB
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.droptable.com|||AWE not enabled . (The extended memory option in SQL Server 7.0 is not
available on Windows 2000 Advanced Server). Sp_configure shows that the
min, max and the run server memories are 2147483647. In the server
properties memory tab the "Use fixed memory size" are checked, and I can
not change it, and it shows only min 16MB and max 0MB. However the in the
performance monitor the Target server memory shows 2082896KB, and the Total
server memory 144150 KB.
Can I ignore the memory panel, or could you advise something to correct the
wrong values?
Thank you,
Chris
Message posted via http://www.droptable.com|||Ignore the memory panel. The important thing is the sp_configure setting. Th
e memory dialog is
probably just some kind of UI bug.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary Poppins via droptable.com" <forum@.droptable.com> wrote in message
news:0b5fc3940c614ef79498116929c69874@.SQ
droptable.com...
> AWE not enabled . (The extended memory option in SQL Server 7.0 is not
> available on Windows 2000 Advanced Server). Sp_configure shows that the
> min, max and the run server memories are 2147483647. In the server
> properties memory tab the "Use fixed memory size" are checked, and I can
> not change it, and it shows only min 16MB and max 0MB. However the in the
> performance monitor the Target server memory shows 2082896KB, and the Tota
l
> server memory 144150 KB.
> Can I ignore the memory panel, or could you advise something to correct th
e
> wrong values?
> Thank you,
> Chris
> --
> Message posted via http://www.droptable.com|||OK. Thank you.
Bye
Message posted via http://www.droptable.com

Memory management

Hi,
I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6GB
ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
ram. But the sql server memory management tab shows false values (16MB and
0 values), and the sliders are disabled. Why is it? How can I check memory
usage for the sql server?
Thank you,
Chris
--
Message posted via http://www.sqlmonster.comHi
Run sp_configure 'max server memory' to see what is configured.
If you use SQL Server Standard Edition, all what it can use is 2GB as that
is an Edition limitation.
SQL Server Enterprise edition, IIRC is 8GB.
Regards
Mike
"Mary Poppins via SQLMonster.com" wrote:
> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with 6GB
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com
>|||Do you have AWE enabled? If so then sql server does not dynamically manage
memory. It will grab all of what if there unless you set the max memory
setting to something less than the total available. In your case I recommend
around 5GB. You can use the Sql Memory counters in perfmon to see how much
it is actually using. Total memory and Target memory should be the same and
what ever you set your max memory setting to. Make sure you don't have the
Use Fixed Amount checked as that will disable the sliders.
--
Andrew J. Kelly SQL MVP
"Mary Poppins via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6ea9fff4366143f8b44320e5b0092e94@.SQLMonster.com...
> Hi,
> I have a MS Sql server 7.0 running on Windows 2000 advanced server with
> 6GB
> ram. The boot.ini contains the /3gb /pae options. The OS sees all of the
> ram. But the sql server memory management tab shows false values (16MB and
> 0 values), and the sliders are disabled. Why is it? How can I check memory
> usage for the sql server?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com|||AWE not enabled . (The extended memory option in SQL Server 7.0 is not
available on Windows 2000 Advanced Server). Sp_configure shows that the
min, max and the run server memories are 2147483647. In the server
properties memory tab the "Use fixed memory size" are checked, and I can
not change it, and it shows only min 16MB and max 0MB. However the in the
performance monitor the Target server memory shows 2082896KB, and the Total
server memory 144150 KB.
Can I ignore the memory panel, or could you advise something to correct the
wrong values?
Thank you,
Chris
--
Message posted via http://www.sqlmonster.com|||Ignore the memory panel. The important thing is the sp_configure setting. The memory dialog is
probably just some kind of UI bug.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary Poppins via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:0b5fc3940c614ef79498116929c69874@.SQLMonster.com...
> AWE not enabled . (The extended memory option in SQL Server 7.0 is not
> available on Windows 2000 Advanced Server). Sp_configure shows that the
> min, max and the run server memories are 2147483647. In the server
> properties memory tab the "Use fixed memory size" are checked, and I can
> not change it, and it shows only min 16MB and max 0MB. However the in the
> performance monitor the Target server memory shows 2082896KB, and the Total
> server memory 144150 KB.
> Can I ignore the memory panel, or could you advise something to correct the
> wrong values?
> Thank you,
> Chris
> --
> Message posted via http://www.sqlmonster.com|||OK. Thank you.
Bye
--
Message posted via http://www.sqlmonster.com

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?

> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.
Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>
|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?

> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>