Friday, July 28, 2006

Poor man's guide to memory usage tracking

I have a service and I need to make sure that it doesn't have a memory leak after running for a sustained length of time.  I'm doing all the good stuff in code to manage the garbage collection and I have the right tools to check for leaks in my code.  But I still want to monitor an instance of the service during regular usage.  I don't need anything too finely grained, I just want to see if the memory usage is trending upwards.  There's a good chance that I might have to deploy this out in the field, so I want something simple.  I could have used the Performance console, but I wanted something very simple to explain.  You can probably do this with a CScript batch file and WMI, but I wanted something fast to load and fast to exit.

What I want is to log the memory usage to a text file, with each entry timestamped.  I was able to do this with almost all off the shelf parts.  I did have to write the timestamper, but that was a trivial task.  Since the home viewers will not have my service, pick a service or app of your own and play along.  I'll describe what I did using FireFox as a substitute for the actual service.

In the excellent PsTools suite over at SysInternals site, there is a utility named PsList.  It's a combination of the pmon and pstat tools that works like a command line version of the "Processes" tab of Task Manager.  By default it lists information for all running processes, but you can filter it by service name or process ID.  I wrote a batch file to call PsList with the service name and the "-m" command line switch to print the memory usage.  PsList prints some banner information with the details.  Something like this:

PsList 1.26 - Process Information Lister
Copyright (C) 1999-2004 Mark Russinovich
Sysinternals -

Process memory detail for Kremvax:

Name Pid VM WS Priv Priv Pk Faults NonP Page
firefox 3936 108952 41380 32748 36452 140201 8 54

All fine and good, but not pretty enough for a log file.  What I need was just the last line.  So I piped the output from PsList through the good 'ol FIND command with "firefox" as the filter text.  With that, I can redirect the output to a file (with append).  I ended up creating a batch file named memlog.cmd that had the following commands:
pslist -m firefox | find "firefox" >>c:\logs\memuse.txt
That gave me the last line in a file.  But I still needed the time stamp. I thought about going through some script file sleight of hand with ECHO and DATE, but this is the Windows Server 2003 CMD.EXE.  It doesn't have that skill set.  I could do with some 3rd party shells, but the goal is something I can deploy on a remote site without anyone having to pay for a tool or go through the hassle of installing something like Power Shell.

Time to fire up Delphi and create a little command line app that would take text coming in as standard input and send it back out as standart output, but with a timestamp prepended to the text.  The source code has less text in it than the previous sentence.  If you have Delphi, the following code will give you that mini-tool.  I used Delphi 7, any of the Win32 versions should do.

program dtEcho;



s: string;
WriteLn('[' + FormatDateTime('yyyy-mm-dd hh:mm', Now) + '] ' + s);

There's no banner or error checking.  I didn't need any of that and I wanted to keep it light.  By adding dtEcho to my batch file like this:

pslist -m firefox | find "firefox" | dtecho >>c:\logs\memuse.txt

I now get output like this:
[2006-07-28 23:14] firefox            3936  536904   61324   51244   57384   445377     90  249
[2006-07-28 23:15] firefox 3936 538176 60844 50764 57384 449193 91 249
[2006-07-28 23:16] firefox 3936 538212 60620 50528 57384 455935 91 249
The output only goes down to the minute, I'm tracking the memory usage every 10 minutes, I didn't need to make the timestamp that granular. If I needed it, I just need to make a slight change the dtEcho source code and it will print the seconds.

To run that batch file, I just used the scheduled tasks control panel applet and set it to run off of my account. For remote deployment, that would probably be the hardest step.

Thursday, July 27, 2006

SQL Server WHERE clause tip (not needed for SQL Server 2005)

One of my services logs every request to a private log table. That table mainly a diagnostic tool to provide some crude performance benchmarks. It's not designed for historical trending, so I have code to purge older records. The service would periodically (twice a day) issue a DELETE statement to the database server to delete records older than 30 days.  Given the following schema (sample, not the actual schema):

[RecordID] [int] IDENTITY(1,1) NOT NULL,
[LogTimeStamp] [datetime] NOT NULL,
[Duration] [decimal](124) NOT NULL,
[SessionID] [varchar](40) NOT NULL,
[IP] [varchar](24) NOT NULL,
[Request] [varchar](80) NULL,
[Response] [varchar](80) NULL,
[Error] [varchar](255) NULL,
[Description] [varchar](80) NULL,
[RecordID] ASC
[LogTimeStamp] ASC

I would execute the following SQL statement;


It's pretty simple, use the DateDiff() function to compare the timestamp field with the current date and if it's older than 30 days, delete that record.  I implemented that code in the first go around of the code, about two years ago.  This week, I was in that area code for some maintenance and I took another look at that statement.  That WHERE clause jumped right out at me.  For every row in that table, both the DateDiff() and GetDate() functions are going to be called.  SQL Server will need to compare every value of LogTimeStamp to see if it is older than 30 days ago.  In this case, MyLog has an index on LogTimeStamp, but it will has to read the entire index.   GetDate() is a nondeterministic function, it's going to get re-evaluated for each row in the database.  Since the actual date comparison is against a constant value, I decided to evaluate the comparision date first and change the WHERE clause to a simpler expression.

DECLARE @PurgeDate smalldatetime
DELETE MyLog WHERE LogTimeStamp @PurgeDate

I added a smalldatetime variable and assigned to it date of 30 days ago with the DateAdd() and GetDate() functions.  Now SQL Server can use the value of @PurgeDate to jump into the index and jump out when the date condition no longer matches the criteria.  By I implemented this on SQL Server 2005 and when I evaluated the estimated execution plans for each delete statement, I was surprised to see identical plans.  Both sets of statements spent the same percentage of time doing scanning and deleting.

When I did the same evaluation on SQL Server 2000, I saw different results.  The first delete statement spent 73% of the time scanning the index and 27% actually deleting rows from the table.  The second delete statement spent 19% of the time scanning and 81% of the time deleting rows.  On table that could have a large number of rows, it turned out to be big performance saving on SQL Server 2000 installations.

It's pretty cool that the SQL Server 2005 parser is smart enough to optimize code and recognize a constant expression when it sees it.  My code would have seen a nice little performance boost by moving from SQL Server 2000 to SQL Server 2005.  It's still a better thing to pull constant expressions out of the WHERE clause when you can do that.

SQL formatting courtesy of The Simple-Talk SQL Prettifier for SQL Server.

Friday, July 14, 2006

Migrating to Delphi 2006

I've been working on migrating our Delphi 5 code to Delphi 2006.  For the most part it's been pretty straight forward, the fun part has been dealing with the assortment of 3rd party packages.  Some of them required new versions, others were not available and I had to migrate them myself.  I'll save my DevExpress experiences for another post...

Most of our Delphi code has been in Delphi 5 for a number of reasons.  We mainly stayed with it because it worked and did what we needed.  We did a complete rewrite of flagship app, VersaTrans RP, and Delphi 6 and Delphi 7 were released during that cycle.  Delphi 6 didn't add any features that we needed and Delphi 7 came out during regression testing for RP.  It would have been insane to switch compilers during final testing.  I do use Delphi 7 for our web based application, e-Link RP, mainly because of the SOAP functionality.  Now it's time to round up the children and jump on the Delphi 2006 wagon.

One of the packages that I migrated up was Abbrevia.  This is an Open Source compression toolkit that includes components for handling PKZip, CAB, TAR, and gzip formats.  It used to be a commercial product from Turbopower, but a few years back it became Open Source when Turbopower was bought out by Los Vegas gaming company.  Since going Open Source, development has come to a flying stop and nothing new has been released since 2004.  I had to tweak the package files somewhat to compile with Delphi 2006.

The distribution came with tons of file, and package files (.dpk) for every version of Delphi/C++Builder from 3 to 7.  I took the Delphi 7 package files and edited them outside Delphi to make Delphi 2006 packages out of them.  I probably could have done within Delphi, it was easy enough to do with TextPad.  I changed the names to match the compiler, Abbrevia uses the the compiler version to differentiate between the various package files.  B305_r70.dpk became B305_r100 and B305_d70 became B305_d100.  Except they didn't include B305_d70, I had to use the Delphi 5 version B305_d50.

I also changed the file paths around.  There is a source folder and a packages folder.  I added a bin folder and edited the package files to place all of the compiled units in that folder.  I also need to explicitly add the source folder to the package's source path.  Then I tried to compile.  The runtime package compiled without incident.  I moved it's .bpl file to my system32 folder and moved to the design time package.  It failed to compile because it couldn't find the DesignEditors unit.  The DesignEditors unit contains the base classes for implementing component editors.  You used to be able to have that unit in your runtime code, Borland has stopped allowing it.  It does come with Delphi, just not on the default search paths.

So I figured I could just add the path to DesignEditors.pas to the package search path.  Just add the path and Bob's your uncle.  Nope, didn't work.  DesignEditors.pas has a typo in it. It's missing a comma in the uses clause of the implementation section.  My first inclination was to just add the idiot comma, but it seemed like other things would have failed if that was the case.  I was doing it wrong.  Instead of adding the source path, I'm supposed to have added the .dcp compiled package, which is in DesignIDE.dcp.  Once I added that unit to the package's requires list, everything was good to go.

Thursday, July 13, 2006

The following could have been coming from my desk (but didn't)

Overheard at work today.

"It's Wise, so I really don't ask questions. I just work around it." — Sam Tesla, 7/12/2006

If you've worked with the Wise for Windows Installer, you'll understand...

[Joe White's Blog]

I finally received confirmation from Wise Tech Support that the last bug that I reported was a real bug. You can't install a service written in .NET 2.0 on a machine with the 1.1 and 2.0 Frameworks installed. Wise runs the installutil.exe from the Framework to install the service and they pick the wrong version to run. Of course, it may be fixed for the next release, but that's tentative. My work around of using undocumented methods to self-install will continue to be used.

Friday, July 07, 2006

I don't care about RocketBoom

I have seen too many blog postings about Amanda Congdon leaving the RocketBoom vlog. There's a he said/she said thing going on between her and the other owner. Since the world+dog seems to have a need to post about, I might was well too.

I first heard about RocketBoom when it became available through TiVo. I subscribed to it for about a week or so, but i cancelled it. I found her schtick to be tiresome and annoying. She's an acquired taste that I just couldn't acquire. Ze Frank is much better at that kind of stuff.

In the end, it will probably work out well for both partners. Congdon has been getting plenty of job offers and RocketBoom has had tons of free publicity.