Merge error 2147200925 - why does reboot the server machine could solve problem.

Have a look at this article:
http://support.microsoft.com/?id=814916
There is a link to the hotfix details there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks,
I did take a look at it and have download the hot fix.
But could you explain why restarting the server can solve the problem
temporarily?
Pikctsach
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||A total guess here, but the problem occurs 'in certain circimstances' and
relates to teh production of temporary tables, so the initial population of
the tempdb database might play a part. Restarting the server will recreate
tempdb from the model database, and so reset the starting point - makes
sense locically but I have no way of determining if this is the case.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks!
Have you ever thought of in whichscenarios, SQL Server creates the
temporary table name with a dash sign in the table name? (For Merge
Replication)
Why are temporary tables needed in Merge Replication?
Pikctsach.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

memory used by sqlservr

Hi,
My sql server is running on a Windows 2000 server with 2G physical memory
machine and I'm looking at the private-bytes performance counter for
sqlservr process and it's taking 1.8G. Is that something I should be
concerned about? I assume that sqlserver can be smart in taking most
possible available memory - so that would explain it. But how do I know when
I should be concerned - in the case 1.8 is the actual minimum it needs to
run efficiently (constantly swapping memory with page file is not efficient
of course).
In another word, how do I know when to start investing in more hardware for
my sql server?
thanks!Have a look at
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zen" <zen@.nononospam.com> wrote in message news:uolOKk9YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Hi,
> My sql server is running on a Windows 2000 server with 2G physical memory
> machine and I'm looking at the private-bytes performance counter for
> sqlservr process and it's taking 1.8G. Is that something I should be
> concerned about? I assume that sqlserver can be smart in taking most
> possible available memory - so that would explain it. But how do I know when
> I should be concerned - in the case 1.8 is the actual minimum it needs to
> run efficiently (constantly swapping memory with page file is not efficient
> of course).
> In another word, how do I know when to start investing in more hardware for
> my sql server?
> thanks!
>

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 Usage per DB-Instance

Hi,
I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
My question is, how much memory can I allocate (reserve) per instance?
Due to limitations in 2000 Standard Server i cannot use parameters like
/3GB, AWE or other in BOOT.INI
Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM, or
both instances can only use TOGHETHER 2GB of RAM?
Thanks,
NickDo you have two SQL Server Instances or simply two Databases? If it is two
instances each one can use up to 2GB each if you had that much memory
available on the server minus what the OS needs. By default each instance
will dynamically manage the available memory with the OS and each instance
as needed. But you can specify a MAX Memory setting for each instance as
well. If you are talking about two databases in a single SQL Server
instance they all share the same memory.
--
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> Hi,
> I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> My question is, how much memory can I allocate (reserve) per instance?
> Due to limitations in 2000 Standard Server i cannot use parameters like
> /3GB, AWE or other in BOOT.INI
> Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> or
> both instances can only use TOGHETHER 2GB of RAM?
> Thanks,
> Nick
>|||Yes, I have two instances not only simply two databases. I thought that
normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb for
kernel-mode.
Thanks,
Nick
"Andrew J. Kelly" wrote:
> Do you have two SQL Server Instances or simply two Databases? If it is two
> instances each one can use up to 2GB each if you had that much memory
> available on the server minus what the OS needs. By default each instance
> will dynamically manage the available memory with the OS and each instance
> as needed. But you can specify a MAX Memory setting for each instance as
> well. If you are talking about two databases in a single SQL Server
> instance they all share the same memory.
> --
> Andrew J. Kelly SQL MVP
>
> "NickD" <NickD@.discussions.microsoft.com> wrote in message
> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
> > Hi,
> >
> > I'm using SQL 2000 Standard and Windows 2000 Server Standard on one server
> > machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
> > My question is, how much memory can I allocate (reserve) per instance?
> >
> > Due to limitations in 2000 Standard Server i cannot use parameters like
> > /3GB, AWE or other in BOOT.INI
> >
> > Can I use per example for instance1 2GB-RAM, and for instance2 512MB-RAM,
> > or
> > both instances can only use TOGHETHER 2GB of RAM?
> >
> > Thanks,
> >
> > Nick
> >
>
>|||Every application such as SQL Server, Notepad<g> etc. has the ability to use
2GB each and the OS can use 2GB as well. Again assuming there is enough to
go around.
--
Andrew J. Kelly SQL MVP
"NickD" <NickD@.discussions.microsoft.com> wrote in message
news:47C7774C-43BC-4E2E-8705-44BB6324F107@.microsoft.com...
> Yes, I have two instances not only simply two databases. I thought that
> normally the OS uses 2GB for user progs (like SQL 2000) and the other 2Gb
> for
> kernel-mode.
> Thanks,
> Nick
> "Andrew J. Kelly" wrote:
>> Do you have two SQL Server Instances or simply two Databases? If it is
>> two
>> instances each one can use up to 2GB each if you had that much memory
>> available on the server minus what the OS needs. By default each instance
>> will dynamically manage the available memory with the OS and each
>> instance
>> as needed. But you can specify a MAX Memory setting for each instance as
>> well. If you are talking about two databases in a single SQL Server
>> instance they all share the same memory.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "NickD" <NickD@.discussions.microsoft.com> wrote in message
>> news:0BBD1416-C163-44C5-851E-21CF7EFC8C74@.microsoft.com...
>> > Hi,
>> >
>> > I'm using SQL 2000 Standard and Windows 2000 Server Standard on one
>> > server
>> > machine. Ther real RAM is 3,25GB. I'm using TWO DB-instances.
>> > My question is, how much memory can I allocate (reserve) per instance?
>> >
>> > Due to limitations in 2000 Standard Server i cannot use parameters like
>> > /3GB, AWE or other in BOOT.INI
>> >
>> > Can I use per example for instance1 2GB-RAM, and for instance2
>> > 512MB-RAM,
>> > or
>> > both instances can only use TOGHETHER 2GB of RAM?
>> >
>> > Thanks,
>> >
>> > Nick
>> >
>>

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)Hi
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So the rest 1 GB will be used for all the other application including OS
SQL Server Enterprise will support 4 GB RAM
Thank
Har
MCDB
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, s
we upgraded RAM to 3GB. Since then I've noticed it's still using almos
exactly 2GB. I'm guessing this means there is some limitation on how muc
memory SQL Server will make use of. Is there some switch that allows SQL t
make use of the additional memory
SQL Server 2000 on Windows 2000 (both standard editions

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)
Hi,
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So the rest 1 GB will be used for all the other application including OS.
SQL Server Enterprise will support 4 GB RAM.
Thanks
Hari
MCDBA
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)Hi,
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So th
e rest 1 GB will be used for all the other application including OS.
SQL Server Enterprise will support 4 GB RAM.
Thanks
Hari
MCDBA
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)

memory usage jumps

I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM. This
is a small development machine so there's very little stress on it. SQL
server is pretty much the only app running as I turned off IIS and all web
server stuff.
When I start the server, the RAM usage is around 150Mb. After a little while
(seems random but within an hour) the RAM usage jumps to 600Mb. This is not
a gradual increase to 600Mb but rather a jump. If I restart the mssqlserver
service, then the RAM usage goes back down to 150Mb and the cycle starts
again.
There are only 6 DBs defined, I turned off all the jobs and there is no such
things as DTS etc... the config is very simple
Any idea what could be causing that?
ThanksIt doesn't take much to eat up a few 100 MB of ram. A large regular join or
Cartesian join can easily do this. I would suggest running profiler to see
what is happening when this occurs.
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
This
> is a small development machine so there's very little stress on it. SQL
> server is pretty much the only app running as I turned off IIS and all web
> server stuff.
> When I start the server, the RAM usage is around 150Mb. After a little
while
> (seems random but within an hour) the RAM usage jumps to 600Mb. This is
not
> a gradual increase to 600Mb but rather a jump. If I restart the
mssqlserver
> service, then the RAM usage goes back down to 150Mb and the cycle starts
> again.
> There are only 6 DBs defined, I turned off all the jobs and there is no
such
> things as DTS etc... the config is very simple
> Any idea what could be causing that?
> Thanks
>|||Try to avoid pulling data from the disk into the buffer pool. To do this,
avoid all DML, like Select's or Updates.
You should then see memory remain pretty constant.
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
This
> is a small development machine so there's very little stress on it. SQL
> server is pretty much the only app running as I turned off IIS and all web
> server stuff.
> When I start the server, the RAM usage is around 150Mb. After a little
while
> (seems random but within an hour) the RAM usage jumps to 600Mb. This is
not
> a gradual increase to 600Mb but rather a jump. If I restart the
mssqlserver
> service, then the RAM usage goes back down to 150Mb and the cycle starts
> again.
> There are only 6 DBs defined, I turned off all the jobs and there is no
such
> things as DTS etc... the config is very simple
> Any idea what could be causing that?
> Thanks
>|||what do you mean by DML?
do you mean replacing SELECT and UPDATE by stored procs?
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:us3SVPWiDHA.2960@.TK2MSFTNGP11.phx.gbl...
> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
> You should then see memory remain pretty constant.
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
> This
> > is a small development machine so there's very little stress on it. SQL
> > server is pretty much the only app running as I turned off IIS and all
web
> > server stuff.
> >
> > When I start the server, the RAM usage is around 150Mb. After a little
> while
> > (seems random but within an hour) the RAM usage jumps to 600Mb. This is
> not
> > a gradual increase to 600Mb but rather a jump. If I restart the
> mssqlserver
> > service, then the RAM usage goes back down to 150Mb and the cycle starts
> > again.
> >
> > There are only 6 DBs defined, I turned off all the jobs and there is no
> such
> > things as DTS etc... the config is very simple
> >
> > Any idea what could be causing that?
> >
> > Thanks
> >
> >
>|||> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
Eh, doesn't that make SQL Server a pretty useless product?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:us3SVPWiDHA.2960@.TK2MSFTNGP11.phx.gbl...
> Try to avoid pulling data from the disk into the buffer pool. To do this,
> avoid all DML, like Select's or Updates.
> You should then see memory remain pretty constant.
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:ukTtPGTiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > I have an instance of SQL server installed on a P3 1GHz with 512Mb RAM.
> This
> > is a small development machine so there's very little stress on it. SQL
> > server is pretty much the only app running as I turned off IIS and all web
> > server stuff.
> >
> > When I start the server, the RAM usage is around 150Mb. After a little
> while
> > (seems random but within an hour) the RAM usage jumps to 600Mb. This is
> not
> > a gradual increase to 600Mb but rather a jump. If I restart the
> mssqlserver
> > service, then the RAM usage goes back down to 150Mb and the cycle starts
> > again.
> >
> > There are only 6 DBs defined, I turned off all the jobs and there is no
> such
> > things as DTS etc... the config is very simple
> >
> > Any idea what could be causing that?
> >
> > Thanks
> >
> >
>

memory usage and configuration...

Hi,
I've a server to setup.
This server is not only dedicated to SQL Server, but SQL Server is the main
application on the machine. (Datawarehouse system)
I just want to know how to configure my windows swap file?
I know that SQL Server is optimized to not use the Windows Swap file.
I've 6 disks available:
4 * 64 Gb for my SQL Server databases (Raid 5)
On the 2 other disks I've to install Win 2003 + applications + swap file.
How to optimize this?
Where to setup the tempdb database?
Actually I plan to get 10Gb in 1 disk for the OS + application
and setup a stripped volume with the 2 disks for the swap file and the
tempdb file (2*40gb).
Do you think its a good config?
thanks for your feedback.
Jerome.How much memory is in your system in total, and what is
your drive configuration beyond the 6 disk RAID 5 set for
SQL Server? That may not be enough spindles, but I can't
say for sure. If you stick all of your SQL Server files
(system DBs, data/index, logs, tempdb) all on the same
stripe set, you are not buying yourself anything.
So if your goal as I saw is to put everything on the one
R5 stripe, I'd say wholeheartedly no, it is not an optimal
setup at all based on my experience.
However, you need to have done some system profiling to
tell you definitively.|||I've 4gb of RAM
I want to control the usage of my hard drive to keep a good performance.
it's why I want to separate the tempdb database in 1 disk or 2 stripped
disks.
And I'll keep 4 disks in raid 5 for my data. for security reasons.
Maybe I can put my indexes on a separate disk.
Config 1:
Disk0: OS + Applications (10Gb only)
Disk0,1: Stripped - 22Gb + 32 Gb - tempdb - user db indexes - Swap file
Disk3,4,5,6: Raid 5 for system DB and user DB
Config 2:
Disk0: OS + Swap + Applications (10Gb only)
Disk0: tempdb (22gb)
Disk1: indexes (32gb)
Disk3,4,5,6: Raid 5 for system DB and user DB
each disk is a 32gb disk
what do you think about this?
which config is better?
or what is your recommendation?
Also, what is the swap file size required?
"Allan Hirt" <anonymous@.discussions.microsoft.com> a écrit dans le message
de news:5ab501c474a8$a67500b0$a601280a@.phx.gbl...
> How much memory is in your system in total, and what is
> your drive configuration beyond the 6 disk RAID 5 set for
> SQL Server? That may not be enough spindles, but I can't
> say for sure. If you stick all of your SQL Server files
> (system DBs, data/index, logs, tempdb) all on the same
> stripe set, you are not buying yourself anything.
> So if your goal as I saw is to put everything on the one
> R5 stripe, I'd say wholeheartedly no, it is not an optimal
> setup at all based on my experience.
> However, you need to have done some system profiling to
> tell you definitively.

Memory setting on a Cluster

Hello, I am purchasing a HP DL-380 (Cluster)
I am planing to install Windows 2003 and SQL 2000
Both machine has 4 GB o Physical Memory (RAM), but I want to put 6 GB in the
Active Machine and 2 GB in the Passive Machine.
So, That is possible?
1) Is possible to have differentes memory configuration in a cluster?
2) SQL 2000 and Windows 2003 supports 6GB of Physical Memory?
Thanks a lot!!
AA
I forgot to mention that both machine are with 32 bits processors
Thanks again
AA
"AA" <aa@.personal.net.py> wrote in message
news:%23qJn80MIEHA.3848@.tk2msftngp13.phx.gbl...
> Hello, I am purchasing a HP DL-380 (Cluster)
> I am planing to install Windows 2003 and SQL 2000
> Both machine has 4 GB o Physical Memory (RAM), but I want to put 6 GB in
the
> Active Machine and 2 GB in the Passive Machine.
> So, That is possible?
> 1) Is possible to have differentes memory configuration in a cluster?
> 2) SQL 2000 and Windows 2003 supports 6GB of Physical Memory?
>
> Thanks a lot!!
>
> AA
>
|||1. Yes, it is possible. Not sure why you'd want to do it though.
2. Yes, it is supported as long as you enable AWE.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||1. ) because an error in the requirement the cluster come with 4 GB Memory
each, but I think that the Active Server must be more powerful
In the current environment we have installed SQL 2000 in a single machine
dual processor and is UP without restart (the OS and the DB) for more that 6
months, without any kind of problems. In two years we NEVER have any
problems with our SQL 2000 database. I am very happy
So, I want to use the Pasive machine just for emergencies (when the Active
Failure) and increment the Active server power.
Is that ok?
Thanks Michael
AA
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u6MKyDSIEHA.3968@.TK2MSFTNGP12.phx.gbl...
> 1. Yes, it is possible. Not sure why you'd want to do it though.
> 2. Yes, it is supported as long as you enable AWE.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Yes, you can do that. You just need to recognize that if you ever have to
fail over, you can have some performance issues.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Memory required for SQL Server 2000

We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.This is a multi-part message in MIME format.
--060500000502050507020305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
In most situations, SQL Server's memory allocation will be lowest when
you first start the service. SQL Server will cache each data page as it
is read from disk until a minimum free memory threshold is reached (from
memory the threshold is 10MB by default). SQL Server then removes least
recently used pages from its cache, and releases memory, as necessary in
order to maintain the free memory threshold. This means that the longer
the service runs the more memory it will use (up to a limit). This is
Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer &
procedure cache).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
>We have been running an application on SQL Server 2000 on a Windows 2000
>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>From the statistics, we find that the memory utilization of that Windows
>2000 Server is around 200MB of memory. In this way, we attempt to change
>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>We just thinking the rise of memory required is only for the first 1 to 2
>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>memory when we start it up ? Will the memory be stabilized to become 200MB
>again ?
>
>Your advice is sought.
>
>
--060500000502050507020305
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>In most situations, SQL Server's memory allocation will be lowest
when you first start the service. SQL Server will cache each data page
as it is read from disk until a minimum free memory threshold is
reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache,
and releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</body>
</html>
--060500000502050507020305--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C69ED0.60E2CBA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change the maximum RAM setting for the =SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000 will =use most up to 1.5GB RAM ?
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
In most situations, SQL Server's memory allocation will be lowest when =you first start the service. SQL Server will cache each data page as it =is read from disk until a minimum free memory threshold is reached (from =memory the threshold is 10MB by default). SQL Server then removes least =recently used pages from its cache, and releases memory, as necessary in =order to maintain the free memory threshold. This means that the longer =the service runs the more memory it will use (up to a limit). This is =Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you =configure SQL Server to grab its entire memory allocation on service =start-up, in which case all the memory, up to the configured maximum =limit, is reserved up front. However, there are no situations (that I =can think of) where SQL Server initially uses more memory and then =releases a substantial portion of it to "stabilise" at a certain level =(unless you start messing around with DBCC to flush the data buffer & =procedure cache).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Robert wrote: We have been running an application on SQL Server 2000 on a Windows 2000 =
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows =
2000 Server is around 200MB of memory. In this way, we attempt to =change the RAM from 2GB to 1GB. However, we find that the memory utilization =is as high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to =2 hours OR not ? If there any reason for a SQL Server 2000 to grab more memory when we start it up ? Will the memory be stabilized to become =200MB again ?
Your advice is sought.
--=_NextPart_000_0016_01C69ED0.60E2CBA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change =the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does it mean that =the SQL Server 2000 will use most up to 1.5GB RAM ?
Thanks
"Mike Hodgson" wrote =in message news:OYx8BvknGHA.3340=@.TK2MSFTNGP02.phx.gbl...In most situations, SQL Server's memory allocation will be lowest when =you first start the service. SQL Server will cache each data page as it is =read from disk until a minimum free memory threshold is reached (from =memory the threshold is 10MB by default). SQL Server then removes =least recently used pages from its cache, and releases memory, as necessary =in order to maintain the free memory threshold. This means that =the longer the service runs the more memory it will use (up to a =limit). This is Microsoft's dynamic memory management model.One =exception to this "increasing memory use" model is when you configure SQL Server to =grab its entire memory allocation on service start-up, in which case all =the memory, up to the configured maximum limit, is reserved up =front. However, there are no situations (that I can think of) where SQL =Server initially uses more memory and then releases a substantial portion of =it to "stabilise" at a certain level (unless you start messing around with =DBCC to flush the data buffer & procedure cache).
--mike =hodgsonhttp://sqlnerd.blogspot.com Robert wrote: We have been running an application on SQL =Server 2000 on a Windows 2000 Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows =2000 Server is around 200MB of memory. In this way, we attempt to =change the RAM from 2GB to 1GB. However, we find that the memory utilization =is as high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to =2 hours OR not ? If there any reason for a SQL Server 2000 to grab more memory when we start it up ? Will the memory be stabilized to become =200MB again ?
Your advice is sought.

---

--=_NextPart_000_0016_01C69ED0.60E2CBA0--|||This is a multi-part message in MIME format.
--060007000708090601070304
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on the
box (including the OS itself), then part of that memory space will be
backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not apply
to you because you are playing with less than 4GB of physical memory, is
AWE memory, which is always satisfied from physical memory (it is never
swapped to disk).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
> Dear Mike,
> Thank you for your advice.
> If the physical RAM is 2GB and I change the maximum RAM setting for
> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
> will use most up to 1.5GB RAM ?
> Thanks
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
> In most situations, SQL Server's memory allocation will be lowest
> when you first start the service. SQL Server will cache each data
> page as it is read from disk until a minimum free memory threshold
> is reached (from memory the threshold is 10MB by default). SQL
> Server then removes least recently used pages from its cache, and
> releases memory, as necessary in order to maintain the free memory
> threshold. This means that the longer the service runs the more
> memory it will use (up to a limit). This is Microsoft's dynamic
> memory management model.
> One exception to this "increasing memory use" model is when you
> configure SQL Server to grab its entire memory allocation on
> service start-up, in which case all the memory, up to the
> configured maximum limit, is reserved up front. However, there
> are no situations (that I can think of) where SQL Server initially
> uses more memory and then releases a substantial portion of it to
> "stabilise" at a certain level (unless you start messing around
> with DBCC to flush the data buffer & procedure cache).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>>We have been running an application on SQL Server 2000 on a Windows 2000
>>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>>From the statistics, we find that the memory utilization of that Windows
>>2000 Server is around 200MB of memory. In this way, we attempt to change
>>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>>We just thinking the rise of memory required is only for the first 1 to 2
>>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>>memory when we start it up ? Will the memory be stabilized to become 200MB
>>again ?
>>
>>Your advice is sought.
>>
>>
--060007000708090601070304
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on
the box (including the OS itself), then part of that memory space will
be backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not
apply to you because you are playing with less than 4GB of physical
memory, is AWE memory, which is always satisfied from physical memory
(it is never swapped to disk).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="miducrwUrnnGHA.4728@.TK2MSFTNGP03.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2912" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Dear Mike,</font></div>
<div> </div>
<div><font face="Arial" size="2">Thank you for your advice.</font></div>
<div> </div>
<div><font face="Arial" size="2">If the physical RAM is 2GB and I
change the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does
it mean that the SQL Server 2000 will use most up to 1.5GB RAM ?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks</font></div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a href="http://links.10026.com/?link=mailto:e1minst3r@.gmail.com">e1minst3r@.gmail.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl">news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl</a>...</div>
<tt>In most situations, SQL Server's memory allocation will be
lowest when you first start the service. SQL Server will cache each
data page as it is read from disk until a minimum free memory threshold
is reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache, and
releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--060007000708090601070304--|||This is a multi-part message in MIME format.
--070103030000040705020106
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
For a little more background info about it, see the blog entry I just wrote:
http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html
Plus there's a stack of info about it in BOL and several quality
publications including The Guru's Guide to SQL Server Architecture and
Internals
<http://www.amazon.com/gp/product/0201700476/sr=8-2/qid=1152005971/ref=sr_1_2/104-4550407-8498336?ie=UTF8>
by Ken Henderson and Inside SQL Server 2000
<http://www.amazon.com/gp/product/0735609985/sr=8-2/qid=1152006007/ref=pd_bbs_2/104-4550407-8498336?ie=UTF8>
by Kalen Delaney.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> It means that SQL Server will be allowed, at most, a 1.5GB address
> space. It does not necessarily mean this will be backed by physical
> memory. If there is not enough physical memory to support SQL
> Server's requests, in addition to the other applications requesting
> memory on the box (including the OS itself), then part of that memory
> space will be backed by virtual memory, which, needless-to-say, is not
> a high-performance situation. The exception to this, which does not
> apply to you because you are playing with less than 4GB of physical
> memory, is AWE memory, which is always satisfied from physical memory
> (it is never swapped to disk).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>> Dear Mike,
>> Thank you for your advice.
>> If the physical RAM is 2GB and I change the maximum RAM setting for
>> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
>> will use most up to 1.5GB RAM ?
>> Thanks
>>
>> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
>> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
>> In most situations, SQL Server's memory allocation will be lowest
>> when you first start the service. SQL Server will cache each
>> data page as it is read from disk until a minimum free memory
>> threshold is reached (from memory the threshold is 10MB by
>> default). SQL Server then removes least recently used pages from
>> its cache, and releases memory, as necessary in order to maintain
>> the free memory threshold. This means that the longer the
>> service runs the more memory it will use (up to a limit). This
>> is Microsoft's dynamic memory management model.
>> One exception to this "increasing memory use" model is when you
>> configure SQL Server to grab its entire memory allocation on
>> service start-up, in which case all the memory, up to the
>> configured maximum limit, is reserved up front. However, there
>> are no situations (that I can think of) where SQL Server
>> initially uses more memory and then releases a substantial
>> portion of it to "stabilise" at a certain level (unless you start
>> messing around with DBCC to flush the data buffer & procedure cache).
>> --
>> *mike hodgson*
>> http://sqlnerd.blogspot.com
>>
>> Robert wrote:
>>We have been running an application on SQL Server 2000 on a Windows 2000
>>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>>From the statistics, we find that the memory utilization of that Windows
>>2000 Server is around 200MB of memory. In this way, we attempt to change
>>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>>We just thinking the rise of memory required is only for the first 1 to 2
>>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>>memory when we start it up ? Will the memory be stabilized to become 200MB
>>again ?
>>
>>Your advice is sought.
>>
>>
--070103030000040705020106
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>For a little more background info about it, see the blog entry I
just wrote:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html</a><br>">http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html">http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html</a><br>
<br>
Plus there's a stack of info about it in BOL and several quality
publications including <a
href="http://links.10026.com/?link=The">http://www.amazon.com/gp/product/0201700476/sr=8-2/qid=1152005971/ref=sr_1_2/104-4550407-8498336?ie=UTF8">The
Guru's Guide to SQL Server Architecture and Internals</a> by Ken
Henderson and <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/gp/product/0735609985/sr=8-2/qid=1152006007/ref=pd_bbs_2/104-4550407-8498336?ie=UTF8">Inside
SQL Server 2000</a> by Kalen Delaney.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Mike Hodgson wrote:
<blockquote cite="midef076WpnGHA.3340@.TK2MSFTNGP02.phx.gbl" type="cite">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
<tt>It means that SQL Server will be allowed, at most, a 1.5GB
address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on
the box (including the OS itself), then part of that memory space will
be backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not
apply to you because you are playing with less than 4GB of physical
memory, is AWE memory, which is always satisfied from physical memory
(it is never swapped to disk).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="miducrwUrnnGHA.4728@.TK2MSFTNGP03.phx.gbl"
type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;">
<meta content="MSHTML 6.00.2900.2912" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Dear Mike,</font></div>
<div> </div>
<div><font face="Arial" size="2">Thank you for your advice.</font></div>
<div> </div>
<div><font face="Arial" size="2">If the physical RAM is 2GB and I
change the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does
it mean that the SQL Server 2000 will use most up to 1.5GB RAM ?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks</font></div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a href="http://links.10026.com/?link=mailto:e1minst3r@.gmail.com">e1minst3r@.gmail.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl">news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl</a>...</div>
<tt>In most situations, SQL Server's memory allocation will be
lowest when you first start the service. SQL Server will cache each
data page as it is read from disk until a minimum free memory threshold
is reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache, and
releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--070103030000040705020106--

Memory required for SQL Server 2000

We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.In most situations, SQL Server's memory allocation will be lowest when
you first start the service. SQL Server will cache each data page as it
is read from disk until a minimum free memory threshold is reached (from
memory the threshold is 10MB by default). SQL Server then removes least
recently used pages from its cache, and releases memory, as necessary in
order to maintain the free memory threshold. This means that the longer
the service runs the more memory it will use (up to a limit). This is
Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer &
procedure cache).
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:

>We have been running an application on SQL Server 2000 on a Windows 2000
>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>From the statistics, we find that the memory utilization of that Windows
>2000 Server is around 200MB of memory. In this way, we attempt to change
>the RAM from 2GB to 1GB. However, we find that the memory utilization is a
s
>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>We just thinking the rise of memory required is only for the first 1 to 2
>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>memory when we start it up ? Will the memory be stabilized to become 200MB
>again ?
>
>Your advice is sought.
>
>|||Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change the maximum RAM setting for the SQL
Server 2000 to 1.5GB. Does it mean that the SQL Server 2000 will use most u
p to 1.5GB RAM ?
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:OYx8BvknGHA.3340@.
TK2MSFTNGP02.phx.gbl...
In most situations, SQL Server's memory allocation will be lowest when you f
irst start the service. SQL Server will cache each data page as it is read
from disk until a minimum free memory threshold is reached (from memory the
threshold is 10MB by default). SQL Server then removes least recently used
pages from its cache, and releases memory, as necessary in order to maintain
the free memory threshold. This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic memor
y management model.
One exception to this "increasing memory use" model is when you configure SQ
L Server to grab its entire memory allocation on service start-up, in which
case all the memory, up to the configured maximum limit, is reserved up fron
t. However, there are no situations (that I can think of) where SQL Server
initially uses more memory and then releases a substantial portion of it to
"stabilise" at a certain level (unless you start messing around with DBCC to
flush the data buffer & procedure cache).
mike hodgson
http://sqlnerd.blogspot.com
Robert wrote:
We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.|||It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on the
box (including the OS itself), then part of that memory space will be
backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not apply
to you because you are playing with less than 4GB of physical memory, is
AWE memory, which is always satisfied from physical memory (it is never
swapped to disk).
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
[vbcol=seagreen]
> Dear Mike,
> Thank you for your advice.
> If the physical RAM is 2GB and I change the maximum RAM setting for
> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
> will use most up to 1.5GB RAM ?
> Thanks
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
> In most situations, SQL Server's memory allocation will be lowest
> when you first start the service. SQL Server will cache each data
> page as it is read from disk until a minimum free memory threshold
> is reached (from memory the threshold is 10MB by default). SQL
> Server then removes least recently used pages from its cache, and
> releases memory, as necessary in order to maintain the free memory
> threshold. This means that the longer the service runs the more
> memory it will use (up to a limit). This is Microsoft's dynamic
> memory management model.
> One exception to this "increasing memory use" model is when you
> configure SQL Server to grab its entire memory allocation on
> service start-up, in which case all the memory, up to the
> configured maximum limit, is reserved up front. However, there
> are no situations (that I can think of) where SQL Server initially
> uses more memory and then releases a substantial portion of it to
> "stabilise" at a certain level (unless you start messing around
> with DBCC to flush the data buffer & procedure cache).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>|||For a little more background info about it, see the blog entry I just wrote:
http://sqlnerd.blogspot.com/2006/07...sql-server.html
Plus there's a stack of info about it in BOL and several quality
publications including The Guru's Guide to SQL Server Architecture and
Internals
<http://www.amazon.com/gp/product/02...8498336?ie=UTF8>
by Ken Henderson and Inside SQL Server 2000
<http://www.amazon.com/gp/product/07...8498336?ie=UTF8>
by Kalen Delaney.
*mike hodgson*
http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> It means that SQL Server will be allowed, at most, a 1.5GB address
> space. It does not necessarily mean this will be backed by physical
> memory. If there is not enough physical memory to support SQL
> Server's requests, in addition to the other applications requesting
> memory on the box (including the OS itself), then part of that memory
> space will be backed by virtual memory, which, needless-to-say, is not
> a high-performance situation. The exception to this, which does not
> apply to you because you are playing with less than 4GB of physical
> memory, is AWE memory, which is always satisfied from physical memory
> (it is never swapped to disk).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>

Memory Questions

I am running SSAS 2005 SP1 on a machine with Windows 2003 Server R2 and 2 GB of memory.I have approximately 30 analysis databases defined on this machine.

When I process one of my analysis databases, MSMDSRV.EXE gradually takes over 1 GB of virtual memory and does not release it once processing is completed.Once processing completes, MSMDSRV.EXE continues to take up 5-15% of CPU to process something.This goes on for hours.

Let’s say I go into BIDS on this machine and try to open an analysis database by entering the server name then attempting to select a database from the database drop down.It takes about 10 minutes for the list of databases to appear on the screen, and you can watch MSMDSRV.EXE take up more and more virtual memory until it takes over 1 GB of virtual memory.

- Why is SSAS taking up so much virtual memory to perform these operations? Is it metadata overload?

- Is there any way to get MSMDSRV.EXE to release the virtual memory it has taken?

- Once database processing has been completed, what is MSMDSRV.EXE doing that would take 5-15% of CPU?

- Is there a recommended solution to improve memory handling and performance in this situation?

Thanks in advance for your help.

Wendell G.

Is this a recent event or a continual event?

Is the server recognizing the 2gig's of RAM and the full capacity of the processor(s)?

How old is the server?

Adamus

|||

This has been a continual event and has appeared to get worse as more analysis services databases are added to the server.

The server is recognizing the 2 GB ram and is less than one year old.

Wendell G.

|||

Not knowing your situation, the immiteate suggestion is to increase memory of your machine.

2 Gb looks litte on the lower side.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Memory Questions

I am running SSAS 2005 SP1 on a machine with Windows 2003 Server R2 and 2 GB of memory.I have approximately 30 analysis databases defined on this machine.

When I process one of my analysis databases, MSMDSRV.EXE gradually takes over 1 GB of virtual memory and does not release it once processing is completed.Once processing completes, MSMDSRV.EXE continues to take up 5-15% of CPU to process something.This goes on for hours.

Let’s say I go into BIDS on this machine and try to open an analysis database by entering the server name then attempting to select a database from the database drop down.It takes about 10 minutes for the list of databases to appear on the screen, and you can watch MSMDSRV.EXE take up more and more virtual memory until it takes over 1 GB of virtual memory.

- Why is SSAS taking up so much virtual memory to perform these operations? Is it metadata overload?

- Is there any way to get MSMDSRV.EXE to release the virtual memory it has taken?

- Once database processing has been completed, what is MSMDSRV.EXE doing that would take 5-15% of CPU?

- Is there a recommended solution to improve memory handling and performance in this situation?

Thanks in advance for your help.

Wendell G.

Is this a recent event or a continual event?

Is the server recognizing the 2gig's of RAM and the full capacity of the processor(s)?

How old is the server?

Adamus

|||

This has been a continual event and has appeared to get worse as more analysis services databases are added to the server.

The server is recognizing the 2 GB ram and is less than one year old.

Wendell G.

|||

Not knowing your situation, the immiteate suggestion is to increase memory of your machine.

2 Gb looks litte on the lower side.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I have exactly the same situation but I have 12G RAM
each process of the cube increases the memory of the msmdsrv.exe by 2G
eventualy after 4 / 5 processings it fails with a page file % nearly full and the users cannot access. CPU useage by is 13% constant build of sql is 3054 on test server and 3152 on live server both servers have same issue.

Was there an answer to the above appart from more RAM i.e. should the memory be released?

Firts process of cube useage = 951M ,
Second process of cube usage starts at 951M and completes at 2,951M
Third process starts at 2,951 and increases etc and so on till memory all used
Thanks