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

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.

1_SQL_Server_Realtime_Monitoring_using_WMI_classes_Part_1

  • 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

,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_bat

ch,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 Chaudhary

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook

   

28 Comments on “SQL Server Real-time Monitoring using WMI classes: Part -1”

  1. Hey Abhay – nice one and a coincedence too..was working on the same topic for my next blog post.. Smile

  2. Hi Abhay,

    This is a nice one. I have tried out many different things to capture blocking info when it happens and finally after lots of try had to run a job every minute which checks whether blocking is there for more than 1 minute and email the DBA’s with the snapshot of sysprocesses. But your trick sounds better. I will surely try this in our environment. Just thinking if you blogged this 3-4 months earlier , I would have saved lots of my time trying different things which never worked 😉

    Ashwin

  3. Great article on the T-SQL way of doing this. I am not an expert (not even a beginner) in the SQL space. S, this TSQL code looks very intimidating to me. I prefer doing the same in PowerShell. It is equally powerful and lets me do it outside of the box I am monitoring and remotely. PowerShell eventing lets you take advantage of this and you can event run scripts remotely as a response to an event.

    For example, Get-WMIObject -Namespace root\Microsoft\SqlServer\ServerEvents\SQLEXPRESS -Query “SELECT * FROM meta_class WHERE (__THIS ISA ‘__Event’)” all the SQL event classes for the Express Instance I have on my laptop. Now, using the event classes and Register-WMIEvent cmdlet, we can easily create a monitoring solution that responds to an event and does something.

    As I said, I am not an expert in SQL area. So, I don’t really know why I have to use T-SQL as compared to PowerShell for WMI event monitoring. Just sharing my 2 cents! I will see if I can quickly blog a part of your solution in PowerShell.

    Ravi

  4. Thanks Ravi . You are bang on the target .In my next blog I will be discussing on using different SQL Server related and OS related (using simple WQL) classes to create any kind of alert we want, through SQL Server .This will use the TargetMachine, ISA ,etc clauses … I have infact a monitoring solution ready (written in VB .Net ) and working that we use to deploy these scripts on standalone and centralized monitoring instances .This becomes even more fruitful if we use it through an instance that is used only for monitoring other instances on different servers(i.e. Centralized monitoring instance).

    Instead of using SQL Server we can use (I can use actually ) VB scripts to do the same .However, integrating it with SQL had the benefit of not only capturing the alert but also take the actions based on this like running jobs that captures the data in the table , sending mail using database mail (rather than using CDO objetcs ) .SO this basically gave me all the options at one place .Will try to cover as much as I can .

    Waiting eagerly for the powershell blog(s) 🙂 .

    Kind Regards

    Abhay

  5. Aah, I see. Nice to know your plans. Using PowerShell 2.0, you can do everything in one place. Send-MailMessage and other cmdlets make it possible!

  6. Nice post

    In order to obtain deadlock graph by whatever means, youll need to have one of the traceflag 1204 or 1205 or 1224 turned on.

  7. Incorrect 🙂 ……..You can use deadlock_graph class .There is no need to use any traceflag …Wait for my future blogs 🙂 ..

    Kind Regards

    Abhay

  8. Hi ..

    Thanks for posting wonderful script to detect deadlocks .I am working on this script .

    getting one error when the job starts run

    “Unable to start execution of step 1 (reason: Variable WMI(HostName) not found). The step failed.”

    Any Idead about this ?

  9. Nice article. I have designed the same solution for my new company last year that has both Blocking and Deadlock detection in place.

    The beauty of this solution is that you do not have to enable any deadlock trace flags…

    Just 2 cents from my side, in sp_add_alert, you are better off adding a delay for e.g 5 mins (300 Secs) or whatever fits in your environment by using @delay_between_responses=300. This way you dont receive unnecessay emails Smile

    HTH,

    Kin

  10. Thanks Kin ….In this solution you dont need to use the delay_between_responses option because 1) I have kept the blocking threshold to 5 seconds , and 2) I have kept the duration to less than 8 seconds .Now what happens is that internally the blocking setection thread wakes up every 5 seconds and + some time that it might take for the overhead .This is the reason I have kept the extra buffer of 3 seconds in case the server is busy or slow.So anyone will get only ONE alert for ONE blocking ….these 2 values can always be modified …

    Initially I was wanting to use the delay_between_responses .But that will send you redundant blocking mails (chances are less) + during the 5 minutes wait (300 seconds) if any blocking occurs you might not get the response …. So ideally it will wake up every 5 minutes to deliver the blocking information if at that time its happeneing ..whereas this solution will send you mail for every blocking for sure ..

    Kind Regards

    Abhay

  11. If you read my second post , I have mentioned the deadlock example as well .And yes , you are right ; there is no need of any traceflag ..

    Kind Regards

    Abhay

  12. Hi there: Getting same msg that was already reported

    Unable to start execution of step 1 (reason: Variable WMI(SQLInstance) not found). The step failed

  13. 1 : Have you enabled the token replacement policy ?

    2: What is the SQL Sevrer version and build ?

    3:Did you try again after restarting SQL Server agent …

  14. Abhay,

    one more thing …

    SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration = as the duration is the amount of time (in milliseconds) that the process was blocked.

    So you what all the spids that are blocking for more than certain value.

    HTH,

    Kin

  15. Kin ,

    The whole idea was to capture the blocking which is greater than 5 seconds.This is controlled by keeping the blocked process threshold value to 5 in sysconfigurations via sp_configure .The reason I have kept the duration of 8 seconds is because I only want this blocking to be captured ONCE and not more than that .If you set this value to 10 or 11seconds then there is a very high chance of getting redundant blocking alerts .You can test this yourself .The reason of keeping it higher than sp_configure values is this : you may have blocking happening but to detect that blocking there is a background SPID that needs to wake up every 5 seconds (due sp_configure setting ) ..I have seen that sometimes Blocking is detected a bit late due to the fact that blocking capturing spid initiated might be after the 1st or 2nd second of actual blocking .But since 5+2 is 7 we are still with in the range as we have kept it to 8 second .This way the chances of missing a blocking alert is almost NIL ..

    HTH

    Kind regards

    Abhay

  16. Hi Abhay,

    Thanks for sharing this piece of great work. Just a recommendation:

    @server is derived from SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’). What would happen if I run it on a sql cluster? I set it up on the active node of a a sql cluster checked the alert’s properties. It shows the physical hostname of the active node. Now if there is a failover to the passive node, the name space wouldn’t be correct. I don’t have a lab sql cluster to test and thought you might have an answer.

    Thanks.

  17. Hi Abhay,

    Thanks for sharing this piece of great work. Just a recommendation:

    @server is derived from SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’).

    What would happen if I run it on a sql cluster?

    I set it up on the active node of a a sql cluster checked the alert’s properties.

    It shows the physical hostname of the active node. Now if there is a failover to

    the passive node, the name space wouldn’t be correct. I don’t have a lab sql

    cluster to test and thought you might have an answer.

    Thanks.

  18. HI Jan, You have a valid point …I need to test this on a cluster …But we can add a check for this in the code (should’nt be a problem) …IN the mean time run it on both the nodes and when the instance fails over the alert will still work ..

    Regards

    Abhay

  19. Hi Abhay, Thanks for one more beautiful post. I am a regular follower of your blogs and also attended recent webcast on transactional replication you have conducted.

    I see this post very promising to the DBAs. I would like to know the impact of these WMI alerts on production machines. Do we need to take care of anything from that point of view. please advise.

  20. Moin ,

    Thanks for your kind words …

    In my environment ,I wrapped several such WMI scripts + Reports + Capacity mgt reports with a VB .net frontend tool .I can say that one of the client is monitoring around 60+ instances through a centralized SQL Server with 4GB RAM , 1 Quad core CPU and 50 GB Storage .I normally take this as a benchmark..The resource consumprion should be much less as comparision to the third party tools .

    Kind Regards

    Abhay

  21. Just out of curiosity why do all this and maintain such a heavy script ?

    Cant we just enable block thresold using sp_configure and create a event notification which will trigger a mail if a block occurs.Same thing can be done for deadlocks also.Hardly 4 to 5 line code.

  22. Sachin,

    It makes sense to just create an alert on the event and get notified .I have already mentioned if certain part of the code is not needed it can be comented out .However , I as an admin would never want an email alone stating that there is a deadlock or blocking .I would always prefer to know more about who is causing blocking , who are the head blokers , What are the resources like page , extent , row , table ,etc participating in blocking .Is the stats of the participating tables out of date or have less sampling .Are the indexes of the participating tables fragemnted …Once we know that there is blocking , we will do all these things to find out the root cause and that is the reason that I have added all these options here for the community members .Its up to them what to pick and what not to pick ..for example ,In our enviornment where people talks in TBs , we cannot afford to find the index fragemntation and hence I dont use it ..

    Kind Regards

    Abhay

  23. I am definately not trying to say here that only a notification(email) would suffice.We can do more than that.The whole default blocked process report can be sent out in a mail through event notification which gives you more than enough information to deep dive the causes of blocking.

    Between never heard that index fragementation also causes blocking.Yes it does hamper performance but never saw/heard a direct relationship beetwen blocking and index fragementation.

  24. I think a ping pong has started which is not useful..Can you give me your email address so that I can send you a detailed mail to address your concerns.

    Kind Regards

    Abhay

Leave a Reply

Your email address will not be published.