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/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-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...[vb
col=seagreen]
> 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 wh
en
> 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 efficien
t
> of course).
> In another word, how do I know when to start investing in more hardware fo
r
> my sql server?
> thanks!
>[/vbcol]

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 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!
/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/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>
|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.

> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?

> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.

> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
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/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb[vbcol=seagreen]
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return
>

Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.

> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?

> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.

> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficu
lt to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?[/vb
col]
The key word here is "nearly". There are several ways a query plan can be re
-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (
worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest yo
u investigate using the
system table syscacheobjects in the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andrew,
> Thanks for the response. Your information agrees with what I have run acr
oss searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curio
us about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secre
t handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familia
r with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sen
t to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
would be difficult to
> predict how many "cached plans" there actually are would be, with about 18
to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached pl
ans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any incre
ase today. So far,
> performance of the overall application hasn't shown any degradation. This
is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our
target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
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/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
another[vbcol=seagreen]
those[vbcol=seagreen]
procedure[vbcol=seagreen]
stress[vbcol=seagreen]
SQL[vbcol=seagreen]
15Mb.[vbcol=seagreen]
process[vbcol=seagreen]
return[vbcol=seagreen]
>

Memory Usage/Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!Daniel,
The min and max settings are for the memory pool only and there is another
section of memory called the MemToLeave area that is not included in those
limits. This includes such things as the SQL Server exe, dll', extended
procs, net libs etc. Even the worker threads can use up to 128MB by
default. But more than likely you are seeing the results of the procedure
cache (which is included in the memory pool). Especiallyif you are stress
testing with adhoc queries. SQL Server will use as much memory as is
available up until the limits if any are set. Even if your data only
amounts to 15MB you can have many thousands of cached plans in the procedure
cache and they will stay there until the OS asks for some of SQL Servers
memory. By default the OS and SS will dynamically share the available
memory but if the OS never asks for it SS will keep it. So if you never set
a limit SS expects to use all that it can and that is by design and usually
works best.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>|||Andrew,
Thanks for the response. Your information agrees with what I have run
across searching the Net and the KB. If you don't mind fielding a couple
more questions, I'm curious about the mechanism that the OS uses to ask SQL
Server for the memory back. Is there a 'secret handshake' between the OS
and the service that's not disclosed to the public? I'm pretty familiar
with the Win32 SDK and the only things I know of is the WM_COMPACTING
message - but it's only sent to top-level windows - something that SQL
Server running as a service doesn't have.
Secondly, without having more detail information about my application, it
would be difficult to predict how many "cached plans" there actually are
would be, with about 18 to 20 queries with nearly identical predicates, why
wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
is for?
Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79 Mb
VM without any increase today. So far, performance of the overall
application hasn't shown any degradation. This is on a 512Mb box -getting
SQL Server to 'play well with others' is critical because our target
platform is 256Mb! (No choice and not open to discussion)
BTW: Is cascading deletes as resource intensive as it appears to be?
Thanks,
Dan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Daniel,
> The min and max settings are for the memory pool only and there is another
> section of memory called the MemToLeave area that is not included in those
> limits. This includes such things as the SQL Server exe, dll', extended
> procs, net libs etc. Even the worker threads can use up to 128MB by
> default. But more than likely you are seeing the results of the procedure
> cache (which is included in the memory pool). Especiallyif you are stress
> testing with adhoc queries. SQL Server will use as much memory as is
> available up until the limits if any are set. Even if your data only
> amounts to 15MB you can have many thousands of cached plans in the
> procedure cache and they will stay there until the OS asks for some of SQL
> Servers memory. By default the OS and SS will dynamically share the
> available memory but if the OS never asks for it SS will keep it. So if
> you never set a limit SS expects to use all that it can and that is by
> design and usually works best.
> --
> Andrew J. Kelly SQL MVP
>
> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process
>> in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of
>> VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration
>> settings
>> are really just "suggestions"!
>|||> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
To be honest I knew exactlkly what it was at one time as one of the SQL
Server developers told me but don't recall what it is. Other than the
settings in SQL Server (and maybe some of the Win2003 management features)
you can't really do anything special otherwise.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
> why wouldn't SQL Server "reuse" the cached plans? Isn't that what the
> "cache" is for?
Nearly identical is not good enough if the calls are not done properly. If
you use RPC calls and specify parameters properly the plans should be cached
and reused. Sending in a SQL string and executing it will result in a new
plan for each variation of the string including spaces. If you only have 20
different types of queries then syscacheobjects ideally should only have
about 100 rows or so in it at any particular time. What does select
COUNT(*) FROM Master..Syscacheobjects give you?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
> Mb
Well here is the official minimum requirements from BOL:
Enterprise Edition and Enterprise Evaluation Edition: 64 MB minimum, 128 MB
or more recommended
Standard Edition and Developer Edition: 64 MB minimum
Personal Edition and Desktop Engine (MSDE 2000):
a.. 128 MB minimum on Windows XP
b.. 64 MB minimum on Windows 2000
c.. 32 MB minimum on all other operating systems
Don't know what edition you plan to run and what you are doing with it but
good luck with PC's with 256MB's<g>.
> BTW: Is cascading deletes as resource intensive as it appears to be?
It all depends. You need proper indexes and RI to make it work properly but
it is simply a series of index seeks and deletes.
--
Andrew J. Kelly SQL MVP
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...|||> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans?
The key word here is "nearly". There are several ways a query plan can be re-used. Stored procedure
(best), explicit prepare and then execute, auto parameteration, and ad-hoc (worst). My guess is that
you have ad-hoc for which the query text has to be *identical*. I suggest you investigate using the
system table syscacheobjects in the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run across searching the Net
> and the KB. If you don't mind fielding a couple more questions, I'm curious about the mechanism
> that the OS uses to ask SQL Server for the memory back. Is there a 'secret handshake' between the
> OS and the service that's not disclosed to the public? I'm pretty familiar with the Win32 SDK and
> the only things I know of is the WM_COMPACTING message - but it's only sent to top-level windows -
> something that SQL Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it would be difficult to
> predict how many "cached plans" there actually are would be, with about 18 to 20 queries with
> nearly identical predicates, why wouldn't SQL Server "reuse" the cached plans? Isn't that what
> the "cache" is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and 64Mb max - after about
> 18 hrs, sqlservr.exe had grown to a little over 79 Mb VM without any increase today. So far,
> performance of the overall application hasn't shown any degradation. This is on a 512Mb
> box -getting SQL Server to 'play well with others' is critical because our target platform is
> 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Daniel,
>> The min and max settings are for the memory pool only and there is another section of memory
>> called the MemToLeave area that is not included in those limits. This includes such things as
>> the SQL Server exe, dll', extended procs, net libs etc. Even the worker threads can use up to
>> 128MB by default. But more than likely you are seeing the results of the procedure cache (which
>> is included in the memory pool). Especiallyif you are stress testing with adhoc queries. SQL
>> Server will use as much memory as is available up until the limits if any are set. Even if your
>> data only amounts to 15MB you can have many thousands of cached plans in the procedure cache and
>> they will stay there until the OS asks for some of SQL Servers memory. By default the OS and SS
>> will dynamically share the available memory but if the OS never asks for it SS will keep it. So
>> if you never set a limit SS expects to use all that it can and that is by design and usually
>> works best.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in message
>> news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
>>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
>> However, after running an endurance test for 5 days, the sqlservr process in
>> TaskMan has grown to over 550Mb VM size.
>> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
>> space and never give it back? And/or how to encourage sqlservr to return
>> memory that it shouldn't be using?
>> Thanks in advance,
>> Dan
>> P.S. BTW I already know that min & max server memory configuration settings
>> are really just "suggestions"!
>>
>|||Hi
No secret handshake. SQL Server only used publicly published API's (AFAIK,
SQL server calls malloc to allocate memory)
I can't find the API, but the OS sends a message requesting memory release
and then SQL Server tries to release it if possible (realloc or free).
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/
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:e7XQNI28EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> Thanks for the response. Your information agrees with what I have run
> across searching the Net and the KB. If you don't mind fielding a couple
> more questions, I'm curious about the mechanism that the OS uses to ask
SQL
> Server for the memory back. Is there a 'secret handshake' between the OS
> and the service that's not disclosed to the public? I'm pretty familiar
> with the Win32 SDK and the only things I know of is the WM_COMPACTING
> message - but it's only sent to top-level windows - something that SQL
> Server running as a service doesn't have.
> Secondly, without having more detail information about my application, it
> would be difficult to predict how many "cached plans" there actually are
> would be, with about 18 to 20 queries with nearly identical predicates,
why
> wouldn't SQL Server "reuse" the cached plans? Isn't that what the "cache"
> is for?
> Yesterday evening we tried set limits on SQL Server Memory to 16Mb min and
> 64Mb max - after about 18 hrs, sqlservr.exe had grown to a little over 79
Mb
> VM without any increase today. So far, performance of the overall
> application hasn't shown any degradation. This is on a 512Mb box -getting
> SQL Server to 'play well with others' is critical because our target
> platform is 256Mb! (No choice and not open to discussion)
> BTW: Is cascading deletes as resource intensive as it appears to be?
> Thanks,
> Dan
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uIVG7ds8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Daniel,
> >
> > The min and max settings are for the memory pool only and there is
another
> > section of memory called the MemToLeave area that is not included in
those
> > limits. This includes such things as the SQL Server exe, dll', extended
> > procs, net libs etc. Even the worker threads can use up to 128MB by
> > default. But more than likely you are seeing the results of the
procedure
> > cache (which is included in the memory pool). Especiallyif you are
stress
> > testing with adhoc queries. SQL Server will use as much memory as is
> > available up until the limits if any are set. Even if your data only
> > amounts to 15MB you can have many thousands of cached plans in the
> > procedure cache and they will stay there until the OS asks for some of
SQL
> > Servers memory. By default the OS and SS will dynamically share the
> > available memory but if the OS never asks for it SS will keep it. So if
> > you never set a limit SS expects to use all that it can and that is by
> > design and usually works best.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "daniel.ferguson" <daniel.ferguson@.discussions.microsoft.com> wrote in
> > message news:8A46C920-A536-42BC-8E26-B71D0ABB9648@.microsoft.com...
> >>I have a database that starts out as 9Mb. Never grows to more than
15Mb.
> >> However, after running an endurance test for 5 days, the sqlservr
process
> >> in
> >> TaskMan has grown to over 550Mb VM size.
> >>
> >> Can anyone help me understand how a 15 Mb database can consume 550Mb of
> >> VM
> >> space and never give it back? And/or how to encourage sqlservr to
return
> >> memory that it shouldn't be using?
> >>
> >> Thanks in advance,
> >>
> >> Dan
> >>
> >> P.S. BTW I already know that min & max server memory configuration
> >> settings
> >> are really just "suggestions"!
> >>
> >
> >
>

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 / Hog

I have a database that starts out as 9Mb. Never grows to more than 15Mb.
However, after running an endurance test for 5 days, the sqlservr process in
TaskMan has grown to over 550Mb VM size.
Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
space and never give it back? And/or how to encourage sqlservr to return
memory that it shouldn't be using?
Thanks in advance,
Dan
P.S. BTW I already know that min & max server memory configuration settings
are really just "suggestions"!
Hi Dan,
By default, sql server (of any ilk) will grow to use whatever memory is
available in the system. The main assumption is that it's the key thing
that's happening on that server. You can limit the usage if you want. In
Enterprise Manager, if you right-click the server, you can set a maximum
memory value under the "Memory" tab. Alternately, you can set it via T-SQL.
We find we can get it to "play" happily with other apps that way. Exchange
is the other main culprit if it happens to be on the same box and we often
have to limit the size of store.exe when it's there as well.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
news:OMDX9vp8EHA.2012@.TK2MSFTNGP15.phx.gbl...
>I have a database that starts out as 9Mb. Never grows to more than 15Mb.
> However, after running an endurance test for 5 days, the sqlservr process
> in
> TaskMan has grown to over 550Mb VM size.
> Can anyone help me understand how a 15 Mb database can consume 550Mb of VM
> space and never give it back? And/or how to encourage sqlservr to return
> memory that it shouldn't be using?
> Thanks in advance,
> Dan
> P.S. BTW I already know that min & max server memory configuration
> settings
> are really just "suggestions"!
>
|||Hi Greg,
Since this is MSDE, we don't have Enterprise Manager. How do we limit it
with just the stuff provided with MSDE?

> The main assumption is that it's the key thing that's happening on that
server.
Obviously that's not a good assumption when MSDE is included as part of
another application. :-)
Thanks,
Ray
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:O0Ba53r8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi Dan,
> By default, sql server (of any ilk) will grow to use whatever memory is
> available in the system. The main assumption is that it's the key thing
> that's happening on that server. You can limit the usage if you want. In
> Enterprise Manager, if you right-click the server, you can set a maximum
> memory value under the "Memory" tab. Alternately, you can set it via
T-SQL.[vbcol=seagreen]
> We find we can get it to "play" happily with other apps that way. Exchange
> is the other main culprit if it happens to be on the same box and we often
> have to limit the size of store.exe when it's there as well.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "daniel.ferguson" <daniel.ferguson.nospam@.kodak.com> wrote in message
> news:OMDX9vp8EHA.2012@.TK2MSFTNGP15.phx.gbl...
process[vbcol=seagreen]
VM[vbcol=seagreen]
return
>
|||hi Ray,
"abc" <please@.newsgroup.only> ha scritto nel messaggio
news:OQ15beC9EHA.3820@.TK2MSFTNGP11.phx.gbl
> Hi Greg,
> Since this is MSDE, we don't have Enterprise Manager. How do we limit
> it with just the stuff provided with MSDE?
>
> Obviously that's not a good assumption when MSDE is included as part
> of another application. :-)
if you don't like Transact-SQL, you can have a look at a free prj of mine,
which requires SQL-DMO to be installed, that provide a user interface
similar to Enterprise Manager, where your memory needs can be set in a
"friendly" UI
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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 portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confused
Bunch of good articles here:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.gbl...
>I want to know what this is virtual memory address space is . Where can i understand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

memory portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confusedBunch of good articles here:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.g
bl...
>I want to know what this is virtual memory address space is . Where can i u
nderstand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

memory portion tied to -g switch of sqlservr

I want to know what this is virtual memory address space is . Where can i
understand more about this particular allocation and why just 256MB is given
for it ?
Just a bit confusedBunch of good articles here:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:eW40%23lP4FHA.2640@.TK2MSFTNGP09.phx.gbl...
>I want to know what this is virtual memory address space is . Where can i understand more about
>this particular allocation and why just 256MB is given for it ?
> Just a bit confused
>

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: