Sql-server – ny reasonable way to determine what executed a query

Here's my suggestion, which is somewhat different. I call it "log, aggregate, and snapshot."

Basically all your sensor input is coming in. We can assume or even require that it gets into the database within a specified period of time (1 day?). At the close of each interval we go back one interval into the past and snapshot our analytic info. In this way we can start with a snapshot before where we need and roll forward.

For example we might do this in a financial app (not really the same thing but poses similar challenges over time):

CREATE TABLE gl ( 
   id bigserial not null unique,
   reference text primary key
   description text,
   date_posted date not null
);
CREATE TABLE gl_lines (
   entry_id bigserial,
   account_id int not null references account(id),
   gl_id bigint not null references gl(id),
   amount numeric
);
CREATE TABLE eoy_checkpoint (
   date_ending date,
   account_id int references account(id)
   running_balance numeric not null,
   debits numeric,
   credits numeric
   primary key (date_ending, account_id)
);

Then we can maintain eoy_checkpoint when we close our books. We can also have a trigger that denies entry into gl_lines where it occurs on or before the most recent date in eoy_checkpoint. This allows us to still do aggregate reporting, but we can use the checkpoint as a point we can roll forwards from, and thus manage how much data is actually being aggregated.

I think a similar approach would be usable in your case. It handles this sort of thing very well, and allows a mixture of OLTP and more complex queries to be run without too much impact. It isn't really clear how normalized this is since there is duplication of data, but the data duplication allows you to do things like purche historical data without impacting your current running totals.

These times where your connection is forced closed is usually an indication of throttling. I have found that the main reason for throttling is from high numbers of outstanding requests within your database. Unfortunately there are no hard limits that can be monitored to identify when this throttling occurs. The good news is that the lower limits have been documented which means you can track when you are getting close to the upper limits.

To give a plug to our Cotega monitoring service, this is one of the main things we track to allow people to be notified when they approach these upper limits. Another useful thing to do when this happens is to do a deep database diagnostics to see what expensive queries, connections, etc are occurring within your database as described on How to Perform SQL Azure Performance & Diagnostics Analysis for SQL Azure.