Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2000 Memory Leak?

Posted on 2003-03-25
10
Medium Priority
?
3,008 Views
Last Modified: 2007-12-19
I'm running a copy of SQL Server 2000 SP3 on Windows NT4 SP6a on a dual 800MHz with 1GB RAM.

I'm running a busy web application which uses ASP to talk to a VB COM object which in turn connects to SQL.  It handles relatively large amounts of data and can be quite processor intensive.  The main problem I have seems to be due to a memory leak.  When the application is running some of its larger methods you can watch the memory go up in the task manager on the server.  When the method finshes the memory doesn't seem to be released.  This can consume 300 to 400 MB of memory during just one call to the COM object.  I've triple checked the ASP and COM object for non-destroyed objects and am 100% sure that everything is being cleaned up correctly.

To test SQL I have written a DTS package in VBScript to transfer data from one database to another on the same server.  I have intentionally made the row size quite big to simulate the functionality in the web app.  The field datatypes are:

int (4)
uniqueidentifier (16)
int(4)
nvarchar (1024)
nvarchar (1024)
nvarchar (1024)
nvarchar (128)
nvarchar (1024)
nvarchar (1024)
bit (1)

The table has well over 100000 records so I've set the DTS to batch 1000 at a time to test the memory.
The DTS uses an ActiveX Script containing 4 ADO objects (2 connections and 2 recordsets).  These are created and opened at the start and closed and destroyed at the end.

Each batch of 1000 records transferred takes about 13MB more memory!?  This memory never seems to be released from SQL so it eventually runs out and paging starts.

Does anyone know how I can force this memory to deallocate or what I might be doing wrong.

Looking forward to your replies!

0
Comment
Question by:BNBJ
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 3

Expert Comment

by:jSkipB
ID: 8205715
Perhaps this may be worthwhile:

You may notice that, after the very first couple of iterations, the problem doesn't seem to be there, but rather occurs after, say, the first 3-5K records have been processed.

If it doesn't cause integrity problems, after each 1000 rows, commit any pending transactions, then give SQL a chance to "calm down" (you may even have to close and reopen your data connection). This applies especially if implicit transactions are turned on.
0
 
LVL 4

Accepted Solution

by:
TheSpirit earned 2000 total points
ID: 8206595
OK this is expected behaviour from SQL Server. Under default setrings when SQL starts up it will take a low amount of memory, it will then take more memory up to it's maximum setting (or as much as it can !) It will release the memory when the OS comes under pressure for memory (down to 5%). Now you can limit the maximum memory SQL will take, and also how much it will release, my setting the maximum and minimum settings. My understanding is that on Win98 it works a bit different and will release but check Books on Line.
So bottom line is that what you describe is expected behaviour from SQL, it will take as much memory as it need and not release it, unless it really has to.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8207502
TheSpirit is right with his post.  I assume you're not using AWE and you have SQL Server configured to dynamically allocate memory?

Brett
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:SylvainPouliot
ID: 8207661
I have a question for you...

What if I want to let MSSQL use as much memory as it wants, but I would like to get it back when the peak is done...

I have the same behavior than BNBJ and I ocasionnaly turn off and on my server to free up some memory.

By the way, I had a server on a computer with 256 megs RAM and MSSQL was always taking as much as it could...when we upgraded to a computer with 1.8gig of RAM, MSSQL did the same thing with nearly 1.7gig of memory for itself...

0
 

Author Comment

by:BNBJ
ID: 8209433
Thanks TheSpirit.  Not the answer I wanted but if that's the way it is then fair enough.

If I want to isolate the web app's database with no detrimental effect on the other databases on the webserver, would I be best setting up a 2nd instance of SQL and putting the web app database in there?  I could then tell each instance how much memory it can have.

The reason I say this is as the server slows down and memory increases I tend to get a problem with ASP and SQL.  I either get an 'HTTP 1.1' message or '[DBNETLIB] SQL Server does not exist or access denied'.  This then means I need to reboot.

Would controlling the memory useage within SQL help with this and reduce the problem?

Also, whats the SP for limiting memory?

Thanks everyone.
0
 

Author Comment

by:BNBJ
ID: 8209642
I've just read in Books Online the following paragraph...

"There is a short delay between the start of a new application and the time SQL Server releases memory. Using max server memory prevents this delay and may give better performance to the other application. Only set min server memory if the start time of new applications sharing the same server as SQL Server shows up as a problem. It is better to let SQL Server use all of the available memory."

Do you reckon by setting the max server memory this would then prevent the '[DBNETLIB] SQL Server does not exist or access denied' message as the remaining memory could be accessed quicker by IIS?
0
 
LVL 4

Expert Comment

by:TheSpirit
ID: 8210423
Setting the max memory would probably help you since there would be no delay waiting for SQL to give up the memory it took.
0
 

Author Comment

by:BNBJ
ID: 8210540
What about setting up 2 instances of SQL?

Any benefit or would it just be an increased overhead?
0
 
LVL 34

Expert Comment

by:arbert
ID: 8210862
Setting up a second instance takes more resources on the server--you're escentially running 2sql server process now....I've used them before, but I haven't really seen anything good from them (you're suppose to not affect the other instance, but I've had an instance lock up the whole server and all you could do is stop and start ALL sql servers)....

Brett
0
 

Author Comment

by:BNBJ
ID: 8211244
Thanks everyone for your comments.

TheSpirit,

Such a simple solution in the end.  I've set the max server memory and kept just the one instance and so far evrything seems fine.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question