The difference was by varying @Code when under load.

I now think that the problem lies with Geospatial STDistance searches on the site. (I realise execution plans would be helpful, but need to get and anonymise first and I...

@mark-3 - Thanks for the pointers. See below for having potentially narrowed the problem down a little.

@TheSQLGuru - I would love to bring in a qualified consultant, and in an...

@drawlings - thanks for the advice - tempdb is only 1 file but there does not seem to be any contention there even under load, will consider splitting out the...

Thanks pavan. It's good to know this has worked for someone. It is just one of a number of changes that will be tested independently.

An update to the above. We are running SQL 2008 SP1 with no cumulative update patches (i.e. the build is 10.0.2531.0).

Any possible advice anyone could give on any of the...