 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / New Users / May 2007Memory never released in SMO (when create database)
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 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 [quoted text clipped - 9 lines] > Thanks. > Hao 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.  SignatureRegards, 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. > [quoted text clipped - 46 lines] >> Thanks. >> Hao 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. 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 > Hi Hao, > [quoted text clipped - 50 lines] > This posting is provided "AS IS" with no warranties, and confers no > rights. 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. 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 > Thanks for your reply Hao, > [quoted text clipped - 39 lines] > This posting is provided "AS IS" with no warranties, and confers no > rights. 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. 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 > Hi Hao, > [quoted text clipped - 11 lines] > This posting is provided "AS IS" with no warranties, and confers no > rights. 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. 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 > Hi Hao, > [quoted text clipped - 17 lines] > This posting is provided "AS IS" with no warranties, and confers no > rights. 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 > 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 [quoted text clipped - 29 lines] >> This posting is provided "AS IS" with no warranties, and confers no >> rights. 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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.