Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.
Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

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 of sqlservr.exe

Hi,

Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.

The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.

regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.

To set a fixed amount of memory

Expand a server group.

Right-click a server, and then click Properties.

Click the Memory tab.

Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!

Thaks for your valuable input.

Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.

Please advise.

Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!

I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.

Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.

Best regards!

Memory usage

I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.

PeteThis is a pretty common question from people who do not administer SQL Servers or even Exchange servers for that matter. SQL Server is designed to take up memory and not release it unless something else needs it. SQL Server will allow 4 - 10 MB of physical memory for other processes. If this is a dedicated server, it is nothing to worry about. He is actually getting something use out of all that memory.|||I'm sure for a server that needed that much use out of its memory it would be good, but for a server that has maybe 20 sites on it and a probably 8gb of traffic a month for sites that use the sql server 2.9gb is unacceptable to me. I have mysql running along side it which I admin and it is only using a quarter of what sql server is using.|||If the box is paging memory, then you can put a cap on SQL Server's consumption of memory. If not, I am not sure why you would bother.

Memory pb

Hie,
When i restrict the memory of the db in property dialog box, the process sqlserv.exe increase his memry above this limit why ?Have you enabled AWE? That pretty much circumvents the normal Windows process management, mostly because that is exactly what it is supposed to do!

Even if you haven't enabled AWE, the server responds to its environment. If large blocks of RAM are not being used, then the OS can elect to dynamically ease the restrictions on an application that is memory starved (which happens fast with a busy SQL Server).

-PatP

Memory of sqlserver.exe cannot released

Hi everybody

Help me, Help me.
When I backup the MSSQL databases using ARCServe 2000 in the server, the memory of sqlserver.exe is 1.5G in task manager. After backup was finished, the memory of sqlserver.exe cannot released. It is still 1.5G. Our users can get the resource of that server but they get it slowly. I need to stop and restart the MSSQL service. The server can back to normal.
Can everyone fix it ?

My server is
Windows 2000 Server Standard Edition with SP4
Windows SQL 2000 Standard Edition With SP3
Memory is 2G RAM

Pls Help.
ThanksDo you want to stop and restart (http://msdn2.microsoft.com/en-us/library/ms187598.aspx) SQL Server from a script?

-PatP|||PatP
Thank you for your reply
I got a one problem. I must need to restart the service using password because this is security reason. So, how to write a script with password|||Hi Everybody

I want to reinstall ARCserve 2000 on the server but I lost the license key.
I cannot register it. Can I get or export or read the license key / serial number on existing ARCserve 2000 of server for reinstallation ?

Thank you|||You don't need a password to stop or restart SQL Server. If you are using a Windows Service login, then the password is stored by the service itself. If you need to run the script as a machine administrator, then that password needs to be stored with the Windows Task description. You don't need to code the password into the script, that's a very bad idea and should be avoided.

-PatP|||could the problem be on the arcserv end. i do not know arcserv, but i am wondering if it is leaving connections open or something like that. have you tried opening up profiler to see what is going on?|||Hi Pat
Thanks
A applications is running with connecting the SQL server on that server. If the sql service is restarted, I need to provide two passwords for application to run it. Although the sql service can use the script automatically, the application on that server need to be ran manually every time.

Memory Leaks using MSDE 2000

Hello.
Our company is running an application that uses the MSDE 2000 (SP4), to load
large amount of data using bulk inserts.
The sqlservr.exe process increases from the 4-10MB that it starts with to
80-100MB after all transactions has been completed.
When starting a new transaction session, the process just continue to grow
causing the OS to perform poorly.
Ok, I understand that while on an insert transaction, the memory should grow
but when we are done, why doesn’t it release the resources?
You can see that this is a bad behavior if you just close the process
manually and start it again, the memory will start with 4-10MB, and the
application still works, meaning that the exe have lots of unused resources.
What can I do about that (other than limiting the memory size to xMB)?
Hi
Not leaking, it is by design
INF: SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
> Hello.
> Our company is running an application that uses the MSDE 2000 (SP4), to
> load
> large amount of data using bulk inserts.
> The sqlservr.exe process increases from the 4-10MB that it starts with to
> 80-100MB after all transactions has been completed.
> When starting a new transaction session, the process just continue to grow
> causing the OS to perform poorly.
> Ok, I understand that while on an insert transaction, the memory should
> grow
> but when we are done, why doesn't it release the resources?
> You can see that this is a bad behavior if you just close the process
> manually and start it again, the memory will start with 4-10MB, and the
> application still works, meaning that the exe have lots of unused
> resources.
> What can I do about that (other than limiting the memory size to xMB)?
>
>
|||Ok, it’s by design, pretty weird design, by ok.
Now, how can I handle this, design or not, it is a problem…
Limiting the max memory? Stopping and starring the process of the SQL?
What is the best practice for it?
Eli.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Not leaking, it is by design
> INF: SQL Server Memory Usage
> http://support.microsoft.com/default...b;en-us;321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Eli" <Eli@.discussions.microsoft.com> wrote in message
> news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
>
>
|||Hi
Set the MAX memory.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:5D23EFC5-6240-47E3-AF83-B8B0CB0DC5D7@.microsoft.com...[vbcol=seagreen]
> Ok, it's by design, pretty weird design, by ok.
> Now, how can I handle this, design or not, it is a problem.
> Limiting the max memory? Stopping and starring the process of the SQL?
> What is the best practice for it?
> Eli.
>
> "Mike Epprecht (SQL MVP)" wrote: