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?
output of stored procedure into temp table !! please see link below ..
If you are trying to capture any queries that take longer than 3 minutes - instead of using sp_whoisactive and trying to capture that data, I would recommend looking at extended events. You can setup an event that saves the data to a file and monitor for rpc_completed and sqk_batch_completed events that have a duration longer than your required time.
Thanks, this is exactly what I needed. For anyone else interested, this is the code I ended up with:
IF OBJECT_ID('tempdb..#tblTemp') IS NOT NULL
DROP TABLE #tblTemp
CREATE TABLE #tblTemp(
[dd hh:mm:ss.mss] [varchar](8000) NULL,
[session_id] [smallint] NOT NULL,
[sql_text] [xml] NULL,
[login_name] [nvarchar](128) NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[CPU] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[blocking_session_id] [smallint] NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[used_memory] [varchar](30) NULL,
[status] [varchar](30) NOT NULL,
[open_tran_count] [varchar](30) NULL,
[percent_complete] [varchar](30) NULL,
[host_name] [nvarchar](128) NULL,
[database_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[start_time] [datetime] NOT NULL,
[login_time] [datetime] NULL,
[request_id] [int] NULL,
[collection_time] [datetime] NOT NULL
@destination_table = #tblTemp
Thank you. I think this is probably the next step for me. I have to implement basic usage logging ASAP, but this seems to be the more standardized way to go about the problem.