Memory never released in SMO (when create database)

[Hao]

I have a simple application to create an empty SQL Server database (no
table) in C# trying smo. I believe I closed all connections and also forced
garbage collection.
Why did I see memory increased by at least 9M (from 17MB to 26MB) and never
came back?
Is it by design?

My goal is to create thousands of tables and hundreds of databases. I ran
into problems when the code actually consumed about 2G memory. Now I tried
to simplify the code to find the memory issue.

Thanks.
Hao

 

[Hao]

Here is the testing code.

static void Main(string[] args)

{

if (true)

{

// Connect to the local, default instance of SQL Server.

Server srv = new Server();

// Define a Database object variable by supplying the server and the
database name arguments in the constructor.

Database db = new Database(srv, "Test_SMO_Database");

// Create the database on the instance of SQL Server.

db.Create();

Console.WriteLine(db.CreateDate.ToString());

}



GC.Collect();


GC.WaitForPendingFinalizers();

Console.Read();

return;

}

 

[Alvin Bruney [MVP]]

You don't need to do that GC collect and pendingfinalizer stuff. I see that
code on the net a lot and it is mostly incorrect. What you need to do is
close your open connections which it seems like you aren't doing so there's
no benefit in calling gc collect because you still have roots. Most
probably, your open connections, command objects and readers are not being
de-allocated properly.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

Here is the testing code.

static void Main(string[] args)

{

if (true)

{

// Connect to the local, default instance of SQL Server.

Server srv = new Server();

// Define a Database object variable by supplying the server and the
database name arguments in the constructor.

Database db = new Database(srv, "Test_SMO_Database");

// Create the database on the instance of SQL Server.

db.Create();

Console.WriteLine(db.CreateDate.ToString());

}



GC.Collect();


GC.WaitForPendingFinalizers();

Console.Read();

return;

}

I have a simple application to create an empty SQL Server database (no
table) in C# trying smo. I believe I closed all connections and also
forced garbage collection.
Why did I see memory increased by at least 9M (from 17MB to 26MB) and
never came back?
Is it by design?

My goal is to create thousands of tables and hundreds of databases. I ran
into problems when the code actually consumed about 2G memory. Now I
tried to simplify the code to find the memory issue.

Thanks.
Hao

 

[Steven Cheng[MSFT]]

Hi Hao,

As for the SMO management code, I've also tested it locally and the memory
behavior does be like you mentioned. I think it is the expected behavior
since the .NET managed memory management is different from raw unmanaged
application in which memory will be released immediately after we delete
objects. In .net , those object allocated on CLR heap will not be released
like unmanaged objects, it is the CLR GC that control when to sweep and
collect unused objects. Also, managed heap is like a pooled memory block,
when it allocate a block of memory, it may keep it for sometime (for
caching) in case some other new created objects will use it, so it is
expected to see that the .NET application keep certain size of memory after
it previously use them(not release immediately).

If you do care about memory leaking, you can use performance counter to
lookup both total private bytes and managed heap size of the application.
(Task manager "mem" column only show private bytes). If all the memory are
hold in managed CLR heap, I think it is ok. If there is continous memory
increase in unmanaged heap(managed heap not grow), we may take further look
into it. Anyway, so far I haven't found any existing memory leak issue of
SMO objects.

http://msdn2.microsoft.com/en-us/library/ms954591.aspx

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hao]

Hi Steven and others,

Thanks for the knowledgable answers.

I blieve that there is no actual memory leaks in my application. The
challenge I experinced is to create tens of thousands of tables/databases
during the operation. I am developing a data center server application.
Normally we do not need to create tables, but occasionally we have to create
such a large number of tables. The problem is that the server used about 2G
memroy when it tried to continuously create tables. The system became
extremely slow since it only had 2G memory.

Another interesting thing that I noticed is that if I move the table
creation code to a simple Windows Forms application (I am actually using the
SMO demo application "ManageTables" installed from MSDN SQL Server demos), I
saw large memory never released even after it finished running and I left
the window open over the weekend. Here is the most interesting thing: When I
minimize the window, the memory usage dropped to only a few MB from
thousands of MB immediately. Why minimizing window trigger the memory usage
(caching) of SMO code?

What is the best solution to create huge number of tables? I have to create
tables on the fly but infrequently. My operation will depend on these
tables.

Thanks.
Hao

 

[Steven Cheng[MSFT]]

Thanks for your reply Hao,

For the behavior that memory will drop a lot when you minimize the windows,
it is because what you watch is the "mem" column in task manager, this is
actually the workingset (physical memory used), then, when you minimize the
window, the process will release many of its commited physical memory so
that the value drops a lot. For all the virtual memory consumed, you should
choose the "Virtual Memory size" column in task manager.

Also, for the SMO object, I have performed some further test, I have used
the following function to create many SMO database objects, create and drop
them. It seems the .NET clr heap size remain at a stable size, not
continuously growing to a very large size.

===========================
private void btnCreateDB_Click(object sender, EventArgs e)
{
for (int i = 0; i < 1000; i++)
{
Server server = new Server(".\\SQLEXPRESS");
Database db = new Database(server, "my test db" +
DateTime.Now.Ticks);

db.Create();

db.Drop();
server.Alter();
}
}
==========================

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hao]

The memory usage does not grow in your testing because the database objects
created and then dropped. If you keep creating tables in a database, the VM
consumption will keep increasing. From my testing, I found that each table
cost about 800 more bytes. It is not too bad that the virtual memory
consumption of a process will increase about 20MB after creating 25000
tables continuously.

Is there any way to delete the cached table objects in the SMO after I
create so many tables? I like to release this 20MB memroy if possible.

Thanks.
Hao

 

[Steven Cheng[MSFT]]

Hi Hao,

Thanks for your reply and the further info.

I'll also do some new tests on this and see the result. I'll update you
soon.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hao]

Thanks for the help.
I've managed to continue creating lots of databases and tables without
increasing memory at client process side by closing connections every time
after I create a table.

Using SMO to create database objects costs memory, especially when we want
to create hundreds and thousands of database objects (such as tables)
continuously. There is a one time global cost of creating additional tables,
which is about 10MB. This cost is static and unrelated to the number of
tables being created and is unrelated to whether the creating is continuous.
The tests have been performaned to monitor the virtual memory consumption:
24MB memory before creating tables, 34MB after creating 257 tables (972 real
type columns per table), then still 34MB after creating another 257 tables.
During the table creation, the VM consumption can temporarially be increased
to 40 MB. The key tip to control the memory consumption in creating large
amount of tables continuously is to close connection after every table
creation. If the connection was kept open for the entire creation, the
memory consumption will be accumulated all the way to 2GB.

However, the SQL Server's VM usage was increased from 150M to 1.3G. I have
2G memroy. The table creation failed after creating 2313 tables. The error I
saw in the windows event viewer is "There is insufficient system memory to
run this query. (EventID 701)."

How do I reduce the SQL Server memory usage for example by cleaning all
possible cache? I cannot restart SQL Server, which of cause will clean all
the memory.

I tried DBCC FREESYSTEMCACHE, DBCC FREEPROCCACHE, or "CHECKPOINT
DBCC DROPCLEANBUFFERS" and I did not see these work as I expected.

After when I see SQL Server using so much memory, is there any command or
diagnostics tool that I can issue to clean up all the memory for SQL Server
process?

Thanks.

Hao

 

[Steven Cheng[MSFT]]

Hi Hao,

I've performed some further tests which continously create SQL Server
database through SMO(I use a for loop to create 1000 new empty databases).
Yes, the memory used will increase during the creation, however, for
managed CLR heap, "bytes in all heaps" only increase about 20 mb.

Also, it is expected that memory usage(expecially in SQL Server process's
VM) will increase as new created database may remain in memory instead of
keep in database disk file. For .NET CLR runtime, it will also hold some
reference objects, but as I've tested, that won't cause significant CLR
memory pressure.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hao]

There must be some memory related bugs in SQL Server that was fixed in the
SP2 or patches right after SP2. I thought I did install the SP2, but
actually I did not. Therefore I downloaded and upgraded my SQL 2005 to SP2
plus two additional patches. The memory issue is resolved. I am able to
create thousands of tables and manage the SQL Server memroy (cache) to avoid
any insufficient memroy error. I will continue to test more to make sure the
problem is 100% gone.

Thanks.
Hao

 

[Hao]

Unfortunately, the test failed with 701 (insufficient memory) after creating
4883 tables. It is much better than before. It used to error our after
creating around 2000 tables.

Hao

 

[Steven Cheng[MSFT]]

Thanks for your followup Hao,

So far my test did indicate that when you continously create SMO tables,
the held memory will increase and I haven't found any existing issue
documented on this. Due to the complexity of the further troubleshooting on
this that may require some code analysis to the SQL Server SMO's internal
implementation, I would suggest you contact CSS for further debugging on
this or confirm whether this is actually an issue in SMO or SQL Server
product engine.

http://msdn.microsoft.com/subscriptions/support/default.aspx

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.