Snippet Name: envwhotrack
Tags: Connection Activity
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM
–desc: creates process to track db usage via sp_whoisactive for a while
Dependencies: This snippet assumes you have installed Adam Machanic’s sp_whoisactive, and leverages some parameters or techniques to drill down into a specific scope.
default description
Sample Output:
--desc: creates process to track db usage via sp_whoisactive for a while
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
/*
DECLARE @s VARCHAR(MAX)
EXEC sp_WhoIsActive
-- @output_column_list = '%',
@get_outer_command=1,
@get_full_inner_text=1,
@return_schema = 1,
@schema = @s OUTPUT
SELECT REPLACE(@s,'CREATE TABLE <table_name>','IF OBJECT_ID(''[dbo].[whoisactiveCapture]'') IS NOT NULL DROP TABLE [dbo].[whoisactiveCapture];CREATE TABLE [dbo].[whoisactiveCapture]')
select @s
EXECUTE (@s)
*/
USE master;
GO
IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name = 'sp_whoisactive capture')
EXECUTE msdb.dbo.sp_delete_job @job_name = 'sp_whoisactive capture'
IF OBJECT_ID('[dbo].[whoisactiveCapture]') IS NULL
EXECUTE ('
CREATE TABLE [dbo].[whoisactiveCapture]
(
[dd hh:mm:ss.mss] VARCHAR (8000) NULL,
[session_id] SMALLINT NOT NULL,
[sql_text] XML NULL,
[sql_command] 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]
@get_outer_command=1,
@output_column_list ='[dd hh:mm:ss.mss],[session_id],[sql_text],[sql_command],[login_name],[wait_info],[CPU],[tempdb_allocations],[tempdb_current],[blocking_session_id],[reads],[writes],[physical_reads],[used_memory],[status],[open_tran_count],[percent_complete],[host_name],[database_name],[program_name],[start_time],[login_time],[request_id],[collection_time]',
@get_full_inner_text=1,
@show_sleeping_spids = 1,
--@show_system_spids = 1,
@destination_table = '[dbo].[whoisactiveCapture]'
IF OBJECT_ID('[dbo].[sp_whoisactive_results]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_whoisactive_results];
GO
--#################################################################################################
--CREATE PROCEDURE [dbo].[sp_whoisactive_results]
--#################################################################################################
CREATE PROCEDURE [dbo].[sp_whoisactive_results]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
;;WITH T1
AS
(
SELECT
session_id,
CONVERT(VARCHAR(MAX),sql_text) AS sql_text,
login_name,
host_name,
MIN(collection_time) AS mintime,MAX(collection_time) AS maxtime
FROM [dbo].[whoisactiveCapture]
WHERE 1=1
AND sql_text IS NOT null
AND [collection_time] > DATEADD(hh,-24,GETDATE())
GROUP BY session_id,CONVERT(VARCHAR(MAX),sql_text),login_name,host_name
--ORDER BY mintime DESC
)
SELECT ROW_NUMBER() OVER (ORDER BY mintime DESC) AS RW,
DATEDIFF(MINUTE,mintime,maxtime) AS ElapsedMinutes,
ElapsedTime = dt.days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
[T1].[session_id],
login_name,
host_name,
[T1].[sql_text],
[T1].[mintime],
[T1].[maxtime]
--[ss].[spt],
--[ss].[ept],
--[dt].[Days],
--[dt].[Hours],
--[dt].[Minutes],
--[dt].[Seconds],
--[fl].[FileName],
--[tm].[LogFileTimeEST],
--[lf].[LogTime]
--INTO #delphixresults
FROM T1
CROSS APPLY(SELECT [Days] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(DAY, maxtime - mintime) -1),2),
[Hours] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(HOUR, maxtime - mintime)),2),
[Minutes] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(MINUTE,maxtime - mintime)),2),
[Seconds] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(SECOND,maxtime - mintime)),2)
) dt
WHERE 1=1
END --Proc
GO
--#################################################################################################
-- create job sp_whoisactive capture
--#################################################################################################
/****** Object: Job [sp_whoisactive capture] Script Date: 3/26/2020 10:54:33 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 3/26/2020 10:54:33 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'sp_whoisactive capture',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [EXEC sp_WhoIsActive] Script Date: 3/26/2020 10:54:34 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'EXEC sp_WhoIsActive',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [sp_WhoIsActive]
@output_column_list =''[dd hh:mm:ss.mss],[session_id],[sql_text],[sql_command],[login_name],[wait_info],[CPU],[tempdb_allocations],[tempdb_current],[blocking_session_id],[reads],[writes],[physical_reads],[used_memory],[status],[open_tran_count],[percent_complete],[host_name],[database_name],[program_name],[start_time],[login_time],[request_id],[collection_time]'',
@get_outer_command=1,
@get_full_inner_text=1,
@show_sleeping_spids = 1,
--@show_system_spids = 1,
@destination_table = ''[dbo].[whoisactiveCapture]'';',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'fifteen secs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20200324,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'2019e61f-79b4-43cf-822f-4499e9c4e487'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO