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