SQL Server Real-time Monitoring using WMI classes: Part -1

We all know that that SQL Server had a great face lift from SQL Server 2000 to 2005 .Everything else was a history after that .It was a big leap and we can count the new features of SQL Server 2005 on fingers. However there is one feature that I feel should have been marketed but was not done.

Starting from SQL Server 2005, we can use SQL Server agent to capture WMI alerts .While this is catching up fast in the SQL Server community, many still do not know what magic this functionality can do and how useful this is.

Let’s say you are facing blocking on one of your very busy database. But how will you know this? When will you know this? What information will you collect? .I have noticed it many time that by the time the DBA team is involved the blocking fades away (I am not saying always).If not we generally find our less skilled DBAs struggling in finding the root cause, until things really go out of control and the senior DBA is called upon at mid night .He joins the call and asks for the so called history of this issue, which frustrates the stake holders … this is a common story my friends. There are lots other.

In this post I will show you how you can leverage SQL Server Agent and WMI to detect the Blocking and send you a mail with blocking graph as an attachment (using database mail) to give you a complete understanding of the reason behind blocking. Even though I wanted to add some more details related to WMI in this post but let me first begin with the demonstration and we will talk about this ni later posts (This reminds me of the dialogue in the movie The Good , The Bad and the Ugly “When you want to shoot , just shoot , don’t talk”) .

Before we begin,

• Make sure your database mail is working and has an account associated with the Profile.
• Make sure you are on SQL Server 2005 SP3 or above .I have seen some issues with SQL Server 2005 SP2 and below .You might find some bugs also if you BING .So far I have not found any issues on Build 3054 (which is SP2 + some CU) but it’s better to be on SP3 .
• You should have admin rights before you deploy the scripts.
• Ensure that Token Replacement option is checked in the Agent properties :

  SQL Server Agent >> Alert System >> Token Replacement >> Check the Replace tokens for all job responses to alerts opton.

• Set the blocked process threshold value in sys configurations to 5 using sp_configure.This means that we want to capture any blocking that exceeds 5 seconds.

We are all set now to deploy the following script:

/* Step 1: creating the table to capture the Event information */
If not exists (select name from sys.sysdatabases where name='MYALERTS ')
create database MYALERTS  
go

USE MYALERTS  
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BLOCKED_PROCESS_REPORT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BLOCKED_PROCESS_REPORT]
GO

CREATE TABLE [dbo].[BLOCKED_PROCESS_REPORT] (
[PostTime] [datetime] NOT NULL ,
[SQLInstance] varchar(50),
[Databaseid] int,
[computerName] Varchar(50),
[SessionLoginName] Varchar(50),
[SPID] int,
[TransactionID] int,
[EventSequence] int,
[objectID] int,
[IndexID] int,
[TextData] nvarchar(4000) ,
[duration] int,
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_BLOCKED_PROCESS_REPORT_Flag]  DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [BLOCKED_PROCESS_REPORT_IDX01] ON [dbo].[BLOCKED_PROCESS_REPORT]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO


/*Step 2 : Creating the Job that will enter values into the Deadlockevents table    created above*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture BLOCKED_PROCESS_REPORT Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture BLOCKED_PROCESS_REPORT Event', @delete_unused_schedule=1

GO
/*Service account and sql operator option are optional*/
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N'<job_owner_account>'
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N'<sql_agent_operator>'

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

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'Capture BLOCKED_PROCESS_REPORT Event', 
@enabled=1, 
@notify_level_eventlog=2, 
@notify_level_email=3, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'Job for responding to blocking events', 
@category_name=N'[Uncategorized (Local)]', 
--@owner_login_name=@ServiceAccount, 
--@notify_email_operator_name=@SQLOperator, 
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


/*Step 3: Insert blocking graph into the table we created above*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@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'

INSERT INTO BLOCKED_PROCESS_REPORT (
[PostTime] ,
[SQLInstance] ,
[Databaseid] ,
[computerName],
[SessionLoginName],
[SPID] ,
[TransactionID] ,
[EventSequence] ,
[objectID] ,
[IndexID] ,
[TextData],
[duration]
)

VALUES (
GETDATE(), 
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
N''$(ESCAPE_NONE(WMI(Databaseid)))'',
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(SessionLoginname)))'',
N''$(ESCAPE_NONE(WMI(SPID)))'',
N''$(ESCAPE_NONE(WMI(TransactionID)))'',
N''$(ESCAPE_NONE(WMI(EventSequence)))'',
N''$(ESCAPE_NONE(WMI(objectid)))'',
N''$(ESCAPE_NONE(WMI(indexid)))'',
N''$(ESCAPE_SQUOTE(WMI(Textdata)))'',
N''$(ESCAPE_NONE(WMI(Duration)))''
)', 
@database_name=N'MYALERTS ', 
@flags=0


/*Step 4: Adding the job step 2 for sending mail and changing the flag for the mail sent to 1 so that the same blocking graph is not sent twice*/

declare @command1 nvarchar (200)
set @command1='use MYALERTS' +'; exec BLOCKED_PROCESS_REPORT_rpt;exec [dbo].[flag_BLOCKED_PROCESS_REPORT];'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sending mail and changing the flag value', 
@step_id=2, 
@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=@command1

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_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


/* STEP 5:Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to BLOCKED_PROCESS_REPORT')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to BLOCKED_PROCESS_REPORT'

GO
declare @count int
declare @instance nvarchar(50)
declare @server nvarchar(50)
DECLARE @server_namespace varchar(255)
select @instance=convert(nvarchar(50),(SERVERPROPERTY('instancename')))
select @server=convert(nvarchar(50),(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')))
IF (@instance is not NULL)
	SET @server_namespace = N'\\'+@server+'\root\Microsoft\SqlServer\ServerEvents\' + @instance
ELSE
	SET @server_namespace = N'\\'+@server+'\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'


EXEC msdb.dbo.sp_add_alert @name=N'Respond to BLOCKED_PROCESS_REPORT',
@enabled=1, 
@notification_message=N'Your Message', 
@wmi_namespace=@server_namespace, 
@wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration <=8000000', 
@job_name='Capture BLOCKED_PROCESS_REPORT Event' ;

--EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to BLOCKED_PROCESS_REPORT', @operator_name=N'Test', @notification_method = 1
--GO

/* Step 6: Create a stored proc for capturing blocking graph */ 
 use MYALERTS 
 go
 
 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[blk_text]') AND OBJECTPROPERTY(id, N'Isprocedure') = 1)
 DROP proc  [dbo].[blk_text]
 go
 create proc blk_text as
 print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 print 'Following is the blocking graph'
 print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 select textdata from MYALERTS..BLOCKED_PROCESS_REPORT where flag=0
 print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 print 'END of blocking graph'
 print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 go
 
 /* Step 7: Create a stored proc for capturing Last Stats update and Index fragmentation of the tables involved in blocking */ 
 
 /*set nocount on,include column headers in the resultset */
 use MYALERTS 
 
 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Stats_index]') AND OBJECTPROPERTY(id, N'Isprocedure') = 1)
 DROP proc  [dbo].[Stats_index]
 go
 Create procedure Stats_index as 
 set nocount on   
 print 'Following are the Statistics and Index fragmentation details of the object(s) involved in Blocking'
 print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[stats_state]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 DROP TABLE MYALERTS.[dbo].[stats_state]
 create table MYALERTS..Stats_state (spid bigint,dbid bigint,objid bigint,indid bigint,type varchar (100),resource varchar(200),mode varchar(100),status varchar(100))
 insert into MYALERTS..Stats_state exec sp_lock 
 
 declare contig_Cursor CURSOR FOR select distinct j.objid from MYALERTS..stats_state i inner join MYALERTS..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait'
 FOR READ ONLY
 open contig_Cursor
 declare @spid bigint
 declare @i_buff_string char(30) 
 declare @obj_id bigint
 declare @obj_name nvarchar(50)
 declare @obj_name_temp nvarchar(50)
 declare @syntax nvarchar(100)
 declare @syntax1 nvarchar(100)
 declare @dbid int;
 declare @dbname nvarchar(3000);
 select distinct @dbid = j.dbid from MYALERTS..stats_state i inner join MYALERTS..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait'
 select @dbname=db_name(@dbid)
 
 fetch next from contig_Cursor into @obj_id 
 
 while (@@fetch_status <> -1) 
 begin 
 select distinct @dbid= j.dbid from MYALERTS..stats_state i inner join MYALERTS..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='wait' and j.objid=@obj_id
 set @dbname = db_name(@dbid)
 select @obj_name= object_name(@obj_id)
 
 print 'Show conting output'
 Print '~~~~~~~~~~~~~~~~~~~~'
 SELECT database_id 'dbid',object_id 'objid',index_id 'indid',index_type_desc 'indtype',avg_fragmentation_in_percent '% frag',avg_page_space_used_in_percent 'page_full%',page_count 'page_cnt' FROM sys.dm_db_index_physical_stats(@dbid, @obj_id, NULL, NULL , 'LIMITED');
 
 print' 
 
 '
 
 print 'Last Stats update for the above object was :'
 Print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
 set @dbname='use '+@dbname +';
 
 declare contig_2_Cursor CURSOR FOR select distinct j.objid from MYALERTS..stats_state i inner join MYALERTS..stats_state j on i.resource = j.resource where i.resource =j.resource and i.status ='+'''wait''
 FOR READ ONLY
 open contig_2_Cursor
 declare @obj_id int;
 declare @obj_name nvarchar(300);
 
 fetch next from contig_2_Cursor into @obj_id 
 while (@@fetch_status <> -1) 
 begin 
 
 select distinct @obj_id = j.objid from MYALERTS..stats_state i 
 	inner join MYALERTS..stats_state j on i.resource = j.resource 
 	where i.resource =j.resource and i.status ='+'''wait;''' +'
 select @obj_name =object_name(@obj_id);select "Index Name" = i.name, "Statistics Date" = STATS_DATE(i.object_id, i.index_id)
 FROM sys.objects o JOIN sys.indexes i ON o.name = ' +'@obj_name'+ ' AND o.object_id = i.object_id
 where i.name is not null;
 
 fetch next from contig_2_Cursor into @obj_id
 end 
 close contig_2_Cursor
 deallocate contig_2_Cursor
 '
 --print @dbname
 exec(@dbname)
 
 fetch next from contig_Cursor into @obj_id
 end 
 close contig_Cursor
 deallocate contig_Cursor
 go
 

/*Step 8: Creating SP to capture SP_WHO , Head blocker and sp_lock information.Also adding above SP here to be executed  */
 use MYALERTS 
 go
 IF OBJECT_ID('blk_information','P') IS NOT NULL DROP PROC dbo.blk_information
 GO
 CREATE PROC dbo.blk_information 
 AS
 print ' blocking List:'
 Print '~~~~~~~~~~~~~~~~'
 Select SPID,Blocked,Waittype,Waittime,lastwaittype,program_name,waitresource,dbid,last_batch,open_tran from sys.sysprocesses where blocked <>0
 PRINT ''
 
 Print ' Head blockers List'
 Print '~~~~~~~~~~~~~~~~~~~~'
 select spid as [Blocking spid],loginame,hostname,program_name as progname,cmd,status,physical_io,waittype
 from master.dbo.sysprocesses 
 where spid in (select blocked from master.dbo.sysprocesses)
 and blocked=0
 PRINT ''
 
 declare @spid bigint
 declare @i_buff_string char(30) 
 set nocount on   
 declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where blocked <> 0 OR spid in (select blocked from master.dbo.sysprocesses)
 FOR READ ONLY
 open bufCursor
 fetch next from bufCursor into @spid 
 while (@@fetch_status <> -1) 
 begin 
    Print   'SP_WHO'
    select '~~~~~~~~' 
    exec sp_who @spid 
    PRINT ''
     
    PRINT  'SP_LOCK' 
    select '~~~~~~~~'  
    exec sp_lock @spid 
    PRINT ''
    
 fetch next from bufCursor into @spid 
 end 
 close bufCursor
 deallocate bufCursor
 go


/* Step 9: Create a stored proc for sending the deadlock information as .txt file */
USE    MYALERTS  
GO 
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.BLOCKED_PROCESS_REPORT_rpt') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP proc dbo.BLOCKED_PROCESS_REPORT_rpt
go
Create proc [dbo].[BLOCKED_PROCESS_REPORT_rpt] 
as
DECLARE @Server_name nvarchar(30)
DECLARE @instance_name nvarchar(30)
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
DECLARE @subject1 nvarchar (1000)
select @date= convert(varchar,GETDATE())
--select @date= datepart(day,GETDATE())
SET @SQL='MYALERTS'
SET @SQL = 'print ''


'' use  MYALERTS ;set nocount on; exec blk_text;exec blk_information;Exec Stats_index'
--SET @SQL = 'select * from BLOCKED_PROCESS_REPORT where flag = 0'
SET @File = 'BLOCKED PROCESS Report'+@date+'.txt'
Select @server_name=host_name()
Select @instance_name=@@servicename
set @subject1='Blocking report for ->'+'Server: '+ @server_name+' and Instance: '+@instance_name

 
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'TestPfl',
@recipients = 'abhay.chaudhary@in.ibm.com',
@subject = @subject1,
@body = '***URGENT***Attached please find the BLOCKED PROCESS report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767 

go


/* Step 10: Changing the flag to 1 so that next time this information is not sent*/ 
use MYALERTS  
go
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.flag_BLOCKED_PROCESS_REPORT') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP proc dbo.flag_BLOCKED_PROCESS_REPORT
go
create proc [dbo].[flag_BLOCKED_PROCESS_REPORT] as 
update    MYALERTS .dbo.BLOCKED_PROCESS_REPORT set flag = 1 where flag = 0
go

Thats It ....

Now you have a mail in your inbox that has :

• Blocking graph that give you the complete blocking history.Its just like a deadlock graph .You dont need to run Trace all the time and there is no trace flag for capturing blocking .
• Head blocker list .(You can comment out this code if you don't need it )
• information of sp_who and sp_lock of the spids involved in blocking .(You can comment out this code if you don't need it )
• Information of the index fragmentation and last update stats date for the objects involved in blocking . (You can comment out this code if you don't need it )

So next time you dont have to ask too much becasue you have the blocking history straight to your inbox .

Hope you have enjoyed this post .In the next post I will share some more facts and tips on Realtime monitoring through SQL server Agent and WMI ..

As always , your feedback is most welcome .

Regards

Abhay

Join us on FaceBook at  http://www.FaceBook.com/SQLServerGeeks