Excessive memory usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Excessive memory usage

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-03-05 : 03:08:41

Every couple of weeks my SQL Server 2000 uses huge amounts of memory. eg: just now in Task Manager the sql process was above 500MB of memory. I doesnot stop working or anything just gets slow. As soon as I stop and restart SQL Server from Server manager all is well again for the next few weeks and mem usage sits between 30 & 40 MB. (DB backend for a few websites with light traffic)

I know it is a long shot, anyone with any ideas? Some issue I may have missed etc...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-05 : 03:40:47

Do you have any kind of stats or counters gathered over the period ?

Here is a stored proc I've written to gather "mem users"

***********
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_AST_MAX_MEM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_AST_MAX_MEM]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE proc SP_AST_MAX_MEM @minmem integer = 0
as

select convert(varchar(8),counter_procs.nt_username) as UserId,
'DB_Name'=substring(master..sysdatabases.name,1,35),
convert(varchar(30),counter_procs.loginame) as SQL_LoginName,
counter_procs.No_Processes,
counter_procs.mem_used_in_KB
from (select nt_username,dbid,loginame,count(*) as No_Processes,(sum(memusage)*4) as mem_used_in_KB from master..sysprocesses
group by nt_username,dbid,loginame) counter_procs
join master..sysdatabases on counter_procs.dbid = master..sysdatabases.dbid
where counter_procs.mem_used_in_KB > @minmem
order by 2,5 desc,1,3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

***************

I create this SP in master, so I can call it from anywhere -( I know, I'll lose it when we upgrade etc, and Isystables - but hey - it works for me...)

Run this as follows:

SP_AST_MAX_MEM 50000
and you'll have a report of users using > 50 MB memory (actuall 50000 K, but hey). From that, you'll have a login you can try and determine why it has so much memory etc.

HTH

as per normal - no warranties etc. you use at your own risk ( but I've used this for more than a year now).

Ciao

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-05 : 03:42:48

Meant to add: it would be relatively trivial to create a job that run this, and insert results into a table, so that you can generate a trend - this runs (in my environment, in less that 1 sec, so I would consider running it hourly, and adding a getdate() to the table for timings.

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Edited by - wanderer on 03/05/2003 03:43:22

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-05 : 13:03:59

SQL Server using lots of memory is normal. That is how it is designed. What gets slow when SQL Server uses 500MB of memory? If you have other applications running on the server, then move the applications to another server so that they aren't affected by SQL Server OR add more RAM to the box.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources