Well, still at it (20 hours after starting - yes, the time on this post is correct!). Really fustrating mostly, but one of those times coding where hours and hours of pain (literally in this case - flu and splitting headaches were involved) were followed by a sudden dawning then a solution. Well, I've learnt more about Load Testing and SQL Server performance today than I had in the previous 2 years, lessons I've learnt:

• SQL Profiler is god's own tool (not in any nasty sordid way, in a good way) - use it, love it! If you're running a load test, run Sql profiler at the same time...then look at the results, taking special note of reads and duration of queries...then...
• Use SQL Query Analyzer's 'show execution plan' option, look at the little graph carefully, looking for places where lots of non-indexed table scans are happening / a whole lot of work is going on where you don't expect it!#

It turned out that my problem was to do with a trigger firing on inserts, this updated a stats table which locked my main table - resulting in a bad Deadlock...as long as I had free connections it was fine, but the connections following the insert which queried the same data were getting hung because of the trigger's lock - so the number of connections racked up, eventually causing timeouts and other horridness...

Incidentally, I came across the rudest, most unhelpful Sales rep on the planet today (my opinion only!), a certain load testing tool company (the No.1 in the world apparently - god knows why!), I needed an eval license to do a quick run through of the load testing script my client was using - so I applied for an eval license from the website, downloaded and installed the tool - so far so good! Now, if this product was good enough, my company would probably have sprung for a license - problem is that they 'don't offer eval licenses in Europe' - implication was that I'd use it to rip them off. Alternative, spend an afternoon with their sales guy or spend $10000 on a product which I've never used (an afternoon would mean rouchly £5000 lost earnings for my company). My alternative was to use the free eval of another company's tool (won't mention the name yet in case I hate it) - so the first company have just lost a potential sale.

If you offer an eval download - especially one that requires you to install the software before you can even apply for a liocense - make damn sure you mention that there's not actually one available!