GAC

If I am on a computer named "box1" and type "\\box2\c$\WINDOWS\assembly" into my run directory and see a GAC window, you might assume I'd be looking at the GAC on box2. You'd be wrong.

Pulling up this directory on in windows explorer causes Windows to display the contents of the GAC on the local machine instead of the actual directory's contents. Browsing to this folder over the network does the same thing - show the GAC of the LOCAL machine.

Carnegie Mellon

I can finally finish my application to earn a PhD at Carnegie Mellon. I am posting this entry since they ask for a homepage on my application, and I don't even have anything yet on this blog related to what I want to work on. This is not because I haven't been reading and working on anything related to my PhD, or I'd rather write about business software, just that this blog has been about what I am working on, not what I would like to be working on. There are no jobs for researchers in anything interesting with only an M.S. (which I only recently finished anyway), so I do a lot business programming, accounting, sql databases, websites, etc. What I do for a day job is not bad, and certainly could be worse, but I want the PhD to be able to work on what I find interesting, not what employers find profitable.

I may start adding research notes to this blog. So far, despite having a small number of entries, it has been useful, if only just for my own reference. I doubt anyone from CMU is going to look up this page, but if you are from CMU reading this, please post a comment to prove me wrong. A blog is not the best source for determining how good a student is, but it is probably better than just going by GRE scores.

log4net FileNotFoundException

For the longest time I was getting a FileNotFoundException debugging on my local when trying to use log4net in an aspx app. I would always break on ConfigureAndWatch in the Global.asax. Since I knew log4net was working, and the correct version was loaded from the GAC, I aways just hit "continue" and never spent any time on it.

Today I noticed log4net itself appeared in the call stack when this exception was thrown. This is odd, the assembly is throwing an exception looking for itself. I loaded the debug symbols for both log4net and the system assemblies, which is a great thing to be able to do, and found it was thrown from System.Type.GetType which was being used to dynamically load the PatternLayout class from log4net. Somehow this was incorrectly specified in the logging.config file I used to configure log4.net. Somehow log4net was not smart enough to know I incorrectly specified a loader, and the system exception, that the assembly could not be found, was being thrown.

SQL Server Statistics

Today I found a problem with indexed view performance getting worse over time. This is another post I am creating mostly for myself, but this may be useful for others since it was pretty hard to find all the details on the web. One of the most important factors to SQL Server performance is up to date statistics. Using automatic statistics (enabled by default) does a good job, but we found the need to run sp_updatestats during periods of low activity. We use a Prestonia based server (waiting for Nehalem) for a 5 GB database with 100+ active users, so every bit of performance helps. Automatic statistics and sp_updatestats handles everything well except indexed views.

sp_updatestats ignores indexed views. Microsoft claims this is because statistics on indexed views are ignored except when using the WITH (NOEXPAND) hint. You need Enterprise Edition to use indexed views without the (NOEXPAND) hint, so the only explanation I can think of for this behavior is somehow Microsoft thinks screwed up statistics will sell more Enterprise licenses. If they sell more of anything, it will be Oracle licenses. I have never seen automatic updates work on indexed views either, although I haven't seen this documented.

The result of this is that indexed view that sped everything up when you created it is getting further out of sync with its statistics. Eventually this leads to horrible execution plans that just keep getting worse. The solution, first you should run:

SELECT so.name,
si.rowmodctr,
si.rowcnt,
CASE WHEN si.rowcnt > 0 THEN 100.0 * si.rowmodctr / si.rowcnt ELSE 0 END PercentError
FROM sys.objects so
INNER JOIN sys.sysindexes si ON si.id = so.object_id
WHERE is_ms_shipped = 0 AND indid = 1
ORDER BY 4 DESC

Running this periodically will show you the state of all clustered index statistics, including indexed views and regular tables, and is a good thing to pay attention to regardless of if you have Enterprise edition or use indexed views.

You will need to update stats for each indexed view similar to 'UPDATE STATISTICS vAcBalanceBill', I have not found another way to do this. Setting this up in SQL Server Agent mostly takes care of the problem.