For most of my career I’ve been the only DBA, which means each and every time one of my colleagues encountered a perception that the database is slow, they would form the words “Hey Jeff. . .” As Stan Lee said, with great power comes great responsibility. Which is another way of saying in times of strife, both real and imagined, your Inbox and Voicemail get flooded.
One of the greatest tools ever imagined for a DBA is “sp_WhoIsActive“. It’s probably The Greatest Of All Time, but I know many of us use a multitude of tools and I don’t want to hurt anyone’s feelings. The reason I say G.O.A.T is because it does three things incredibly well. As the name says, it (1) shows you detailed information about all active queries, meaning (2) it can help you quickly isolate troublesome queries when “the database is slow,” which (3) allows you to keep your job.
Oh wait, one more thing: (4) it’s totally free! I’ve never met Adam Machanic, but if I did the least I could do would be to buy him a glass of his favorite beverage. His free tool has saved my bacon more times than I can count.
And yes, I realize it’s the fourth paragraph, so I’d better get to addressing the subject. As I noted, I’m accustomed to being the sole DBA, and if an employer only has the budget for a single DBA then it’s likely they don’t have a budget to purchase licenses for one of those wonderful monitoring tools like Sentry One, Diagnostic Manager, SQL Monitor, etc. Which means you need to come up with your own solution to answering the question why everything stopped working at 2am.
Because it’s always 2am when things stop working, right?
Anyhow, this script creates a table in master to track the info and a job to execute WhoIsActive every minute to capture activity. It retains the records for a week, so you may want to adjust that in the job if that is too much or little of what you need.
Important Note: my script does NOT include the actual sp_WhoIsActive stored procedure. You’re going to need to get that at whoisactive.com and install it in your master database before running this script.
USE [master] GO CREATE TABLE [dbo].[WhoIsActive]( [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, [tran_log_writes] [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, [query_plan] [xml] NULL, [used_memory] [varchar](30) NULL, [status] [varchar](30) NOT NULL, [tran_start_time] [datetime] 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 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO USE [DBA] GO CREATE CLUSTERED INDEX [cx_WhoIsActive_collection_time] ON [dbo].[WhoIsActive] ( [collection_time] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 4/5/2017 1:46:51 PM ******/ 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'DBA - WhoIsActive tracking', @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'REPSRV\_sqlcluster', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Log Activity] Script Date: 4/5/2017 1:46:51 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Activity', @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'DECLARE @destination_table VARCHAR(4000) , @msg NVARCHAR(1000) ; SET @destination_table = ''WhoIsActive''; DECLARE @numberOfRuns INT ; SET @numberOfRuns = 1 ; WHILE @numberOfRuns > 0 BEGIN; EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @destination_table = @destination_table ; SET @numberOfRuns = @numberOfRuns - 1 ; IF @numberOfRuns > 0 BEGIN SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + '': '' + ''Logged info. Waiting...'' RAISERROR(@msg,0,0) WITH nowait ; -- WAITFOR DELAY ''00:00:05'' END ELSE BEGIN SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + '': '' + ''Done.'' RAISERROR(@msg,0,0) WITH nowait ; END END ; GO -- remove records older than 7 days but adjust accordingly for your needs DELETE FROM DBA.dbo.WhoIsActive WHERE collection_time < dateadd(dd, -7, getdate())', @database_name=N'DBA', @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'Every Minute', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=1, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20170127, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'fbbf4d18-81e4-4777-a47a-f9bead4713c7' 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
Just like I’m not the only guy in Arizona who wears sunglasses, I know I’m not the only person rolling this solution. But in case you aren’t. . .enjoy.