MSDE RelA using all memory

Six months ago I rework our application to use MSDE as a desktop DB. It was
used to replace Paradox. I develop in Delphi with ADO.
I realized tremendous performance gains and the ability to use stored procs
and computed columns made many tasks far eaiser. But we seemed to have shot
outselves in the foot. Because to the significant performance gains, we
added additional features that involved complex queries.
Our application, which supports the automation of chicken processing, is
very DB intensive. We write a record to the database every 80 ms or so.
Lately some of our bigger clients have complained of application freeze-ups.
Upon further investigation, it turns out to be due to the consumption of
available memory by sqlsevr.exe. The executable goes consistantly and not at
a slow rate.
We have wittnessed it growing from 8 mb to over 700 mb in six hours. I
found some info via google and have tried setting the memory throttle but I
don't think it is working. The sqlsevr seems to blow right by the setting.
Presently we are advising out clients to install loads of ram and that does
help but we need a real resolution.
Our clients run on Win2000Pro
I am deparately looking for some advise to either limit the rate of the
executable or it's max size.
Thanks
Run sp_configure to check what's the value for "max server memory
(MB)". Probably it will be 2147483647 which means SQL server can use
all of the available memory. While this is good for the performance of
SQL server, it might cause problems for other apps on the server.
So if the machine is not a dedicated database server (and usually MSDE
instances are not), limit the max server memeory to a more convenient
number.
M
|||hi,
DelphiDeveloper wrote:
> Six months ago I rework our application to use MSDE as a desktop DB.
> It was used to replace Paradox. I develop in Delphi with ADO.
> I realized tremendous performance gains and the ability to use stored
> procs and computed columns made many tasks far eaiser. But we seemed
> to have shot outselves in the foot. Because to the significant
> performance gains, we added additional features that involved complex
> queries.
> Our application, which supports the automation of chicken processing,
> is very DB intensive. We write a record to the database every 80 ms
> or so.
> Lately some of our bigger clients have complained of application
> freeze-ups. Upon further investigation, it turns out to be due to the
> consumption of available memory by sqlsevr.exe. The executable goes
> consistantly and not at a slow rate.
> We have wittnessed it growing from 8 mb to over 700 mb in six hours.
> I found some info via google and have tried setting the memory
> throttle but I don't think it is working. The sqlsevr seems to blow
> right by the setting.
> Presently we are advising out clients to install loads of ram and
> that does help but we need a real resolution.
> Our clients run on Win2000Pro
> I am deparately looking for some advise to either limit the rate of
> the executable or it's max size.
> Thanks
in order to better understand memory management in SQL Server/MSDE you can
have a look at
http://www.windowsitpro.com/Article/...908/37908.html and
http://www.windowsitpro.com/Article/...908/37908.html by Notre Dame
SQL Server Kalen Delany or, at a lower level at
http://msdn.microsoft.com/library/de...v_03252004.asp
by Ken Henderson...
SQL Server/MSDE manages memory at it's best without human intervention, but
even if concerned about OS/other application pressures and really doing it's
best to as gentleman as possible and freing unused (and even used memory
discarding query plans and cache) it works best if there's not much
concurrency for resources on the pc it is istalled on..
limitg it's max memomory setting will decrease it's responseness (is this
english? :D) and fewer query plans, cached objects and so on will be
available, requiring mor I/O activities (among the other)... If I corectly
understand your scenario, you are working quiet real-time, and forcing MSDE
into lower memory barriers can causes troubles..
actually 700 mb for SQL Server are not that much, but you know your
customers...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE Memory Usage for Desktop Users

Hi all,
We are developing an application that uses MSDE as the database backend
running on the client's computer. As I understand it, SQL Server's memory
management model allows it to basically use as much memory as it possible
can when under heavy load. Then having allocated that much memory, SQL
Server will not release it unless another program needs it. Does MSDE work
in the same way? Would it be possible to signal SQL Server to release some
memory? If so, then we could signal MSDE to release some memory after we
have finished our major data crunching. This might at least give our
clients some relief as they have a tendency to become disconcerted by
whatever Task Manager tells them, no matter how much I tell them that SQL
Server will easily give up memory to allow other programs to run.
Thanks for any insight.
Jonathan
hi Jonathan,
"Jonathan Van Eenwyk" <jonathanve@.myrealbox.com> ha scritto nel messaggio
news:10hfeqaftls4778@.corp.supernews.com...
> Hi all,
> We are developing an application that uses MSDE as the database backend
> running on the client's computer. As I understand it, SQL Server's memory
> management model allows it to basically use as much memory as it possible
> can when under heavy load. Then having allocated that much memory, SQL
> Server will not release it unless another program needs it. Does MSDE
work
> in the same way? Would it be possible to signal SQL Server to release
some
> memory? If so, then we could signal MSDE to release some memory after we
> have finished our major data crunching. This might at least give our
> clients some relief as they have a tendency to become disconcerted by
> whatever Task Manager tells them, no matter how much I tell them that SQL
> Server will easily give up memory to allow other programs to run.
> Thanks for any insight.
> Jonathan
>
you can't... SQL Server (and MSDE so far) are totally disregarding
application signal... you can only limit it' memory footprint overriding the
dynamic memory configuration, setting a fixed upperbound limit...
please have a look at
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
for further info about fixing memory...
another article, by Ken Henderson, available at
http://msdn.microsoft.com/library/de...v_03252004.asp
is worth reading too...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Alright. Thanks! Seems like that would be nice feature in a desktop
situation, but I guess that's not the real purpose of SQL Server.
Jonathan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2nsbhvF46n5eU1@.uni-berlin.de...[vbcol=seagreen]
> hi Jonathan,
> "Jonathan Van Eenwyk" <jonathanve@.myrealbox.com> ha scritto nel messaggio
> news:10hfeqaftls4778@.corp.supernews.com...
memory[vbcol=seagreen]
possible[vbcol=seagreen]
> work
> some
we[vbcol=seagreen]
SQL
> you can't... SQL Server (and MSDE so far) are totally disregarding
> application signal... you can only limit it' memory footprint overriding
the
> dynamic memory configuration, setting a fixed upperbound limit...
> please have a look at
>
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
> for further info about fixing memory...
> another article, by Ken Henderson, available at
>
http://msdn.microsoft.com/library/de...v_03252004.asp
> is worth reading too...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Jonathan,
"Jonathan Van Eenwyk" <jonathanve@.myrealbox.com> ha scritto nel messaggio
news:10hi63aggt5ja4b@.corp.supernews.com...
> Andrea,
> Alright. Thanks! Seems like that would be nice feature in a desktop
> situation, but I guess that's not the real purpose of SQL Server.
really not... =;-DD
SQL Server is suited to perform it's work the best it can and as quick it
can, with no regards (or little) to other boring users =;-DD
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE memory usage

I am using VB .NET to convert data from Access to MSDE.
While I am transferring table by table, the memory usage of 'sqlsvr' keeps increasing, and also CPU usage is @. 100 %
Some Tables have more than 10,000 records. Is there a way to reduce the memory usage or an explaination why would memory usage increase each time MDSE is accessed.
Just to note, there is only one connection to each database(Acces97 and MSDE)
and all the recordset connections are opened and closed
Thanks
Bobby
hi Bobby,
"Bobby" <Bobby@.discussions.microsoft.com> ha scritto nel messaggio
news:70184C7E-3CBF-4BEB-9381-A3AA8174A646@.microsoft.com...
> I am using VB .NET to convert data from Access to MSDE.
> While I am transferring table by table, the memory usage of 'sqlsvr' keeps
increasing, and also CPU usage is @. 100 %
> Some Tables have more than 10,000 records. Is there a way to reduce the
memory usage or an explaination why would memory usage increase each time
MDSE is accessed.
> Just to note, there is only one connection to each database(Acces97 and
MSDE)
> and all the recordset connections are opened and closed
>
i think Ken Henderson's book extracted, available at
http://msdn.microsoft.com/library/de...v_03252004.asp
is worth reading..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE memory Usage

It seems that the MSDE running SP3a is eating up about 100MB every few days
on my system. Once I reboot it settles down but starts it up again. Is
there a memory leak? Is there a way to calm down the SQLSERVER.EXE memory
usuage?
Thanks
The SQL Server 2000 database engine is designed to take advantage of as much
memory as it can without impacting other applications. If other applications
start running on the computer the database engine will dynamically release
memory for their use. You can also tailor how much memory the database
engine uses by setting configuration options.
For more information, see:
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

msde memory setting

What memory settings should i configure to tune msde?
Sometimes it will be installed on a PC but at other times it will be an app
server. On the app server i want to take advantage of the increase in memory.
hi,
Keeper wrote:
> What memory settings should i configure to tune msde?
> Sometimes it will be installed on a PC but at other times it will be
> an app server. On the app server i want to take advantage of the
> increase in memory.
usually you should not manually "tune" SQL Server as it is tailored to self
configure it self...
on the other hand, SQL Server is very resources intensive, and is designed
to take advantage of all the installed available memory (in sys32 processes
limit )
and it's a very challenging taks to manual configure it for best
performance..
remember that memory usage is database and application dependent, but usage
and workloads dependent too...
Loosely speaking, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding
and because MSDE does not admit it)..
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, linked server drivers, NetLib dlls, in process COM objects space
and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
more, each connection, live or sleeping, will eat about 24kb of memory,
calculated as 12 KB + 3 times the Network Packet Size (default setting that
can be partially customized via sp_configure system stored procedure
modifying the 'user connections' setting), used to store the data structures
holding the connection 's context, as long as for buffer used to send and
receive the relative associated network streams (default to 4KB network
packet setting), that can be stolen from the buffer pool memory region
and/or the MemToLeave memory area...
not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
in our example) for it's uses, and it will perhaps release it only under
pressure by the OS claiming for additional memory, if it's the case, else it
will maintain that memory in order to cache pages and execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releasing
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and staying there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resources
contentions for sure...
I strongly advice you for
http://www.windowsitpro.com/Article/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
Keeper wrote:
> What memory settings should i configure to tune msde?
> Sometimes it will be installed on a PC but at other times it will be
> an app server. On the app server i want to take advantage of the
> increase in memory.
usually you should not manually "tune" SQL Server as it is tailored to self
configure it self...
on the other hand, SQL Server is very resources intensive, and is designed
to take advantage of all the installed available memory (in sys32 processes
limit )
and it's a very challenging taks to manual configure it for best
performance..
remember that memory usage is database and application dependent, but usage
and workloads dependent too...
Loosely speaking, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding
and because MSDE does not admit it)..
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, linked server drivers, NetLib dlls, in process COM objects space
and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
more, each connection, live or sleeping, will eat about 24kb of memory,
calculated as 12 KB + 3 times the Network Packet Size (default setting that
can be partially customized via sp_configure system stored procedure
modifying the 'user connections' setting), used to store the data structures
holding the connection 's context, as long as for buffer used to send and
receive the relative associated network streams (default to 4KB network
packet setting), that can be stolen from the buffer pool memory region
and/or the MemToLeave memory area...
not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
in our example) for it's uses, and it will perhaps release it only under
pressure by the OS claiming for additional memory, if it's the case, else it
will maintain that memory in order to cache pages and execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releasing
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and staying there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resources
contentions for sure...
I strongly advice you for
http://www.windowsitpro.com/Article/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||i appreciate the information
thanx
"Andrea Montanari" wrote:

> hi,
> Keeper wrote:
> usually you should not manually "tune" SQL Server as it is tailored to self
> configure it self...
> on the other hand, SQL Server is very resources intensive, and is designed
> to take advantage of all the installed available memory (in sys32 processes
> limit )
> and it's a very challenging taks to manual configure it for best
> performance..
> remember that memory usage is database and application dependent, but usage
> and workloads dependent too...
> Loosely speaking, SQL Server organize it's memory allocation in two distinct
> regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
> regions. (I'm excluding use of AWE in order to provide easy understanding
> and because MSDE does not admit it)..
> so... the BPool is the primary region SQL Server uses for it's internal
> matter, while MemToLeave consists of the virtual memory space within the 1gb
> user mode address space and the memory not used by the BPool.
> when SQL Server starts, it begins calculating the upper limit the BPool can
> reach... if no MaxMemory is set, this value will be set to the amount of the
> physical memory or the size of the user mode address space (1gb) , minus the
> size of the MemToLeave, whichever is less..
> by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
> stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
> providers, linked server drivers, NetLib dlls, in process COM objects space
> and memory requirements and so on..
> when MaxMemory value is explicitally set, this upper limit will only address
> BPool region needs...
> so only the address space of BPool pages is limited by this configuration
> value, while SQL Server memory requirememts outside BPool allocation are not
> limited this way...
> more, each connection, live or sleeping, will eat about 24kb of memory,
> calculated as 12 KB + 3 times the Network Packet Size (default setting that
> can be partially customized via sp_configure system stored procedure
> modifying the 'user connections' setting), used to store the data structures
> holding the connection 's context, as long as for buffer used to send and
> receive the relative associated network streams (default to 4KB network
> packet setting), that can be stolen from the buffer pool memory region
> and/or the MemToLeave memory area...
> not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
> in our example) for it's uses, and it will perhaps release it only under
> pressure by the OS claiming for additional memory, if it's the case, else it
> will maintain that memory in order to cache pages and execution plans...
> under OS pressure, SQL Server will try having the Lazy Writer releasing
> resources, but you are not granted that this will occur, even if SQL Server
> will always try to leave some memory for other OS needs, usually between 4
> and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
> memory usage climbing to the top and staying there for long time, becouse of
> SQL Server tends to keep pages read from disk in memory to increase further
> and successive access to those pages, as long as query plans and so on... on
> a heavy loaded server, if you add web server duty, you will have resources
> contentions for sure...
> I strongly advice you for
> http://www.windowsitpro.com/Article/...908/37908.html and
> http://www.windowsitpro.com/Article/...890/37890.html
> another interesting readings:
> http://msdn.microsoft.com/library/de...v_03252004.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

msde memory leak?

Any problem with MSDE?
Client: ASP.Net C#
Server: MSDE
2 CPU/2GB RAM machine
at beginning, RAM for MSDE is 80MB
after 40 hours, RAM for MSDE is 1.3GB
Connections to msde is flat: 28 connections
1. It is said msde will keep grabbing ram, but is it too much?
2. try to set the limitation for max mem, but got error, says can not find
'max server mem'
sp_configure 'max server mem' 3000000
thanks,
Shelby
hi Shelby,
"shelby" <shelby@.discussions.microsoft.com> ha scritto nel messaggio
news:2537A25E-666F-4E46-9878-DB00B854AF66@.microsoft.com
> Any problem with MSDE?
> Client: ASP.Net C#
> Server: MSDE
> 2 CPU/2GB RAM machine
> at beginning, RAM for MSDE is 80MB
> after 40 hours, RAM for MSDE is 1.3GB
> Connections to msde is flat: 28 connections
> 1. It is said msde will keep grabbing ram, but is it too much?
> 2. try to set the limitation for max mem, but got error, says can not
> find 'max server mem'
> sp_configure 'max server mem' 3000000
try perhaps with
EXEC sp_configure 'max server memory' , xx
regarding your memory usage, it could be actually normal, as SQL Server
manage it's memory footprints very well, but it usually is very hungry about
it...
you can have a look at http://tinyurl.com/63bl3 , but a better disgression
about memory management is provided by Ken Henderson, and you can have a
loot at a sample chapter at
http://msdn.microsoft.com/library/de...v_03252004.asp
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

MSDE Memory Leak

My application attaches to, and detaches from many
databases. When I attach, the sqlservr process memory
usage goes up, and when I detach, it sometimes goes back
down, sometimes only partly, and sometimes actually goes
up. When my application exits, the sqlservr process never
releases the memory. Is this a bug? I'm using ADO15 and
I call Close() and Release() on all my connections and
recordsets. Is there something else I should be calling
to clean up the memory in MSDE?awolchinsky,
I don't think it's a bug. If it were, I'm sure it would have been fixed by
now via a service pack or whatever.
The mem usage behaviour you are seeing is not unexpected nor inappropriate
for an application server, which is essentially what MSDE is, being a
cut-down version of SQL Server. Typically, the server would grab as much
memory as is required to manage its workload for optimum performance,
subject to the limitations of the relevant operating system. What it
certainly *won't* do is release its allocated resources (memory) by default!
It wants to keep those resources so it's ready for next time... and no, it
doesn't care if you've got 20 other processes running on your box competing
for those resources - that's for the O/S to manage. This is why application
servers such as SQL Server are (usually) installed on a dedicated box......
You don't need to look at the memory allocation of the sqlserver.exe process
to determine whether your application (and your code) is satisfactory. As a
developer, you should assume that sqlserver.exe is fine and dandy and just
let it get on with doing whatever it's doing. You should concentrate on the
mem usage of your application.exe and any associated components that you've
coded/built etc .. if your app exits "cleanly" (ie your app.exe process
terminates and therefore releases all resources allocated to it) then you
have done your job.
--
--
Kind Regards,
Robert A. Ellis, MCSD
Software Developer
"awolchinsky" <awolchinsky@.bonders.com> wrote in message
news:04f401c35c6f$33c91fd0$a401280a@.phx.gbl...
> My application attaches to, and detaches from many
> databases. When I attach, the sqlservr process memory
> usage goes up, and when I detach, it sometimes goes back
> down, sometimes only partly, and sometimes actually goes
> up. When my application exits, the sqlservr process never
> releases the memory. Is this a bug? I'm using ADO15 and
> I call Close() and Release() on all my connections and
> recordsets. Is there something else I should be calling
> to clean up the memory in MSDE?|||Check the memoryleak FAQ entry and then post back if you think you still have
a leak.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Robert, thanks for the response. I agree now that it is
not a bug. And normally, I would only be concerned with
the memory usage of my app. But my app runs in the
background while our primary app executes. As sqlserver
eats up memory (200 MG), our main app, with through-put
requirements, slows down.
I did get some decent info from Microsoft. I set the 'max
server memory' to 20 MG, and that seems to hold the memory
usage down our 33 MG.
Thanks again.
Andy
>--Original Message--
>awolchinsky,
>I don't think it's a bug. If it were, I'm sure it would
have been fixed by
>now via a service pack or whatever.
>The mem usage behaviour you are seeing is not unexpected
nor inappropriate
>for an application server, which is essentially what MSDE
is, being a
>cut-down version of SQL Server. Typically, the server
would grab as much
>memory as is required to manage its workload for optimum
performance,
>subject to the limitations of the relevant operating
system. What it
>certainly *won't* do is release its allocated resources
(memory) by default!
>It wants to keep those resources so it's ready for next
time... and no, it
>doesn't care if you've got 20 other processes running on
your box competing
>for those resources - that's for the O/S to manage. This
is why application
>servers such as SQL Server are (usually) installed on a
dedicated box......
>You don't need to look at the memory allocation of the
sqlserver.exe process
>to determine whether your application (and your code) is
satisfactory. As a
>developer, you should assume that sqlserver.exe is fine
and dandy and just
>let it get on with doing whatever it's doing. You should
concentrate on the
>mem usage of your application.exe and any associated
components that you've
>coded/built etc .. if your app exits "cleanly" (ie your
app.exe process
>terminates and therefore releases all resources allocated
to it) then you
>have done your job.
>--
>--
>Kind Regards,
>Robert A. Ellis, MCSD
>Software Developer
>"awolchinsky" <awolchinsky@.bonders.com> wrote in message
>news:04f401c35c6f$33c91fd0$a401280a@.phx.gbl...
>> My application attaches to, and detaches from many
>> databases. When I attach, the sqlservr process memory
>> usage goes up, and when I detach, it sometimes goes back
>> down, sometimes only partly, and sometimes actually goes
>> up. When my application exits, the sqlservr process
never
>> releases the memory. Is this a bug? I'm using ADO15
and
>> I call Close() and Release() on all my connections and
>> recordsets. Is there something else I should be calling
>> to clean up the memory in MSDE?
>
>.
>

MSDE Memory Issue

I am curious if anybody has seen or had this proble before.
Starting today whenever I do anything to my local MSDE 2000 SP3 installmemory just gets eaten up on my machine until there is none left andthe machine starts crawling. I end up having to stop SQL Server andanything local that might have been using it to get control of mymachine back. As I speak I am looking at Task Manager on the the othermonitor and memory is just draining like there is no tomorrow. Andthere are no queries running right now. It happens when I try to runany SELECT, browse the local machine via Enterprise Manager, and alsowhen I have tried changing the width of a column. AFAIK it happens witheverything I have tried today. I am on SP3 so I am prettycurrent. I can't think of anything that has changed, and I am doingnothing except for some small time local Win32 development.
Any ideas would be appreciated. I'll keep looking around the Internet to see if I can find anything.
TIA
Hmmm, well you might check this KB article:INF: SQL Server Memory Usage. SQL Server will use whatever memory is available to it.