Bloghater's Rantings

Monday, February 20, 2006

K2.NET 2003 processes start on random versions

Issue:

When using K2.NET 2003 in a load balanced environment, you may notice that new processes start on versions other than the latest to be exported using K2 Studio.

Keywords:

K2.NET 2003, Load Balancing, NLB, Process Version. Little admission; I think this is on K2.NET 2003 SP1a, but I'm not sure... and I don't know if it's resolved yet, but I doubt it.

Background:

It seems that there is an issue in the K2 application server, in that the definition of the latest process version is cached on each server in the load balanced set. Then, when a process instance is created on each server, the latest cached version on that server is used to define it. The problem appears to be that when you export to K2 using K2 Studio, only a single member server of the load balanced set receives the export. This server correctly stashes the definition in the database, but the other member servers fail to notice that a new version is available, and continue with the previous definition. Left unchecked this could go on for a while, and all sorts of odd combinations of running instances can spread throughout your application.

Investigation:

The simplest way to spot this is to write your own tool to interrogate K2. I wrote a utility using the K2ROM to list all Process Sets, all Process Versions under each set, and all running Process Instances on each version. This is actually quite trivial to do with the K2ROM. What you will notice is that some instances will have a Start Date that is later than the Exported Date of the latest Process Version for that Process Set. Obviously if you are using K2 in the normal manner (i.e. you haven't set an alternative default version) then this makes no sense - once an updated version has been exported all process instances started afterwards should be running on it.

* if some of these phrases (Process Set, Version, etc) are not familiar to you, they will be by the time you've mucked around in the K2ROM a little, so have a dig and all will become clear.

Resolution:

Nice and simple (although by no means ideal) - after every export to K2, restart the K2Server service on each load balanced server. You can of course take each of them out of the balanced set, cycle the service, and put it back in before moving onto the next server to minimise disruption if you cannot have any downtime.

Enjoy!

Sunday, October 09, 2005

SQL Blocking due to Stored Procedure Recompiles

Issue:

When under a small amount of load, many spids are listed as blocked or blocking in SQL Enterprise Manager. On examination of the blocks, the resource is displayed as [COMPILE]. Performance of the database may also not be up to scratch.

Keywords:

SQL Server 2000, T-SQL, Stored Procedures, Compilation, Blocks, Deadlock, Regional Settings.

Background:

It turns out that SQL assesses whether to recompile a stored procedure when it is called based on a list of criteria, found at http://support.microsoft.com/default.aspx?scid=kb;en-us;243586. When recompiling a stored procedure, SQL requires an exclusive lock on it until the plan has been calculated and cached. Therefore, if many connections need the same stored procedure at the same time, and it is being recompiled every time, a number of blocks will appear.

Investigation:

The support article at http://support.microsoft.com/default.aspx?scid=kb;en-us;263889 details how to investigate this further. Using SQL Profiler monitor the SP:Recompile event and add the EventSubClass data field to the trace. The contents of the EventSubClass field in the trace can be used in conjunction with the article found at http://support.microsoft.com/default.aspx?scid=kb;en-us;308737 to determine the cause of the multiple recompiles.

Resolution:

In my case, this was the presence of a SET DATEFORMAT statement in a stored procedure. This statement was forcing a recompile every time the procedure was executed, even when the KEEP PLAN option was specified. It turns out that the simplest resolution was to change the SQL user's default regional settings to British English, to match the specified date format of DMY. This means that the format statement is not executed on each call, and therefore the recompilations cease.

Obviously the resolution to your specific problem may be slightly different, but the links shown in this post should help you track it down. Enjoy!

Welcome

Hello and welcome.

This blog's aim is to provide real, useful, resolutions to issues encountered when developing with Microsoft (and related) technologies. It is not my life story, nor will I commit to updating it at any time other than when I have something useful to say.

One of my pet hates is blogs... which makes this blog a bit of a controversial decision. The reason I don't like them is a) they are full of unstructured snippets of info, and b) people that blog seem to think I'll care about their private life.

Obviously these rules don't apply to some of the better bloggers, and I admit I'm writing this with my tongue fairly close to my cheek, but in general there is a lot of tat out there. I'll also admit to reading a number of blogs, so hear what I'm saying, but don't think I'm being too serious and over-zealous!

So, my aim of this blog is to provide a resource that people find using a search engine, and not to try and grow a fan base of loyal readers. I won't tell you if my cat has an operation, or I've been too busy working to blog. Nor will I develop any kind of story line throughout my posts. If you're looking for that, look elsewhere - to someone that's better at it than me! (one reason I won't be doing these things is basically that I'm useless at writing fiction or providing deep personal insight into my rather simple and comfortable life) .

On the other hand, if you find the information here useful, let me know. I hope it saves you some stress. Good luck!

(Note: I have noticed that there is another "bloghater" lurking around... It's nothing to do with me, and I apologise for plagiarising your name - it wasn't intentional)