Better way to collect sp_whoisactive data?

Transact-SQL
1

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?

2

hi

output of stored procedure into temp table !! please see link below ..

3

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.

1 Like
4

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
	)
EXEC [sp_WhoIsActive]
    @destination_table = #tblTemp
5

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.