Sql-server – How do query notifications detect changes in the query result

sys.dm_exec_requests is only able to display requests that are actually in flight at the precise moment sys.dm_exec_requests is executed. To capture all all requests that go by you need to hook into events, either XEvents or profiler events. Eg. capture RPC:Completed and SQL:BatchCompleted will capture all the queries executed (among other things).

There's no mystery, you get a good(er) or (really) bad plan at basically random because there is no clear cut choice for the index to use. While compelling for the ORDER BY clause and thus avoid the sort, you non-clustered index on the datetime column is a very poor choice for this query. What would make a much better index for this query would be one on (serial_number, test_date). Even better, this would make a very good candidate for a clustered index key.

As a rule of thumb time series should be clustered by the time column, because the overwhelming majority of requests are interested in specific time ranges. If the data is also inherently partitioned on a column with low selectivity, like it seems to be the case with your serial_number, then this column should be added as the leftmost one in the clustered key definition.