Wednesday, March 7, 2012

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...
> > 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 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...
> > 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 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...
> > 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 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...
> > 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,
Is the memory configuration for SQL Server set to be dynamic or static?
Have you played around with any advanced memory settings AWE etc...?
Any SQL Server third-party tools/utilities installed?
HTH
Jerry
"Leila" <Leilas@.hotpop.com> wrote in message
news:%2372Q3kbzFHA.3720@.TK2MSFTNGP14.phx.gbl...
> 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...
>> > 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
>> >
>> >
>>
>|||It's far fetched but you never know
are there any tables pinned in memory'
http://sqlservercode.blogspot.com/
"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
>
>|||Try windows system resource manager which
come free with win2k3
also watch out for long running querries and of course
intruders to your sql server.
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"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
>
>|||how about defective hardisk
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"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
>
>|||A connection in itself uses 40K of server memory even when it is inactive.
Although that means that it is good practice to close a connection whenever
it is not needed anymore by the application, the impact is not that major
that it would cause the memory problems you describe. However, when you have
active connections that for example keep cursors open and never close them,
that can cause serious memory problems. You can have a look at the
sysprocesses table to look for anything suspicious. You should be able to
see issues quite clearly (if it is a problem with connections/processes) 1
or 2 days after the server is restarted in your scenario.
Things to look for:
- excessive number of processes
- processes with excessive memory consumption (memusage column)
SELECT * FROM sysprocesses
WHERE spid > 50 -- user processes only, no system processes
--
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:uKn00ebzFHA.3588@.tk2msftngp13.phx.gbl...
> 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...
>> > 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
>> >
>> >
>>
>|||No pinned table!
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:64F691CC-A0FC-43B6-96F7-C1DE925D2E3E@.microsoft.com...
> It's far fetched but you never know
> are there any tables pinned in memory'
> http://sqlservercode.blogspot.com/
> "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
> >
> >
> >|||Jerry,
There is no third party. As far as I know, AWE must be configured for memory
upper than 3GB?!
I'm using dynamic management, I mean I did not fix the memory to a
particular amount. I only tried specifying the maximum memory (to 1.8GB)
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u5TtgobzFHA.904@.tk2msftngp13.phx.gbl...
> Leila,
> Is the memory configuration for SQL Server set to be dynamic or static?
> Have you played around with any advanced memory settings AWE etc...?
> Any SQL Server third-party tools/utilities installed?
> HTH
> Jerry
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%2372Q3kbzFHA.3720@.TK2MSFTNGP14.phx.gbl...
> > 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...
> >> > 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 David,
I believe inappropriate indexes can put the disks under pressure but finally
the result will consume the same amount of memory as a query with optimized
indexes. There is no problem when the memory is available, but only when the
memory runs out!
Do you mean inexistence of appropriate indexes can fill the memory faster?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:#c2jhkbzFHA.1032@.TK2MSFTNGP12.phx.gbl...
> 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
>|||I tried SP_WHO2, does it have the same result?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uE1JKzbzFHA.2424@.TK2MSFTNGP12.phx.gbl...
> A connection in itself uses 40K of server memory even when it is inactive.
> Although that means that it is good practice to close a connection
whenever
> it is not needed anymore by the application, the impact is not that major
> that it would cause the memory problems you describe. However, when you
have
> active connections that for example keep cursors open and never close
them,
> that can cause serious memory problems. You can have a look at the
> sysprocesses table to look for anything suspicious. You should be able to
> see issues quite clearly (if it is a problem with connections/processes) 1
> or 2 days after the server is restarted in your scenario.
> Things to look for:
> - excessive number of processes
> - processes with excessive memory consumption (memusage column)
> SELECT * FROM sysprocesses
> WHERE spid > 50 -- user processes only, no system processes
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uKn00ebzFHA.3588@.tk2msftngp13.phx.gbl...
> > 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...
> >> > 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 Jose,
Everything is fine when memory is available. When it runs out, the disks
become busy because of page/sec
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> how about defective hardisk
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "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
> >
> >
> >|||Leila,
What process is page faulting? I assume it is SQL Server, if so, what
process has taken all the memory? It is SQL Server also? I know that you
said that it is the disk io that is causing the problem due to page faults,
but maybe another process has the memory tied up, hence the excessive page
faults.
Additionally, if it is SQL Server taking the memory, maybe you can flush
cache to release the memory it is holding to get a better idea of what is
taking place. If cache gets flushed does the page faulting stop and the
systems speed back up?
Just a thought.
"Leila" wrote:
> Thanks Jose,
> Everything is fine when memory is available. When it runs out, the disks
> become busy because of page/sec
> <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> > how about defective hardisk
> >
> > --
> > thanks,
> >
> > --
> > Jose de Jesus Jr. Mcp,Mcdba
> > Data Architect
> > Sykes Asia (Manila philippines)
> > MCP #2324787
> >
> >
> > "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
> > >
> > >
> > >
>
>|||Leila wrote:
> Thanks David,
> I believe inappropriate indexes can put the disks under pressure but
> finally the result will consume the same amount of memory as a query
> with optimized indexes. There is no problem when the memory is
> available, but only when the memory runs out!
> Do you mean inexistence of appropriate indexes can fill the memory
> faster?
Yes. Most of your memory is consumed reading data / index pages into
memory. If you scan a 50MB table, then you put 50MB into memory and
likely force out pages that should be there.
So let's say, for example, your hard drive subsystem is not up to task
and they are having a hard time with multiple physical disk requests.
You can verify this by looking at the Avg. Disk Queue Length which
should be less than 2 most of the time. Let's also assume you have some
inefficient queries that run on a regular basis that access large
numbers of pages. Over the course of operation SQL Server reads pages
from memory and stores those results in the buffer cache. As more
information is read, SQL Server grabs memory from the available memory
pool. SQL Server does not normally return this memory to the OS. This is
by design. Those queries that are running that are optimized can take
advantage of data in the buffer cache and avoid hitting the disks.
Eventually, there will be no more memory to give to SQL Server and
requests for data that are not in the buffer cache must go to disk. Not
to mention much of the buffered data does no good since it was put there
from scan type operations. Since the disks are not up to snuff, this
causes the queue length to increase, which means a lot of disk head
movement. Even queries that are optimized are affected because they are
waiting in the queue for their turn to get to the disks. SQL Server has
to start dumping information from the buffer cache in favor of this new
information. Everything starts to slow down.
Now, if all the queries are optimzed, meaning, they all access a reduced
number of pages to generate their results, then it's much less likely
that data will reside in the buffer cache that does not need to be
there. On that same 50MB table, maybe 64K is read into cache instead of
50MB. The disk heads are free to perform less stressful physical reads,
less memory is consumed, and everything runs right as rain.
If you examine some disk counters including Avg Disk Queue Length and
Avg Disk Bytes Transfer (which is appears from another post you already
have in some detail) you should see some changes before and after. From
Profiler, you should also see some changes, probably in Duration of
queries. Duration can increase because of an increase in CPU or other
factors like hard drive issues. Also have a look at the overall system
CPU and see if that changes when the problem occurs. If so, what
process/service is using the CPU?
It could also be that the OS is getting memory starved. If you're
concerned about this designate a maximum memory for SQL Server (as I
believe you already have) and then monitor the performance to see where
the problem lies.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Am Mon, 10 Oct 2005 21:04:29 +0330 schrieb Leila:
> Jerry,
> There is no third party. As far as I know, AWE must be configured for memory
> upper than 3GB?!
> I'm using dynamic management, I mean I did not fix the memory to a
> particular amount. I only tried specifying the maximum memory (to 1.8GB)
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:u5TtgobzFHA.904@.tk2msftngp13.phx.gbl...
>> Leila,
>> Is the memory configuration for SQL Server set to be dynamic or static?
>> Have you played around with any advanced memory settings AWE etc...?
>> Any SQL Server third-party tools/utilities installed?
>> HTH
>> Jerry
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%2372Q3kbzFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> 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...
>> > 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
>> >
>> >
>>
>>
>>
Hi Leila,
i heard sometimes that such problems disappeared when fixed memory was used
instead of dynamic memory. Maybe the memory manager itself comes into
trouble if he has to manage so much of dynamic objects?
I don't know, but maybe you can give it a try changing from dynamic
management to 1 Gb fixed memory (my suggestion).
bye,
Helmut|||Actually I monitored Avg. Disk Queue Length and the situation was good (RAID
5 is being used). But sometimes in heavy queries, the context switch reaches
10,000!
I must check the indexes!
BTW, there was a join that produced about 2,000,000 rows and consumed about
250MB of memory. But other apps that needed that query could reuse the data
from cache without increasing the taken memory.
I suggested the developers to use some filters while the users open a
particular form in app instead of loading this amount of records into
memory, or use paging to retrieve 2000 rows in each page. Can it help?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uIiBwLdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Leila wrote:
> > Thanks David,
> > I believe inappropriate indexes can put the disks under pressure but
> > finally the result will consume the same amount of memory as a query
> > with optimized indexes. There is no problem when the memory is
> > available, but only when the memory runs out!
> > Do you mean inexistence of appropriate indexes can fill the memory
> > faster?
> Yes. Most of your memory is consumed reading data / index pages into
> memory. If you scan a 50MB table, then you put 50MB into memory and
> likely force out pages that should be there.
> So let's say, for example, your hard drive subsystem is not up to task
> and they are having a hard time with multiple physical disk requests.
> You can verify this by looking at the Avg. Disk Queue Length which
> should be less than 2 most of the time. Let's also assume you have some
> inefficient queries that run on a regular basis that access large
> numbers of pages. Over the course of operation SQL Server reads pages
> from memory and stores those results in the buffer cache. As more
> information is read, SQL Server grabs memory from the available memory
> pool. SQL Server does not normally return this memory to the OS. This is
> by design. Those queries that are running that are optimized can take
> advantage of data in the buffer cache and avoid hitting the disks.
> Eventually, there will be no more memory to give to SQL Server and
> requests for data that are not in the buffer cache must go to disk. Not
> to mention much of the buffered data does no good since it was put there
> from scan type operations. Since the disks are not up to snuff, this
> causes the queue length to increase, which means a lot of disk head
> movement. Even queries that are optimized are affected because they are
> waiting in the queue for their turn to get to the disks. SQL Server has
> to start dumping information from the buffer cache in favor of this new
> information. Everything starts to slow down.
> Now, if all the queries are optimzed, meaning, they all access a reduced
> number of pages to generate their results, then it's much less likely
> that data will reside in the buffer cache that does not need to be
> there. On that same 50MB table, maybe 64K is read into cache instead of
> 50MB. The disk heads are free to perform less stressful physical reads,
> less memory is consumed, and everything runs right as rain.
> If you examine some disk counters including Avg Disk Queue Length and
> Avg Disk Bytes Transfer (which is appears from another post you already
> have in some detail) you should see some changes before and after. From
> Profiler, you should also see some changes, probably in Duration of
> queries. Duration can increase because of an increase in CPU or other
> factors like hard drive issues. Also have a look at the overall system
> CPU and see if that changes when the problem occurs. If so, what
> process/service is using the CPU?
> It could also be that the OS is getting memory starved. If you're
> concerned about this designate a maximum memory for SQL Server (as I
> believe you already have) and then monitor the performance to see where
> the problem lies.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Thanks,
What do you mean by page faulting? What is it?
Do you mean I use DBCC DropCleanBuffers to flush the memory?
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:791FCD37-1583-438A-B78B-91F927A7B4AF@.microsoft.com...
> Leila,
> What process is page faulting? I assume it is SQL Server, if so, what
> process has taken all the memory? It is SQL Server also? I know that you
> said that it is the disk io that is causing the problem due to page
faults,
> but maybe another process has the memory tied up, hence the excessive page
> faults.
> Additionally, if it is SQL Server taking the memory, maybe you can flush
> cache to release the memory it is holding to get a better idea of what is
> taking place. If cache gets flushed does the page faulting stop and the
> systems speed back up?
> Just a thought.
> "Leila" wrote:
> > Thanks Jose,
> > Everything is fine when memory is available. When it runs out, the disks
> > become busy because of page/sec
> >
> > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> > news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> > > how about defective hardisk
> > >
> > > --
> > > thanks,
> > >
> > > --
> > > Jose de Jesus Jr. Mcp,Mcdba
> > > Data Architect
> > > Sykes Asia (Manila philippines)
> > > MCP #2324787
> > >
> > >
> > > "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
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Leila,
I read the discussion and i focused on ur problem that after a duration
the problem starts.DO u have any scheduled job or Database maintinance
plan doing rebuilding of indexes on tables.
U can use DBCC Drop CleanBuffer to flush the memory.
--Or try out this to see really what is happening--
--*******************All Locks***********************
select b.spid 'Holding ID', rtrim(b.status) 'Status', 'Lock Type' =case a.rsc_type when 1 then NULL when 2 then 'DATABASE' when 3 then
'FILE' when 4 then 'INDEX' when 5 then 'TABLE' when 6 then 'PAGE' when
7 then 'KEY' when 8 then 'EXTENT' when 9 then 'RID' when 10 then
'APPLICATION' else NULL end, SUSER_SNAME(b.sid) 'Holding
User',SUSER_SNAME(w.sid) 'Waiting User',w.spid 'Waiting ID','Database'=CASE WHEN a.rsc_dbid=0 THEN '[NULL]' ELSE DB_NAME(a.rsc_dbid) END,
a.rsc_objid 'Object ID',rtrim(b.hostname) 'Holding
Host',rtrim(w.hostname) 'Waiting Host', rtrim(b.program_name) 'Holding
Program',rtrim(w.program_name) 'Waiting Program', b.cmd 'Holding
Command', w.cmd 'Waiting Command', b.cpu 'CPU Time', b.physical_io
'I/O', b.memusage 'Mem Usage' from master.dbo.syslockinfo
a,master.dbo.sysprocesses b,master.dbo.sysprocesses w where
a.req_spid=w.blocked AND b.spid=w.blocked AND a.req_spid = b.spid and
b.spid <> @.@.spid ORDER BY 1,5,6,3
--Process in SPID--
DBCC INPUTBUFFER(65)
--**************System I/O Summary--****************
SELECT rtrim(a.counter_name) 'Counter Name', a.cntr_value 'Counter
Value' FROM master..sysperfinfo a
WHERE a.object_name LIKE '%Buffer Manager%' AND
a.counter_name in ('Page lookups/sec','Lazy writes/sec',
'Readahead pages/sec',
'Page reads/sec', 'Page writes/sec', 'Checkpoint
pages/sec',
'AWE lookup maps/sec', 'AWE stolen maps/sec', 'AWE write
maps/sec',
'AWE unmap calls/sec', 'AWE unmap pages/sec') union all
SELECT rtrim(b.counter_name) 'Counter Name', b.cntr_value FROM
master..sysperfinfo b
WHERE b.object_name LIKE '%Access methods%' AND
b.counter_name in
('FreeSpace Scans/sec', 'FreeSpace
Page Fetches/sec', 'Pages Allocated/sec',
'Extents Allocated/sec', 'Mixed page
allocations/sec', 'Extent Deallocations/sec',
'Page Deallocations/sec', 'Page Splits/sec') ORDER
BY 1
--***************Database I/O Summary*********************
SELECT rtrim(a.instance_name) 'DB', 'DBCC Logical Scans' =a.cntr_value, 'Bulk Copy Rows' = b.cntr_value, 'Bulk Copy Throughput' =c.cntr_value, 'Backup/Restore Throughput' =d.cntr_value, 'Log Cache Reads' = e.cntr_value, 'Log Flushes' =f.cntr_value, 'Log Growths' =g.cntr_value, 'Log Shrinks' = h.cntr_value, 'Log Truncations' =i.cntr_value FROM master..sysperfinfo a,
master..sysperfinfo b, master..sysperfinfo c, master..sysperfinfo d,
master..sysperfinfo e, master..sysperfinfo f,
master..sysperfinfo g, master..sysperfinfo h, master..sysperfinfo i
WHERE a.object_name LIKE '%Databases%' AND
b.object_name = a.object_name AND c.object_name = a.object_name
AND d.object_name = a.object_name AND
e.object_name = a.object_name AND f.object_name = a.object_name
AND g.object_name = a.object_name AND
h.object_name = a.object_name AND i.object_name = a.object_name
AND b.instance_name = a.instance_name AND
c.instance_name = a.instance_name AND d.instance_name = a.instance_name
AND e.instance_name = a.instance_name AND
f.instance_name = a.instance_name AND g.instance_name = a.instance_name
AND h.instance_name = a.instance_name AND
i.instance_name = a.instance_name AND
a.counter_name = 'DBCC Logical Scan Bytes/sec' AND b.counter_name ='Bulk Copy Rows/sec' AND c.counter_name ='Bulk Copy Throughput/sec' AND d.counter_name = 'Backup/Restore
Throughput/sec' AND e.counter_name = 'Log
Cache Reads/sec' AND f.counter_name = 'Log Flushes/sec' AND
g.counter_name = 'Log Growths' AND
h.counter_name = 'Log Shrinks' AND i.counter_name = 'Log Truncations'
AND a.instance_name <> '_Total'ORDER BY 1
--**************USer I/O session--
SELECT rtrim(a.counter_name) 'User I/O Operations', 'Value' =a.cntr_value FROM master..sysperfinfo a
WHERE a.object_name LIKE '%Access methods%' AND a.counter_name in
('Full Scans/sec','Range Scans/sec', 'Probe
Scans/sec', 'Scan Point Revalidations/sec',
'Workfiles Created/sec', 'Worktables Created/sec', 'Forwarded
Records/sec', 'Skipped Ghosted
Records/sec', 'Index Searches/sec', 'Table Lock Escalations/sec') union
all SELECT rtrim(b.counter_name) 'User I/O Operations',
b.cntr_value FROM master..sysperfinfo b
WHERE b.object_name LIKE '%SQL Statistics%' AND b.counter_name in
('Batch Requests/sec', 'Auto-Param
Attempts/sec', 'Failed Auto-Params/sec', 'Safe Auto-Params/sec',
'Unsafe Auto-Params/sec', 'SQL
Compilations/sec', 'SQL Re-Compilations/sec') ORDER BY 1
--*************WAits--
DBCC sqlperf(WAITSTATS) WITH NO_INFOMSGS
--**************TOP SQL Cache***********************
select USER_NAME() select usertype,type,name from systypes where
usertype>=257
DECLARE @.db sysname CREATE TABLE ##syscacheobjects
( objtype nvarchar(17) NOT NULL,cachetype nvarchar(34) NOT NULL,objid
int NOT NULL,dbid smallint NOT NULL,
userid smallint NOT NULL,usecounts int NOT NULL,sqlbytes int NOT
NULL,pagesused int NULL,
sql nvarchar(3568) NULL, DBName sysname NULL,ObjectName sysname
NULL, UserName sysname NULL
)BEGIN INSERT ##syscacheobjects (objtype, cachetype, objid, dbid,
userid, usecounts, sqlbytes, pagesused,
sql, DBName ) SELECT TOP 100 objtype, cacheobjtype, objid, dbid, uid,
usecounts, sqlbytes,
pagesused, sql, db_name(dbid) FROM master..syscacheobjects
ORDER BY 6 DESC END DECLARE DBCursor CURSOR FOR SELECT distinct DBName
FROM ##syscacheobjects OPEN DBCursor
DECLARE @.quotedDB nvarchar(130) FETCH NEXT FROM DBCursor INTO @.db
WHILE(@.@.fetch_status <> -1)
BEGIN SET @.quotedDB = QUOTENAME(@.db, '''')
EXEC('UPDATE ##syscacheobjects SET
ObjectName = name FROM [' + @.db + ']..sysobjects where id = objid and
DBName = ' + @.quotedDB)
EXEC('UPDATE ##syscacheobjects SET UserName = name FROM [' + @.db +
']..sysusers where userid = uid
and DBName = ' + @.quotedDB) FETCH NEXT FROM DBCursor INTO @.db END
DEALLOCATE DBCursor
SELECT objtype, cachetype, objid, ObjectName, dbid, DBName, userid,
UserName, usecounts,
sqlbytes, kb_used = pagesused * 8, sql FROM ##syscacheobjects ORDER BY
9 DESC
DROP TABLE ##syscacheobjects
SELECT c.value FROM master.dbo.spt_values, master.dbo.sysconfigures c
WHERE type = 'C' AND number = c.config AND name = 'user options'
There may be issue that ur logfile increases too much so select
thisoption.
--**************Truncate log on chkPT****************
USE master
go
EXEC sp_dboption 'model','trunc. log on chkpt.',true
go
USE model
go
CHECKPOINT
go
--or use all these options
for fast working
DBCC SQLPERF (LOGSPACE)
DBCC SQLPERF (UMSSTATS)
DBCC SQLPERF (WAITSTATS)
DBCC SQLPERF (IOSTATS)
DBCC SQLPERF (RASTATS)
DBCC SQLPERF (THREADS)
DBCC SQLPERF (SPINLOCKSTATS)
DBCC SQLPERF (UMSSPINSTATS)
DBCC SQLPERF (NETSTATS)
DBCC SQLPERF (LRUSTATS)
Hope u will find the memory eater and kill it
from
Doller.
Leila wrote:
> Thanks,
> What do you mean by page faulting? What is it?
> Do you mean I use DBCC DropCleanBuffers to flush the memory?
>
>
> "NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
> news:791FCD37-1583-438A-B78B-91F927A7B4AF@.microsoft.com...
> > Leila,
> >
> > What process is page faulting? I assume it is SQL Server, if so, what
> > process has taken all the memory? It is SQL Server also? I know that you
> > said that it is the disk io that is causing the problem due to page
> faults,
> > but maybe another process has the memory tied up, hence the excessive page
> > faults.
> >
> > Additionally, if it is SQL Server taking the memory, maybe you can flush
> > cache to release the memory it is holding to get a better idea of what is
> > taking place. If cache gets flushed does the page faulting stop and the
> > systems speed back up?
> >
> > Just a thought.
> >
> > "Leila" wrote:
> >
> > > Thanks Jose,
> > > Everything is fine when memory is available. When it runs out, the disks
> > > become busy because of page/sec
> > >
> > > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> > > news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> > > > how about defective hardisk
> > > >
> > > > --
> > > > thanks,
> > > >
> > > > --
> > > > Jose de Jesus Jr. Mcp,Mcdba
> > > > Data Architect
> > > > Sykes Asia (Manila philippines)
> > > > MCP #2324787
> > > >
> > > >
> > > > "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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||sp_who2 is quite similar to SELECT * FROM sysprocesses, but doesn't include
the memusage column, which I think might contain the most useful information
for troubleshooting this problem.
--
Jacco Schalkwijk
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:ugCOMKczFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I tried SP_WHO2, does it have the same result?
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:uE1JKzbzFHA.2424@.TK2MSFTNGP12.phx.gbl...
>> A connection in itself uses 40K of server memory even when it is
>> inactive.
>> Although that means that it is good practice to close a connection
> whenever
>> it is not needed anymore by the application, the impact is not that major
>> that it would cause the memory problems you describe. However, when you
> have
>> active connections that for example keep cursors open and never close
> them,
>> that can cause serious memory problems. You can have a look at the
>> sysprocesses table to look for anything suspicious. You should be able to
>> see issues quite clearly (if it is a problem with connections/processes)
>> 1
>> or 2 days after the server is restarted in your scenario.
>> Things to look for:
>> - excessive number of processes
>> - processes with excessive memory consumption (memusage column)
>> SELECT * FROM sysprocesses
>> WHERE spid > 50 -- user processes only, no system processes
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:uKn00ebzFHA.3588@.tk2msftngp13.phx.gbl...
>> > 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...
>> >> > 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 indeed!
There was fragmentations in their db and one of the big tables had %44
logical fragmentation that I fixed it. I don't think if there's any job for
that.
I'll try your scripts!
Thanks again...
"doller" <sufianarif@.gmail.com> wrote in message
news:1129018878.821746.257680@.z14g2000cwz.googlegroups.com...
> Hi Leila,
> I read the discussion and i focused on ur problem that after a duration
> the problem starts.DO u have any scheduled job or Database maintinance
> plan doing rebuilding of indexes on tables.
> U can use DBCC Drop CleanBuffer to flush the memory.
> --Or try out this to see really what is happening--
> --*******************All Locks***********************
> select b.spid 'Holding ID', rtrim(b.status) 'Status', 'Lock Type' => case a.rsc_type when 1 then NULL when 2 then 'DATABASE' when 3 then
> 'FILE' when 4 then 'INDEX' when 5 then 'TABLE' when 6 then 'PAGE' when
> 7 then 'KEY' when 8 then 'EXTENT' when 9 then 'RID' when 10 then
> 'APPLICATION' else NULL end, SUSER_SNAME(b.sid) 'Holding
> User',SUSER_SNAME(w.sid) 'Waiting User',w.spid 'Waiting ID','Database'=> CASE WHEN a.rsc_dbid=0 THEN '[NULL]' ELSE DB_NAME(a.rsc_dbid) END,
> a.rsc_objid 'Object ID',rtrim(b.hostname) 'Holding
> Host',rtrim(w.hostname) 'Waiting Host', rtrim(b.program_name) 'Holding
> Program',rtrim(w.program_name) 'Waiting Program', b.cmd 'Holding
> Command', w.cmd 'Waiting Command', b.cpu 'CPU Time', b.physical_io
> 'I/O', b.memusage 'Mem Usage' from master.dbo.syslockinfo
> a,master.dbo.sysprocesses b,master.dbo.sysprocesses w where
> a.req_spid=w.blocked AND b.spid=w.blocked AND a.req_spid = b.spid and
> b.spid <> @.@.spid ORDER BY 1,5,6,3
> --Process in SPID--
> DBCC INPUTBUFFER(65)
> --**************System I/O Summary--****************
> SELECT rtrim(a.counter_name) 'Counter Name', a.cntr_value 'Counter
> Value' FROM master..sysperfinfo a
> WHERE a.object_name LIKE '%Buffer Manager%' AND
> a.counter_name in ('Page lookups/sec','Lazy writes/sec',
> 'Readahead pages/sec',
> 'Page reads/sec', 'Page writes/sec', 'Checkpoint
> pages/sec',
> 'AWE lookup maps/sec', 'AWE stolen maps/sec', 'AWE write
> maps/sec',
> 'AWE unmap calls/sec', 'AWE unmap pages/sec') union all
> SELECT rtrim(b.counter_name) 'Counter Name', b.cntr_value FROM
> master..sysperfinfo b
> WHERE b.object_name LIKE '%Access methods%' AND
> b.counter_name in
> ('FreeSpace Scans/sec', 'FreeSpace
> Page Fetches/sec', 'Pages Allocated/sec',
> 'Extents Allocated/sec', 'Mixed page
> allocations/sec', 'Extent Deallocations/sec',
> 'Page Deallocations/sec', 'Page Splits/sec') ORDER
> BY 1
> --***************Database I/O Summary*********************
> SELECT rtrim(a.instance_name) 'DB', 'DBCC Logical Scans' => a.cntr_value, 'Bulk Copy Rows' = b.cntr_value, 'Bulk Copy Throughput' => c.cntr_value, 'Backup/Restore Throughput' => d.cntr_value, 'Log Cache Reads' = e.cntr_value, 'Log Flushes' => f.cntr_value, 'Log Growths' => g.cntr_value, 'Log Shrinks' = h.cntr_value, 'Log Truncations' => i.cntr_value FROM master..sysperfinfo a,
> master..sysperfinfo b, master..sysperfinfo c, master..sysperfinfo d,
> master..sysperfinfo e, master..sysperfinfo f,
> master..sysperfinfo g, master..sysperfinfo h, master..sysperfinfo i
> WHERE a.object_name LIKE '%Databases%' AND
> b.object_name = a.object_name AND c.object_name = a.object_name
> AND d.object_name = a.object_name AND
> e.object_name = a.object_name AND f.object_name = a.object_name
> AND g.object_name = a.object_name AND
> h.object_name = a.object_name AND i.object_name = a.object_name
> AND b.instance_name = a.instance_name AND
> c.instance_name = a.instance_name AND d.instance_name = a.instance_name
> AND e.instance_name = a.instance_name AND
> f.instance_name = a.instance_name AND g.instance_name = a.instance_name
> AND h.instance_name = a.instance_name AND
> i.instance_name = a.instance_name AND
> a.counter_name = 'DBCC Logical Scan Bytes/sec' AND b.counter_name => 'Bulk Copy Rows/sec' AND c.counter_name => 'Bulk Copy Throughput/sec' AND d.counter_name = 'Backup/Restore
> Throughput/sec' AND e.counter_name = 'Log
> Cache Reads/sec' AND f.counter_name = 'Log Flushes/sec' AND
> g.counter_name = 'Log Growths' AND
> h.counter_name = 'Log Shrinks' AND i.counter_name = 'Log Truncations'
> AND a.instance_name <> '_Total'ORDER BY 1
> --**************USer I/O session--
> SELECT rtrim(a.counter_name) 'User I/O Operations', 'Value' => a.cntr_value FROM master..sysperfinfo a
> WHERE a.object_name LIKE '%Access methods%' AND a.counter_name in
> ('Full Scans/sec','Range Scans/sec', 'Probe
> Scans/sec', 'Scan Point Revalidations/sec',
> 'Workfiles Created/sec', 'Worktables Created/sec', 'Forwarded
> Records/sec', 'Skipped Ghosted
> Records/sec', 'Index Searches/sec', 'Table Lock Escalations/sec') union
> all SELECT rtrim(b.counter_name) 'User I/O Operations',
> b.cntr_value FROM master..sysperfinfo b
> WHERE b.object_name LIKE '%SQL Statistics%' AND b.counter_name in
> ('Batch Requests/sec', 'Auto-Param
> Attempts/sec', 'Failed Auto-Params/sec', 'Safe Auto-Params/sec',
> 'Unsafe Auto-Params/sec', 'SQL
> Compilations/sec', 'SQL Re-Compilations/sec') ORDER BY 1
> --*************WAits--
> DBCC sqlperf(WAITSTATS) WITH NO_INFOMSGS
> --**************TOP SQL Cache***********************
> select USER_NAME() select usertype,type,name from systypes where
> usertype>=257
> DECLARE @.db sysname CREATE TABLE ##syscacheobjects
> ( objtype nvarchar(17) NOT NULL,cachetype nvarchar(34) NOT NULL,objid
> int NOT NULL,dbid smallint NOT NULL,
> userid smallint NOT NULL,usecounts int NOT NULL,sqlbytes int NOT
> NULL,pagesused int NULL,
> sql nvarchar(3568) NULL, DBName sysname NULL,ObjectName sysname
> NULL, UserName sysname NULL
> )BEGIN INSERT ##syscacheobjects (objtype, cachetype, objid, dbid,
> userid, usecounts, sqlbytes, pagesused,
> sql, DBName ) SELECT TOP 100 objtype, cacheobjtype, objid, dbid, uid,
> usecounts, sqlbytes,
> pagesused, sql, db_name(dbid) FROM master..syscacheobjects
> ORDER BY 6 DESC END DECLARE DBCursor CURSOR FOR SELECT distinct DBName
> FROM ##syscacheobjects OPEN DBCursor
> DECLARE @.quotedDB nvarchar(130) FETCH NEXT FROM DBCursor INTO @.db
> WHILE(@.@.fetch_status <> -1)
> BEGIN SET @.quotedDB = QUOTENAME(@.db, '''')
> EXEC('UPDATE ##syscacheobjects SET
> ObjectName = name FROM [' + @.db + ']..sysobjects where id = objid and
> DBName = ' + @.quotedDB)
> EXEC('UPDATE ##syscacheobjects SET UserName = name FROM [' + @.db +
> ']..sysusers where userid = uid
> and DBName = ' + @.quotedDB) FETCH NEXT FROM DBCursor INTO @.db END
> DEALLOCATE DBCursor
> SELECT objtype, cachetype, objid, ObjectName, dbid, DBName, userid,
> UserName, usecounts,
> sqlbytes, kb_used = pagesused * 8, sql FROM ##syscacheobjects ORDER BY
> 9 DESC
> DROP TABLE ##syscacheobjects
> SELECT c.value FROM master.dbo.spt_values, master.dbo.sysconfigures c
> WHERE type = 'C' AND number = c.config AND name = 'user options'
> There may be issue that ur logfile increases too much so select
> thisoption.
> --**************Truncate log on chkPT****************
> USE master
> go
> EXEC sp_dboption 'model','trunc. log on chkpt.',true
> go
> USE model
> go
> CHECKPOINT
> go
> --or use all these options
> for fast working
> DBCC SQLPERF (LOGSPACE)
> DBCC SQLPERF (UMSSTATS)
> DBCC SQLPERF (WAITSTATS)
> DBCC SQLPERF (IOSTATS)
> DBCC SQLPERF (RASTATS)
> DBCC SQLPERF (THREADS)
> DBCC SQLPERF (SPINLOCKSTATS)
> DBCC SQLPERF (UMSSPINSTATS)
> DBCC SQLPERF (NETSTATS)
> DBCC SQLPERF (LRUSTATS)
>
> Hope u will find the memory eater and kill it
> from
> Doller.
>
>
>
> Leila wrote:
> > Thanks,
> > What do you mean by page faulting? What is it?
> > Do you mean I use DBCC DropCleanBuffers to flush the memory?
> >
> >
> >
> >
> > "NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
> > news:791FCD37-1583-438A-B78B-91F927A7B4AF@.microsoft.com...
> > > Leila,
> > >
> > > What process is page faulting? I assume it is SQL Server, if so, what
> > > process has taken all the memory? It is SQL Server also? I know that
you
> > > said that it is the disk io that is causing the problem due to page
> > faults,
> > > but maybe another process has the memory tied up, hence the excessive
page
> > > faults.
> > >
> > > Additionally, if it is SQL Server taking the memory, maybe you can
flush
> > > cache to release the memory it is holding to get a better idea of what
is
> > > taking place. If cache gets flushed does the page faulting stop and
the
> > > systems speed back up?
> > >
> > > Just a thought.
> > >
> > > "Leila" wrote:
> > >
> > > > Thanks Jose,
> > > > Everything is fine when memory is available. When it runs out, the
disks
> > > > become busy because of page/sec
> > > >
> > > > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> > > > news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> > > > > how about defective hardisk
> > > > >
> > > > > --
> > > > > thanks,
> > > > >
> > > > > --
> > > > > Jose de Jesus Jr. Mcp,Mcdba
> > > > > Data Architect
> > > > > Sykes Asia (Manila philippines)
> > > > > MCP #2324787
> > > > >
> > > > >
> > > > > "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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>|||Leila wrote:
> Actually I monitored Avg. Disk Queue Length and the situation was
> good (RAID 5 is being used). But sometimes in heavy queries, the
> context switch reaches 10,000!
> I must check the indexes!
> BTW, there was a join that produced about 2,000,000 rows and consumed
> about 250MB of memory. But other apps that needed that query could
> reuse the data from cache without increasing the taken memory.
> I suggested the developers to use some filters while the users open a
> particular form in app instead of loading this amount of records into
> memory, or use paging to retrieve 2000 rows in each page. Can it help?
A join that produces 2M rows will tax temdb and SQL Server. Those types
of queries should be avoided, if possible.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Leila,
Sorry, for the late reply. I will attempt to explain. When a virtual
memory system runs low on physical memory, the processes (items running on
the server) still need to access memory. This is accomplished by using disk
as virtual memory. Hard Page Faulting would be when these processes are
being swapped (paged) out to disk, and back in from disk to memory as the
process need the 'page' to execute (that is what I meant by Page Faulting).
So I was attempting to get you to see what the page faults (delta) are for
each process by using the Windows Performance Monitor (Perfmon).
I was also suggesting that if you determined from using the Windows
Performance Monitor, that it was indeed SQL Server that was paging (as you
said you are running out of memory) maybe you could get it to release the
memory using some of the DBCC commands (please refer to BOL for the exact
ones).
I have seen where other processes start to run that cause performance
issues. Disk Management programs for defragmentation, database
auto-shrinking, and sql server maintenance jobs. Maybe once you determine
what is causing all the disk io's, or what is taking and not releasing the
memory someone can assist further...
Once again, just suggestions...
"Leila" wrote:
> Thanks,
> What do you mean by page faulting? What is it?
> Do you mean I use DBCC DropCleanBuffers to flush the memory?
>
>
> "NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
> news:791FCD37-1583-438A-B78B-91F927A7B4AF@.microsoft.com...
> > Leila,
> >
> > What process is page faulting? I assume it is SQL Server, if so, what
> > process has taken all the memory? It is SQL Server also? I know that you
> > said that it is the disk io that is causing the problem due to page
> faults,
> > but maybe another process has the memory tied up, hence the excessive page
> > faults.
> >
> > Additionally, if it is SQL Server taking the memory, maybe you can flush
> > cache to release the memory it is holding to get a better idea of what is
> > taking place. If cache gets flushed does the page faulting stop and the
> > systems speed back up?
> >
> > Just a thought.
> >
> > "Leila" wrote:
> >
> > > Thanks Jose,
> > > Everything is fine when memory is available. When it runs out, the disks
> > > become busy because of page/sec
> > >
> > > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> > > news:22BAFCC3-E90C-41DB-9237-CFD6A3B9A9FD@.microsoft.com...
> > > > how about defective hardisk
> > > >
> > > > --
> > > > thanks,
> > > >
> > > > --
> > > > Jose de Jesus Jr. Mcp,Mcdba
> > > > Data Architect
> > > > Sykes Asia (Manila philippines)
> > > > MCP #2324787
> > > >
> > > >
> > > > "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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

No comments:

Post a Comment