SQL 2K memory leaks

Question

Post reply

SQL 2K memory leaks

Russell Shilling

SSCarpal Tunnel

Points: 4399
More actions
October 25, 2005 at 3:29 pm

#166423

I was discussing a performance problem with a tech support person from our application vendor, and he suggested that we should periodicall use EM to decrease server memory to minimum, then set it back to max, to get rid of memroy leak problems (with their app, or others).
Because I'm lazy, and a DBA, I'm not going to do this every day after hours. Rather, I'll script it:
sp_configure 'max server memory', 4
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'max server memory', 2147483647
GO
RECONFIGURE WITH OVERRIDE
GO
Will this actually help performance, or should I just schedule a weekly server reboot. Or, what?
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Kory Becker Hall of Fame Points: 3585 More actions · Answer 1

What version of OS and MS SQL Server are you running?

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 2

Win 2K3 and SQL 2K SP4.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Kory Becker Hall of Fame Points: 3585 More actions · Answer 3

Are you taking adavntage of AWE? There is a bug with SP4 and AWE! What was the symptoms that caused the vendor to make this recommnedation? Was the server becoming unresponsive due to memory or were you getting memory errors?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Is the vendor app using any of extended stored procedures especially the Object Automation procedures ? These xps begin with sp_OA%.

There are many problem with the Object Automation procedures including:

1. Yes, there are some memory leaks in these procedures due to the MS software.

2. If the code is not written correctly, the object is not destroyed (call to sp_OADestroy)

3. Since it is not possible to trap ALL errors in stored procedures or triggers, even if the vendor has coded correctly, the clean up routines to destroy the object are cannot be invoked.

I also do not believe that reducing and then reseting the SQL Server total memory will NOT reclaim the memory allocated to objects.

I do not think that an OS restart is necessary but just restart SQL Server.

P.S.

Make sure you do not restart SQL Server using "net stop" and then "net start". See Books OnLine for an explanation.

See http://www.codeproject.com/csharp/svcmgr.asp

for a command line program that will stop and restart services in the correct manner.

You could then schedule the stop/start thru the Windows Scheduler.

Please respond if you find the source of the memory leak as I have not had a SQL Server memory leak problem in at least 4 years. When SQL Servers that have been running for over 3 months, I have not seen a problem with memory leaks.

SQL = Scarcely Qualifies as a Language

Mark Hickin Say Hey Kid Points: 693 More actions · Answer 5

If you believe there is a memory leak in SQL Server, you should get perfmon out.

Mark Hickin Say Hey Kid Points: 693 More actions · Answer 6

Sorry, posted too soon there.

As Carl said, sp_OaCreate is the most likely reason for memory leaks. You should also investigate whether or not they are use extended stored procedures written by themselves.

I have seen 'SQL Server Memory Leaks' caused by custom xp's quite a few times.

What are the symptoms of this problem?

If you find that the server just becomes sluggish there are many many potential causes. I have seen fragmentation in Memtoleave cause this kind of problem, and this tends to be caused by heavy linked server usage. There are various workarounds for this.

Adjusting the memory settings won't acheive much. The only sure fire way is to restart sql. This will zero out sql's memory area.

Carl - What is the thinking behind not using Net Stop, Net Start. A quick scan of BOL revealed nada, same on KB. I've neevr heard of any problems with that....

Mark

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 7

From the responses, it looks like the software vendor is either: 1. Used to working with version 6.5 , or 2. Blaming something else for the performance problems of their software.

Thanks for the responses.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Mark Hickin Say Hey Kid Points: 693 More actions · Answer 8

If they are used to using 6.5 it might be time to look for a new vendor

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 9

I've been thinking the same thing, lately.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

peterhe SSChampion Points: 11469 More actions · Answer 10

Just want to clarify one thing, according to the following blog, sp_OaXXX family procedures do not cause memory leak even if you forget to call sp_OADestroy. Take a look:

http://blogs.msdn.com/khen1234/archive/2005/10/13/480829.aspx