Wednesday, March 7, 2012
Managing memory usage of MSDE
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...
Saturday, February 25, 2012
Managing DB updates to client DB servers
pushed out to clients for product updates. Obviously, We could just
keep track of the changes on a pad or write a database that requires
us to input those changes and eventually hand write the update
scripts. I was wondering if anybody has any solutions that may help
automate this process.
Is there a way to write an application that will compare a current
(updated) database structure against the last realease that will give
us the fields that need to be changed?
As far as creating the scripts for the initial install, thats easy. We
can do that right from the SQL Enterprise Manager.
Call me lazy! Any ideas?
ThanksDuncan (duncan.loxton@.gmail.com) writes:
> I'm looking for ideas on how to write SQL scripts for updates that are
> pushed out to clients for product updates. Obviously, We could just
> keep track of the changes on a pad or write a database that requires
> us to input those changes and eventually hand write the update
> scripts. I was wondering if anybody has any solutions that may help
> automate this process.
> Is there a way to write an application that will compare a current
> (updated) database structure against the last realease that will give
> us the fields that need to be changed?
> As far as creating the scripts for the initial install, thats easy. We
> can do that right from the SQL Enterprise Manager.
There a couple of products on the market. SQLCompare from Red Gate does
indeed compare two databases. DBGhost likes to tout itself as being
good for this. I have not use any of them.
Whatever method, you should keep all your code under version control,
and all your update scripts should have their foundation in the
version-control system. Basically a shipment is all changes between
the label for the previous shipment and this one. With some files added,
like triggers or indexes for changed tables.
In fact, once you have a good version control system up and running,
composing your scripts manually is not daunting task - but admittedly
it becomes boring after a while. The flip side is that you learn to
understand your process.
In fact, I started in our shop with something like this many years
ago. This has now evolved to a versatile toolset that we use. It is
available as freeware for anyone who want to try, see
http://www.abaris.se/abaperls/index.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp