Database monitoring on zero budget

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.

Leave a Reply

Your email address will not be published. Required fields are marked *