I have an application that lives in a SQL Server 2005 database. It builds dynamic SQL based on snippets of code stored in DB tables, and then executes that dynsql against two other databases to get a single number back for each query I run.
So, the general flow is that for location X, I lookup the bits of the first query, execute it against a remote database, and get a number back. Then I move onto the next query. Once I have executed all the remote queries, I update a DB table based on the results.
I have one entire class of locations that all use the same basic query with some variations in the where clause. The account numbers differ, things like that. Nothing big between any of the locations. And all of these locations work- except one.
For one location, partway through the chain of queries it executes, it hangs. If I pick it apart and run one query at a time, each query completes in a timely fashion. No one query hangs. If I start re-adding queries back in to the process, I find that at 10 (of 15) queries, it works fine. But when I execute eleven or more queries, it hangs. These queries all execute serially, so it's not a threading issue or something like that.
Now, here's the kicker: it doesn't seem to matter which 11 queries I execute. Any combination of 11 seems to blow up. And it's not the 11th query that hangs- it hangs on query number 6 pretty consistently. I have about 10 other locations that do the same exact thing, and they all work fine. It's just this ONE location that this happens to. The hang appears to happen when accessing our Oracle Financials system only, never the SQL Server Data Warehouse we get some of our data from. It looks like it's hanging on the remote system, not my local SQL Server.
Suffice to say, I and everyone I've shared this with is utterly perplexed. If it were something logical, like the last query being the source of the hang, or other locations also being problematic, or one specific query that was always failing, I'd be able to dig in and fix it. These inconsistent and irregular bugs are the absolute worst.
Your bonus mindbending physics: the first evidence that we may really be living in a 2D space.