Memory Problem

Hi,
Yesterday I went to company of a friend to troubleshoot their SQL Server
2000.
The server has 2GB memory and no other app is using that (dedicated to SQL
Server). The problem is that after restarting the service, it takes about 4
days that users fill the memory by their queries and when 2GB runs out, the
server becomes so slow and even it takes long time to open the task manager.
If you change the maximum memory to 1.8GB, the OS itself will not be slow
when memory runs out, but the users experience serious problems in
performance as before. The database is not too big, only 700MB(data only)
but there are about 40 concurrent users.
I thought it is good to install more memory but what if this job only
increases the life of memory (e.g 8 days to run out!) instead of eliminating
the problem?!
Any help would be greatly appreciated.
LeilaI'd strat tuning queries first , I don;t think that OS or hardware are a
problem.
Run SQL Server Profiler to identify a long running queries and then try to
optimize them.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Yesterday I went to company of a friend to troubleshoot their SQL Server
> 2000.
> The server has 2GB memory and no other app is using that (dedicated to SQL
> Server). The problem is that after restarting the service, it takes about
> 4
> days that users fill the memory by their queries and when 2GB runs out,
> the
> server becomes so slow and even it takes long time to open the task
> manager.
> If you change the maximum memory to 1.8GB, the OS itself will not be slow
> when memory runs out, but the users experience serious problems in
> performance as before. The database is not too big, only 700MB(data only)
> but there are about 40 concurrent users.
> I thought it is good to install more memory but what if this job only
> increases the life of memory (e.g 8 days to run out!) instead of
> eliminating
> the problem?!
> Any help would be greatly appreciated.
> Leila
>|||Sounds like you have a memory leak somewhere.
Some ideas:
1) Do they have the latest Service Pack installed (SP4)?
2) Are there any third party extended procedures on the machine that might
cause trouble?
3) Are there any connections that are open for a long time with long term
locks?
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Yesterday I went to company of a friend to troubleshoot their SQL Server
> 2000.
> The server has 2GB memory and no other app is using that (dedicated to SQL
> Server). The problem is that after restarting the service, it takes about
> 4
> days that users fill the memory by their queries and when 2GB runs out,
> the
> server becomes so slow and even it takes long time to open the task
> manager.
> If you change the maximum memory to 1.8GB, the OS itself will not be slow
> when memory runs out, but the users experience serious problems in
> performance as before. The database is not too big, only 700MB(data only)
> but there are about 40 concurrent users.
> I thought it is good to install more memory but what if this job only
> increases the life of memory (e.g 8 days to run out!) instead of
> eliminating
> the problem?!
> Any help would be greatly appreciated.
> Leila
>|||If its SQL Standard, more memory will not help the SQL Server.
I find it odd that a 700MB database can take up 1.8 gb memory...
looking forward to input form others.
What version (service pack) of SQL?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Leila" <Leilas@.hotpop.com> wrote in message
news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Yesterday I went to company of a friend to troubleshoot their SQL Server
> 2000.
> The server has 2GB memory and no other app is using that (dedicated to SQL
> Server). The problem is that after restarting the service, it takes about
> 4
> days that users fill the memory by their queries and when 2GB runs out,
> the
> server becomes so slow and even it takes long time to open the task
> manager.
> If you change the maximum memory to 1.8GB, the OS itself will not be slow
> when memory runs out, but the users experience serious problems in
> performance as before. The database is not too big, only 700MB(data only)
> but there are about 40 concurrent users.
> I thought it is good to install more memory but what if this job only
> increases the life of memory (e.g 8 days to run out!) instead of
> eliminating
> the problem?!
> Any help would be greatly appreciated.
> Leila
>|||Leila,
1. Ensure the appropriate indexes exist to help minimize physcial I/O
2. Ensure the end-user ad-hoc queries (not really recommended) include
WHERE clauses (that match the indexes)
3. Check for table/clustered index scans for queries
4. Ensure that no CROSS JOINs (or multiple tables withouth WHERE clause -
old syntax) are not occuring
5. Perhaps think about using the Query Governor Cost Limit
HTH
Jerry
"Leila" <Leilas@.hotpop.com> wrote in message
news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Yesterday I went to company of a friend to troubleshoot their SQL Server
> 2000.
> The server has 2GB memory and no other app is using that (dedicated to SQL
> Server). The problem is that after restarting the service, it takes about
> 4
> days that users fill the memory by their queries and when 2GB runs out,
> the
> server becomes so slow and even it takes long time to open the task
> manager.
> If you change the maximum memory to 1.8GB, the OS itself will not be slow
> when memory runs out, but the users experience serious problems in
> performance as before. The database is not too big, only 700MB(data only)
> but there are about 40 concurrent users.
> I thought it is good to install more memory but what if this job only
> increases the life of memory (e.g 8 days to run out!) instead of
> eliminating
> the problem?!
> Any help would be greatly appreciated.
> Leila
>|||Thanks Uri,
There's no problem with queries. I read the result of profiler and sorted by
duration. There's no bottleneck with queries. Everything is ok when memory
is available. As soon as memory becomes full, the page/sec in performance
monitor goes high!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#sYDnSbzFHA.2064@.TK2MSFTNGP09.phx.gbl...
> I'd strat tuning queries first , I don;t think that OS or hardware are a
> problem.
> Run SQL Server Profiler to identify a long running queries and then try to
> optimize them.
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
SQL
about
slow
only)
>|||Thanks Jacco,
1) SP3
2) No
3) There is no problem with locks and blocking ( I used performance
monitor). The client app opens about 3 or 4 connections (maybe more!) but
there's another question for me: Can an inactive connection consume a lot of
memory? I mean can it be a serious problem for memory?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:#RlKqTbzFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Sounds like you have a memory leak somewhere.
> Some ideas:
> 1) Do they have the latest Service Pack installed (SP4)?
> 2) Are there any third party extended procedures on the machine that might
> cause trouble?
> 3) Are there any connections that are open for a long time with long term
> locks?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
SQL
about
slow
only)
>|||Thanks Kevin,
It's Enterprise Edition + SP3
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:e2u$MVbzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> If its SQL Standard, more memory will not help the SQL Server.
> I find it odd that a 700MB database can take up 1.8 gb memory...
> looking forward to input form others.
> What version (service pack) of SQL?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
SQL
about
slow
only)
>|||Leila wrote:
> Hi,
> Yesterday I went to company of a friend to troubleshoot their SQL
> Server 2000.
> The server has 2GB memory and no other app is using that (dedicated
> to SQL Server). The problem is that after restarting the service, it
> takes about 4 days that users fill the memory by their queries and
> when 2GB runs out, the server becomes so slow and even it takes long
> time to open the task manager. If you change the maximum memory to
> 1.8GB, the OS itself will not be slow when memory runs out, but the
> users experience serious problems in performance as before. The
> database is not too big, only 700MB(data only) but there are about 40
> concurrent users.
> I thought it is good to install more memory but what if this job only
> increases the life of memory (e.g 8 days to run out!) instead of
> eliminating the problem?!
> Any help would be greatly appreciated.
> Leila
I agree with Uri. Tuning queries is the place to start. Your data is
700MB, but you didn't mention the size of your indexes, the number of
query plans in cache, the type of execution model (stored procedures,
dynamic sql, etc.), or the design of the disk subsystem. See BOL "SQL
Server Memory Pool" for a description of memory use.
Keeping table / index / clustered index scans to a minimum can help
performance a great deal.
I would encourage you to also use Profiler before and after the memory
problem to better compare performance of your queries and see where the
differences are. Are you seeing a difference in duration only? If so, it
could be a overutilized disk subsystem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Jerry,
It doesn't seem to be I/O optimization problem. Whether the query is
optimized by indexes or not, at the end it will consume a particular amount
of memory to stay there.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e2l$EdbzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Leila,
> 1. Ensure the appropriate indexes exist to help minimize physcial I/O
> 2. Ensure the end-user ad-hoc queries (not really recommended) include
> WHERE clauses (that match the indexes)
> 3. Check for table/clustered index scans for queries
> 4. Ensure that no CROSS JOINs (or multiple tables withouth WHERE clause -
> old syntax) are not occuring
> 5. Perhaps think about using the Query Governor Cost Limit
> HTH
> Jerry
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OKrDJNbzFHA.3408@.TK2MSFTNGP09.phx.gbl...
SQL
about
slow
only)
>