I have a finished query, but wonder if there's a better way to do this.
I have set up a job on SQL Server Agent to run once per minute to check sp_whoisactive. Any queries which are more than 3 minutes old are logged & each time the agent, runs, that query is evaluated to see if it has changed/updated & updates my final table.
The query can be found here: https://pastebin.com/B9AbfT9J
However I have a few issues with it. I could not figure out how to output a stored procedure to a variable table or temporary table. So I had to go with the snippet of code that I found which outputs the values to an actual table.
Also, I am only interested in the rows which have run for more than 3 minutes. This process first writes all of the processes to an interim table, then deletes impertinent rows, then attempts to merge the rest & only lastly purges the interim table. Lastly, sometimes it takes 1 second to run, other times it's closer to 7. I have no idea why.
This works, which I'm happy to have, but I wonder if I'm committing crimes against SQL here. Does anyone have any feedback?