I have a powershell 2.0 script on an XP OS. The purpose of the script is to extract data from an old database (Sybase) and populate a SQL Server 2008 database. The model that I am using is to create OLEDB connections to the Sybase database. The script calls a series of stored procedures from the Sybase database. The results are used to create an XML string. The XML string is queried for the input data for the SQL Server stored procedures. AFter each data element is created in the SQL Server database the XML string is saved to a file. Every database connection is closed after execution is completed. It is simple model, but uses staggering amounts of memory. For transferring only 1000 rows of data the script memory footprint grows to 3G. When the script completes the memory does not drop. In an attempt to rectify this problem I have added logic to free every variable when not used and call garbage collection in every finally clause of every try block. I am aware that this is overkill, but am trying to find anything that will reduce the memory usage. I am in the processing of looking for a memory trace tool, but I am also looking for expert opinions as to possibly start tracking down this critical issue. I know I am missing something obvious so any advice would be appreciated.
4
-
Maybe you should take something else than XML to store data, it's inefficient as hell. Oh, and could you post the code?thejh– thejh2010-11-06 17:03:52 +00:00Commented Nov 6, 2010 at 17:03
-
The task, as you describe it, should not be difficult to implement, say, in C#, not in PowerShell. Can you try that or is the script too complex? At least try to separate SQL stuff from PowerShell. If it solves the problem then this is the way to go. If it does not solve the problem then it means PowerShell has nothing to do with it.Roman Kuzmin– Roman Kuzmin2010-11-06 17:42:49 +00:00Commented Nov 6, 2010 at 17:42
-
The way in which you write your PowerShell scripts could be part of this issue. For instance there is difference in the foreach operator and the foreach-object cmdlet in terms of speed and memory usage depending on the use case. There's a write up on improving performance of PowerShell scripts in the November edition of Windows IT Pro mag which includes things like the foreach v.s foreach-object tip. It would be helpful to see some POC code as part of your question.Chad Miller– Chad Miller2010-11-07 18:06:14 +00:00Commented Nov 7, 2010 at 18:06
-
Unfortunately, it is a very complex task to be able to post the source code. Agreed that XML is memory intensive and there are better methods of handling the data, but there are down stream processes that operate on the generated XML file so changing away from XML would be an extensive under taking. Before I go the route of a system re-deign to remove the XML, I would venture to guess that my XML management in my script is poorly designed. I have a loop that creates the string and writes it to a file. But the XML string is not being released to GC at end of loop; just keeps growing.user459866– user4598662010-11-09 18:39:24 +00:00Commented Nov 9, 2010 at 18:39
Add a comment
|
1 Answer
I found the source of the leak. I am using an OLEDB connection to a Sybase Server instance to get data to load into SQL Server. 95% of the leak was isolated to a single function that invoked a Sybase stored procdure. Instead of getting the results in a result set, this procedure had the option of iether returning the results as output parameters or a result set. I initially chose the output parameter option. For reasons that are not clear to me, this output parameter mechanism caused a massive memory leak. I changed the logic to use a result set and the resolved the leak. Not clear why output parameters were an issue, but I found an optional approach that corrected the problem.