Wednesday, March 7, 2012

Managing memory usage of MSDE

I am getting ready to deploy a manufacturing application that uses a SQL2K
MSDE database. It is not a very large database but it gets updates every few
seconds, so it is very active.
When I first start the application, the total memory allocated to SQL (as
seen in task manager) is about 6M. this is before any transactions stat with
the application.
Once the transactions start, the memory usage grows consistantly. After
about 12hours of operation, the usage is over 600M (per Task Manager).
My question is, is this normal? Can it be controlled? If so, how? If not,
what options do I have to manage this?
It becomes a problem when the PC running the application must start caching
resources in order to maintain SQL performance.
Thanks in advance for your help...
hi rick,
"rick" <rick@.discussions.microsoft.com> ha scritto nel messaggio
news:E22FF67D-E9C5-4537-BC6B-DD18B697EF84@.microsoft.com
> I am getting ready to deploy a manufacturing application that uses a
> SQL2K MSDE database. It is not a very large database but it gets
> updates every few seconds, so it is very active.
> When I first start the application, the total memory allocated to SQL
> (as seen in task manager) is about 6M. this is before any
> transactions stat with the application.
> Once the transactions start, the memory usage grows consistantly.
> After about 12hours of operation, the usage is over 600M (per Task
> Manager).
> My question is, is this normal? Can it be controlled? If so, how? If
> not, what options do I have to manage this?
> It becomes a problem when the PC running the application must start
> caching resources in order to maintain SQL performance.
> Thanks in advance for your help...
please have a look at http://tinyurl.com/5qrjf about memory requirements,
and please keep in mind SQL Server works at best when it's running, alone,
on a database server, physically separated from an application server, print
server and so on..
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
|||"Andrea Montanari" wrote:

> hi rick,
> "rick" <rick@.discussions.microsoft.com> ha scritto nel messaggio
> news:E22FF67D-E9C5-4537-BC6B-DD18B697EF84@.microsoft.com
> please have a look at http://tinyurl.com/5qrjf about memory requirements,
> and please keep in mind SQL Server works at best when it's running, alone,
> on a database server, physically separated from an application server, print
> server and so on..
> --
> 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
>
Thanks for the reply, and I know that this is typically the case, but for
this application's requirements and the infrastructure that I have built,
keeping the MSDE on the same box as the application is critical. The MSDE is
only used for local storage as a buffer area for data replication to the
enterprise server and only when the Enterprise server requests the
information (based on network traffic, data popularity, and other metrics).
I found the stored procedure 'sp_configure' and the 'max server memory'
parameter and applied it according to the needs of the system. Efficiency may
be degraded slightly compared to a system that is stacked full of RAM, but
for the type of machine that this is running on, limitting the server memory
usage does not hurt performance as much as dik caching does.
Thanks for the reply though...

No comments:

Post a Comment