Could not find data type X.Y when creating a temporary table

Just a reminder: I was trying to create a temporary table in SQLServer 2k8 using a user-defined data type, a bit like:

use MyDB;

create table #myTempTable
(
    MyField udt.MyType_t not null
)

but hit

Could not find data type udt.MyType_t

This is because the type is only defined in my database MyDB – it’s not defined in TEMPDB. When you try to use it when creating a temporary table the database can’t find it.

The workaround is to use the underlying data type instead.

Boolean logic and WaitHandles

I had a fun five minutes today trying to express a wait on three WaitHandles X, Y and Z in which I want both of X and Y to be signalled, but have Z as an early escape. It’s soon clear that you can’t express:

(X && Y) || Z

With WaitAll and WaitOne, e.g. as

WaitHandle.WaitAll{ X, Y } || Z.WaitOne

because WaitAll blocks.

Thanks to the fact that boolean operations are distributive I can recompose (X && Y) || Z as

(X || Z) && (Y || Z)

and apply that structure in this case as

WaitHandle.WaitAny{ X, Z } && WaitHandle.WaitAny{ Y, Z }

with the proviso that the state of Z is unchanged by the Wait – for example, as long as Z does not autoreset.

My particular example was in managing a task workflow – the kind of thing that’s (almost certainly) made redundant by the TPL in .NET 4+. In my case, X and Y are a throttle on in-flight tasks, and a turnstile that admits workers when tasks are newly runnable. Z is an early escape in case of shutdown or exception.

I’m hoping that some clever bod will spot this and point out there’s a perfectly natural and convenient way of expressing this more simply.

Caught between declarative and programmatic custom configuration element methods

I don’t need to create custom configuration sections for .NET apps very often; whenever I do, it seems like an awkward process.

I hit a new problem recently; only a small one, but sufficiently frustrating that I need to commit it to memory. I was mixing up the two distinct methods for creating the configuration: programmatic, and declarative (or attributed).

The symptom was a  “key has already been added” exception when loading a new application’s configuration. The exception was thrown by configuration elements added to a custom ConfigurationElementCollection.

The error seemed clear: I’d already added a config item with this key. So how do I define the key for an item? Figuring it’d be easy I leant on Intellisense, found the IsKey attribute and thought – great! I can define the properties that make up the key. Happily, I added key properties to my derived ConfigurationElement, suitably attributed.

Daft.

It didn’t work, and it took me a little while to figure out what was going on. I’d forgotten that the ConfigurationElementCollection acts as a factory of sorts – and is also given the responsibility of generating the key. I’d filled out some simple placeholder code to generate an element key there which clearly wasn’t up to the task.

It was only after that, when I looked more deeply into the ConfigurationElementCollection that I realised I’d been mixing mechanisms for expressing the custom configuration. The IsKey attribute was irrelevant, as the mechanism I was using to generate the collection was programmatic.

So, multiple lessons learned: don’t lean on Intellisense without reading the docs; think about the structure of your data, even if it’s a configuration element; and don’t add placeholder code for functions like key generation.

State 1, Line 1 Incorrect syntax near ‘go’.

This stumped me for a short while. I had the following straightforward SQLServer batch:

if object_id( N'util.AllBatchProgress' ) is not null
    drop procedure util.AllBatchProgress;
go

and was taken aback when I hit this error when running it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'go'. 

It looked absolutely fine to me; Go needs to stand apart from your SQL batch, on its own line, and here it does. 

But what’s this about line 1? How could this look like a 1 line? Ah, maybe if I'd copied it from Notepad++ (as I did) without realising the mode I was in, I'd have something like:

(this time with all symbols shown). I guess SSMS is not parsing those CR carriage returns -- should be CRLF. Converting the line endings to Windows format (again in Notepad++) got it working.

Paint dries, kettles boil, threads block; waiting can be hard to do

Today I added a simple wait-then-retry mechanism to a piece of code that occasionally bites my current client. The existing code tried an action and just dumped out if an exception was thrown. In this case, some of the thrown exceptions weren’t fatal -- the command could be retried in a short while (it’s actually an attempt to update a database under transient load).

At first I implemented the wait-before-retry simply, with a deliberate trade-off between complexity and accuracy – as a series of Thread.Sleep steps, so that after each step the thread could yield to another. It looked a little like this:

class ActWithSimpleRetry
{
    private int _allowedAttempts;

    private int _delay;

    private Action _customAction;

    public ActWithSimpleRetry(int allowedAttempts, int delay, Action customAction)
    {
        _allowedAttempts = allowedAttempts;
        _delay = delay;
        _customAction = customAction;
    }

    public void Run()
    {
        var attempt = 0;

        while(attempt++ < _allowedAttempts)
        {
            try
            {
                // _customAction should throw a CustomException to simulate 
                // the behaviour I wanted to retry on.
                _customAction();

                return;
            }
            catch (CustomException)
            {
                if (attempt == _allowedAttempts) throw;
            }

            YieldingWait(_delay);
        }
    }

    private void YieldingWait(int delay)
    {
        var elapsed = 0;
        var step = 500;

        delay = 1000 * delay;

        while(elapsed < delay)
        {
            System.Threading.Thread.Sleep(step);

            elapsed += step;
        }
    }
}

There we go, professional job done. Looks like I’ve thought about it, it’s not just sat there blocking the thread for delay milliseconds. But hang on: do I even need to yield?

Constructing the wait like this is more complex than it need be: a single Thread.Sleep would do, as I’m not worried about freezing a UI; I’m not checking on “Cancelled” state; and I’m not waiting before synchronizing threads.

If I had been worried about synchronizing threads then this implementation would be clunky, leading me to poll on some shared state. Moreover, the waiting thread could always yield to another, and it might be a while before the waiting thread is able to execute its next wait step. The way I had implemented the wait – with no reference to actual time elapsed – is naive.

A simple Thread.Sleep would be a more honest solution to the problem.

What about situations where you do want to worry about timing accuracy and thread freezing – and coordinating with a cancellation signal on another thread? The alternative is to use a Timer running independently of the main thread. A first attempt might look like this:

public void Run()
{
    var attempt = 0;
    var timerEvent = new EventWaitHandle(true, EventResetMode.AutoReset);

    var timer = new System.Timers.Timer(_delay * 1000);
    timer.Elapsed += (sender, args) => timerEvent.Set();

    while (attempt++ < _allowedAttempts && timerEvent.WaitOne())
    {
        try
        {
            _customAction();

            return;
        }
        catch (CustomException)
        {
            if (attempt == _allowedAttempts) throw;
        }

        timer.Start();
    }
}

The Timer is created with the desired wait, and configured to signal a WaitHandle on expiry. The main thread then Waits on that handle (and checks the number of attempts) before trying again.

Here I’ve gained some control over the duration of the wait at the cost of some complexity. However – I’m still blocking on the main thread at timerEvent.WaitOne! If I want to entertain the possibility of a clean shutdown, I’ll need to also take a look at waiting on a cancellation signal. That would look something like this:

public void Run()
{
    var attempt = 0;
    var timerEvent = new EventWaitHandle(true, EventResetMode.AutoReset);
    
    // in practice this would be a member field that could be Set
    // from a separate "cancellation" thread
    var cancelEvent = new EventWaitHandle(false, EventResetMode.ManualReset);

    var timer = new System.Timers.Timer(_delay * 1000);
    timer.Elapsed += (sender, args) => timerEvent.Set();

    while (attempt++ < _allowedAttempts && WaitOnSignals(timerEvent, cancelEvent))
    {
        try
        {
            _customAction();

            return;
        }
        catch (CustomException)
        {
            if (attempt == _allowedAttempts) throw;
        }

        timer.Start();
    }

    // handle the drop through here; probably a cancellation
}

private bool WaitOnSignals(WaitHandle timer, WaitHandle cancel)
{
    var handleActions = new[]
        {
            new { Handle = cancel, Result = false },
            new { Handle = timer,  Result = true  },
        };

    var handles = handleActions.Select(x => x.Handle).ToArray();

    int signalled = WaitHandle.WaitAny(handles);

    return handleActions[signalled].Result;
}

Which is better (and reminds me that Chris Oldwood is always reminding me to wait on two events, not one): it’ll break out of the processing if there’s a cancellation. However, it still blocks at WaitHandle.WaitAny. If, finally, I want to yield to other threads periodically then I’ll have to break out of the wait periodically:

private bool WaitOnSignals(WaitHandle timer, WaitHandle cancel)
{
    var handleActions = new[]
        {
            new { Handle = cancel, Result = false },
            new { Handle = timer,  Result = true  },
        };

    int signalled;
    var handles = handleActions.Select(x => x.Handle).ToArray();

    while((signalled = WaitHandle.WaitAny(handles, 10)) == WaitHandle.WaitTimeout)
    {
    }

    return handleActions[signalled].Result;
}

Finally, here I get actual elapsed time before the timer expires and allows work to continue, and can avoid blocking on the timer wait by using Waithandle.WaitAny to automatically handle both timer and cancellation signals. There is a risk that it won’t react to a cancellation or complete signal as soon as possible because it yields control after 10 milliseconds – but if you want to allow other threads a look-in you have to accept that they’ll take some processing time, and that on top of the time taken for context switches.

Bearing in mind the added complexity of this wait and the loose restrictions around thread freezing and cancellation for the original app – I’d be sorely tempted to go for a simple, honest Thread.Sleep instead.

Fun with tools: Visual Studio 2012 RC and GitHub for Windows

I’ve been have fun with a couple of new tools recently. I’m finding the Visual Studio 2012 RC nice and clean, and straightforward to use. Despite the massed wailings on the ‘net about its stylings – I actually like the look of it a lot. I even like the ALL CAPS MENU BAR. Many don’t, it seems, and for them there’s a handy

ALL CAPS REGISTRY HACK

that’ll let you return to more familiar territory.

For some reason the grab bars at window tops bring memories of seeing Apple Mac desktops in Big K magazine, er, some years ago; it’s almost worth it just for that. The only wrinkle I really have is with context list menus – using a white highlight on grey means I need to take a moment to work out what’s selected if there’re only two options.

The second tool is GitHub for Windows. Nice simple Metro stylings – thankfully not in a painfully creative dark theme and the WORKFLOW – I’m habitually a command line Git user, so this is somthing of a revelation. It synchronizes seamlessly with GitHub, makes rollback and reversion a joy, eases diffing of modified files, allows easily selective commits, and more. For a currently one-man-show like me it’s pretty much perfect, although I imagine it’d work well in groups also – I’ve not tried any more advanced Git workflows with it.

Even if you don’t use Git, I’d say it’s worth a look.

C# performance counters impact performance? No, but it seems that Chart.SaveImage does.

A little thought later and it looks like no, they don’t impact performance. What I was seeing was the effect of prepping and drawing a chart – on about the same timescale that I was querying for information.

Diagnosing the problem was straightforward; I crosschecked the processor usage for the tool with perfmon. After configuring it to avoid saving down a chart the processor usage dropped to zero.

It is a good example of misleading yourself while looking at performance stats. I could see usage spiking on a period that looked suspicious. I used the VS 2010 in-built profiler and saw that PerformanceCounter.NextValue() was by far the biggest contributor to CPU time, and put two and two together to make five.

I ran the app for a few minutes, rather than the one minute I originally tested with, and the stats inverted. Chart.SaveImage() was overwhelmingly responsible for CPU consumption, with the calling method, Agent.Graph(), taking up ~71% of samples.

Schoolboy misinterpretation, but as always a bit of thought and investigation soon shows up the problem.

Now. Why is Chart.SaveImage() so expensive, and is there a cheaper alternative?

C# performance counters impact performance?

I’ve been using System.Diagnostics.PerformanceCounter to get at CPU and memory usage stats – but it looks like whenever I call NextValue it spikes CPU usage. Running my app through the VS profiler points straight at NextValue as the main CPU load.

It looks, then, like querying PerformanceCounter.Next value seriously impacts CPU usage – on my dual core laptop it flipflops between 5 and 10% utilization.

I’m pretty sure that the same thing doesn’t happen when you use ProcessExplorer, or TaskManager, or Perfmon. I’ve been trying to work out a native way of getting at the same data, but it’s been surprisingly difficult. I just found these that look like decent leads:

SO question on logging performance metrics

Logman -- command line perfmon use

Writing performance data to a log file

There are no workarounds

There are no workarounds; there is only the system. Some parts of the system might be clunky, or redundant, or might appear suddenly in response to changing needs; but there are no workarounds. There’s only the system, and the current behaviour of the system, and some improvements you can make, and the direction you want it to take.

Labelling something a workaround is dangerous. Something labelled as a workaround, or otherwise tagged as an object of expediency might tend to be left alone. No-one wants to touch it, as it’s “going away soon”. It might even accrete new functionality.

Your system might be mostly workarounds.

Thinking of code as a workaround is wrong. It may have been a workaround once, at the time of creation – but now it’s part of the system. Its behaviour needs to be characterized in tests. Changes to it need to be controlled. You need to be looking at it; refactoring it when you touch it and spot a useful change; considering it’s place in the overall design.

All system code is in a state of change, moving from one expression of requirements to the next. Holding parts of it constant because you consider them a workaround makes no sense.

Telling yourself it’s a workaround is just stopping you from seeing the reality of your system. Give it up; it might stink, but it’s doing the job; if it stinks so much that it’s holding up the evolution of your system then fix it, bit by bit.

Creating a chart programmatically in C# using DataVisualization.Charting

Means you have to reference System.Windows.Forms and System.Windows.Forms.DataVisualization, even though you might be doing this in a console application. That said, it also includes a set of data manipulation functions, some of which are even finance-specific:

using System;
using System.Drawing;
using System.Windows.Forms.DataVisualization.Charting;

namespace Sandbox
{
    class Program
    {
        static void Main(string[] args)
        {
            // set up some data
            var xvals = new[]
                {
                    new DateTime(2012, 4, 4), 
                    new DateTime(2012, 4, 5), 
                    new DateTime(2012, 4, 6), 
                    new DateTime(2012, 4, 7)
                };
            var yvals = new[] { 1,3,7,12 };

            // create the chart
            var chart = new Chart();
            chart.Size = new Size(600, 250);

            var chartArea = new ChartArea();
            chartArea.AxisX.LabelStyle.Format = "dd/MMM\nhh:mm";
            chartArea.AxisX.MajorGrid.LineColor = Color.LightGray;
            chartArea.AxisY.MajorGrid.LineColor = Color.LightGray;
            chartArea.AxisX.LabelStyle.Font = new Font("Consolas", 8);
            chartArea.AxisY.LabelStyle.Font = new Font("Consolas", 8);
            chart.ChartAreas.Add(chartArea);

            var series = new Series();
            series.Name = "Series1";
            series.ChartType = SeriesChartType.FastLine;
            series.XValueType = ChartValueType.DateTime;
            chart.Series.Add(series);

            // bind the datapoints
            chart.Series["Series1"].Points.DataBindXY(xvals, yvals);

            // copy the series and manipulate the copy
            chart.DataManipulator.CopySeriesValues("Series1", "Series2");
            chart.DataManipulator.FinancialFormula(
                FinancialFormula.WeightedMovingAverage, 
                "Series2"
            );
            chart.Series["Series2"].ChartType = SeriesChartType.FastLine;

            // draw!
            chart.Invalidate();

            // write out a file
            chart.SaveImage("chart.png", ChartImageFormat.Png);
        }
    }
}

Gives the following result:

RRDTool, and C# .NET bindings

RRDTool is pretty much ubiquitous in Linux/Unix cluster environments – or at least, it is in those I’ve worked with. It provides simple but highly reliable round-robin database services into which you can dump pretty much any metric you like. It’ll keep a running dataset at the granularity and duration you define; enable you to export data in chart and xml form; and even perform some useful analysis.

These days I’m typically working in Windows environment, and typically most clients I work for need some sort of dashboard to aggregate information about their clusters and grids. For a long time RRDTool was a pain to build on Windows, and there was no single .NET wrapper. The front runner – NHawk – actually spawned rrdtool processes rather than wrapping the underlying libs direct.

Now it turns out that someone’s contributed .NET bindings to the trunk of RRDTool. I figured I’d see if I could get it working, on 64bit Win7 (although actually x86 is fine for my app), under Visual Studio 2010.

I checked trunk out from SVN and took a look.

The first thing is to set up the dependencies – I tried to take a short cut and begin with a GTK+ package, but found it’s best to start with the files mentioned explicitly in the WIN32_BUILD_TIPS file.

There were a few apparently non-serious build errors in the rrdlib project. In several places explicit casts from void* were required on malloc, realloc. Also, there was some skipping of variable initialization in switch statements – latter can be fixed just by enclosing in a block.

Running through the tutorial with rrdtutorial raised one problem – an error claiming “No positional legend found”. The error was generated in rrd_graph_helper.c. The log showed that this was a work in progress, so I updated to the preceding version, built and it worked apparently fine.

After that I built the .NET bindings – basically fine, although I wrangled everything to x86 in the end just to get started. But then – nothing was being exported. There’s a .def file in the lib solution, but it doesn’t appear to exist any more. So: I wrapped a copy of the public method declarations in #ifdef WIN32, and prefixed with __declspec(dllexport), then rebuilt the .NET binding solution and: success.

All looks good to go. Next step is to grab metrics from windows performance counters in a C# app and see what I can present through RRDTool.

FEEE

It was like Christmas again: new blades arrived to add to our UAT and Production environments. And to start with, everything seemed rosy: twice the number of cores, to start with, and the installation of our server code went pretty smoothly.

Then I spun everything up, and watched all our processes die. No meaningful error messages, just some flurries of forlorn my-child-went-away pleas for help in our workflow manager logs.

Stumped, I wondered if I should be looking at the “socket forcibly closed” exceptions I could see that indicated my-child-went-away; but no. The child processes were just dying, and these errors were just an artefact. Later, the workflow manager eventually timed out the children, noting that they’d already finished with error code –254667849. Or something similar; the set varied.

I fired up eventvwr and there it was – a veritable storm of .NET framework errors in the application logs, with two characteristically repeated, one of which was:

.NET Runtime version 2.0.50727.3607 - Fatal Execution Engine Error (7A09795E) (80131506)

What followed turned out to be several hours of searching and getting rapidly downhearted. The whole of the internet seemed to have seen this very exception, and it seemed critically linked to either trying to run a process as a user that had no associated user profile on the box, or to vague “problems in the .NET 3.5 SP1 on Win 2k3 64bit boxes” that no-one – except one particularly determined individual and his team – seemed to get to the bottom of.

I checked: the user had an associated profile. I went to get some coffee.

I had to get a clearer picture of where our workers were dying. I installed the Debugging Tools for Windows and SysInternals suite on the box and then stopped.

The child processes were dying, but they were being spun up by a workflow manager. How was I going to get windbg to attach if I couldn’t spin up the process myself? They died immediately – there’s no way I’d be able to attach in time.

Luckily, Chris Oldwood knew the way. The gflags app in the Debugging Tools set let’s you configure a lot about the debug environment – and lets you even specify that for a given app (under the ImageMap tab) you should spin up a specific debugger – like c:\program files (x86)\debugging tools for windows (x86)\windbg.exe, for example.

It even worked! I pushed some work through the system and watched windbg attach, and let it run through to the exception. !analyze –v took an age – over 10 minutes – without actually giving me any information, so I used procdump to make a full dump, and analyzed that on my own machine.

procdump -ma TaskEngine.exe d:\some\temp\directory\TaskEngine.dmp

There, !analyze -v brought up two things. One, that it was dying while trying to make a SQL Server connection, and another that it might be having trouble associating a user context with the login

Trying with independent tools from that box also fail to connect to the database. The database was definitely up – I had to talk to it to push work through the system. The native client installed on the box is the same version as that installed on the existing servers.

Something’s clearly missing, and as yet, I don’t know what.