MS SQL Server - SQL Server memory growth

Author

SQL Server memory growth

JohnnyMagz

2006-01-30, 8:23 pm

My company sells a client/server system written in VB that connects to a SQL
Server 2000 (std Ed) database. The software is deployed in 4 locations across
the country. Each customer runs their database server on the same physical
hardware as our application. The database is pretty small (about 100 tables
and a mdf file < 1GB), but appears to take more memory than it should. My
technique is not very scientific. Basically, I look inside of Task Manager at
startup time and then again each hour for some duration. Within a day's time,
SQL Server takes up to about 1GB of memory. What are some of the things I can
do (in order of easiest to hardest) to reduce the memory footprint?

Thanks so much for your assistance. I have found this group extremely useful
in helping me develop my SQL skills.

harvinder

2006-01-30, 8:23 pm

SQL Server is suppose to take all the memory except 5mb for OS if required
unless you set up the min server memory and max server memory parameters.
Do your other applications have bottlenexk on memory? If yes you can setup
the max server memory for SQL Server otherwise it is working as designed.

"JohnnyMagz" wrote:

> My company sells a client/server system written in VB that connects to a SQL
> Server 2000 (std Ed) database. The software is deployed in 4 locations across
> the country. Each customer runs their database server on the same physical
> hardware as our application. The database is pretty small (about 100 tables
> and a mdf file < 1GB), but appears to take more memory than it should. My
> technique is not very scientific. Basically, I look inside of Task Manager at
> startup time and then again each hour for some duration. Within a day's time,
> SQL Server takes up to about 1GB of memory. What are some of the things I can
> do (in order of easiest to hardest) to reduce the memory footprint?
>
> Thanks so much for your assistance. I have found this group extremely useful
> in helping me develop my SQL skills.

JohnnyMagz

2006-01-30, 8:23 pm

Really? I'd expect the application's memory utilization to ramp up to some
value and then level off. In this case, it continues to grow. Wouldn't that
be indicative of a memory leak? To answer your question, yes my applications
are leaking memory. We are addressing that, but I'm surprised that the sql
process itself is growing in memory usage. I am not doing anything to
backup/truncate the transaction log. Will doing that have an effect on memory
usage?
--
- Johnny

"harvinder" wrote:
[color=darkred]
> SQL Server is suppose to take all the memory except 5mb for OS if required
> unless you set up the min server memory and max server memory parameters.
> Do your other applications have bottlenexk on memory? If yes you can setup
> the max server memory for SQL Server otherwise it is working as designed.
>
>
> "JohnnyMagz" wrote:
>

Geoff N. Hiten

2006-01-30, 8:23 pm

This is well documented and expected behavior from SQL Server. Most SQL
Servers are on dedicated hosts, so the default is to tune them as such. SQL
doesn't "leak" memory, it allocates more cache. The more data SQL can
cache, the better the performance. SQL also caches query plans, so it can
allocate and use more memory than the physical database size. If you are on
a non-dedicated server, you can limit the amount of memory SQL will
allocate.

The following KB article explains SQL memory usage and how to adjust it:
http://support.microsoft.com/kb/321363/en-us

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"JohnnyMagz" < JohnnyMagz@discussio
ns.microsoft.com> wrote in message
news:0233F3F5-1301-44BD-873D- 1799FC54F9FA@microso
ft.com...[color=darkred]
> Really? I'd expect the application's memory utilization to ramp up to some
> value and then level off. In this case, it continues to grow. Wouldn't
> that
> be indicative of a memory leak? To answer your question, yes my
> applications
> are leaking memory. We are addressing that, but I'm surprised that the sql
> process itself is growing in memory usage. I am not doing anything to
> backup/truncate the transaction log. Will doing that have an effect on
> memory
> usage?
> --
> - Johnny
>
>
> "harvinder" wrote:
>

Sponsored Links