Monitor Blocking in SQL Server 2000 - Part 1

Wednesday Oct 20th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Part of Database Administrator's job is to monitor blocking in SQL Server. Learn how to monitor blocking and capture the SQL statements that are involved to a file.



Part of Database Administrator's job is to monitor blocking in SQL Server. By taking advantage of the Microsoft Knowledge Base article 271509, we can monitor the blocking and capture the SQL statements that are involved in the blocking to a file. This can be accomplished by updating the procedure from KB Article 271509 in such a way that it writes the output directly to a file.



Step 1



Create the procedure usp_writetofile, as shown in the figure Fig 1.1



Click for larger image

[Fig. 1.1]



Source Code



set quoted_identifier off
go
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_WriteToFile]') and 
   OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_WriteToFile]
GO
CREATE PROCEDURE Usp_WriteToFile
(@FileName varchar(1000), @Text1 varchar(1000)) AS
--Objective: To Write a given string on to a given file
--Created by: MAK
--Date: Sep 25, 2004
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Error: Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT 'Error: OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Error : WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Download code here.

Step 2

Create the procedure usp_blockmonitor, by executing the below SQL statement [Fig 1.2]. Download Code from here.


[Fig 1.2]

Step 3

Create a job Job_BlockMonitor by using Job_BlockMonitor.SQL as shown in Fig 1.3 and Fig 1.4. This job creates a schedule that causes this job to run every 5 minutes.


[Fig 1.3]


[Fig 1.4]

Source Code to create the job Job_blockmonitor

-- Script generated on 9/24/2004 10:37 PM
-- By: MAK
-- Server: SQL

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Job_BlockMonitor')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Job_BlockMonitor'' since there is 
	  already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Job_BlockMonitor' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , 
     @job_name = N'Job_BlockMonitor', @owner_login_name = N'sa', 
  @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', 
     @enabled = 1, @notify_level_email = 0, 
  @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, 
     @step_name = N'JOb_BlockMonitor', @command = 
  N'usp_blockmonitor 1,0,''d:\BLOCKING.LOG''
', @database_name = N'master', @server = N'', @database_user_name = N'', 
   @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, 
@retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', 
   @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, 
@on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

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

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Job_BlockMonitor', 
     @enabled = 1, @freq_type = 4, 
  @active_start_date = 20040923, @active_start_time = 0, @freq_interval = 1, 
     @freq_subday_type = 4, 
	 @freq_subday_interval = 5, 
  @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, 
     @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download Code here.

Step 4

Execute the job "Job_BlockMonitor" as shown in Fig 1.5

Click for larger image

[Fig 1.5]

Step 5

Since we gave "d:\blocking.log" as the File Path in the job step, it creates the file on D:\ with blocking details as shown in Fig 1.6

8 No Waittypes: 
  2004-09-24 22:36:54.030 30 SQL
8 No Waittypes: 
  2004-09-24 22:37:42.857 16 SQL
8 No Waittypes: 2004-09-24 22:40:00.140 0 SQL
8 No Waittypes: 2004-09-24 22:43:32.763 0 SQL
8 No Waittypes: 2004-09-24 22:44:24.140 0 SQL
8 No Waittypes: 2004-09-24 22:44:49.640 0 SQL
8 No Waittypes: 2004-09-24 22:45:00.547 0 SQL


SYSPROCESSES SQL  134218488
ESP 1063

SYSPROC FIRST PASS
No blocking via locks at 
  2004-09-24 22:45:50.810

DBCC SQLPERF(WAITSTATS)
MISCELLANEOUS,0.00000,0.00000,0.00000
LCK_M_SCH_S,0.00000,0.00000,0.00000
LCK_M_SCH_M,1.00000,188.00000,0.00000
LCK_M_S,25.00000,6468.00000,79.00000
LCK_M_U,0.00000,0.00000,0.00000
LCK_M_X,0.00000,0.00000,0.00000
LCK_M_IS,0.00000,0.00000,0.00000
LCK_M_IU,0.00000,0.00000,0.00000
LCK_M_IX,0.00000,0.00000,0.00000
LCK_M_SIU,0.00000,0.00000,0.00000
LCK_M_SIX,0.00000,0.00000,0.00000
LCK_M_UIX,0.00000,0.00000,0.00000
LCK_M_BU,0.00000,0.00000,0.00000
LCK_M_RS_S,0.00000,0.00000,0.00000
LCK_M_RS_U,0.00000,0.00000,0.00000
LCK_M_RIn_NL,0.00000,0.00000,0.00000
LCK_M_RIn_S,0.00000,0.00000,0.00000
LCK_M_RIn_U,0.00000,0.00000,0.00000
LCK_M_RIn_X,0.00000,0.00000,0.00000
LCK_M_RX_S,0.00000,0.00000,0.00000
LCK_M_RX_U,0.00000,0.00000,0.00000
LCK_M_RX_X,0.00000,0.00000,0.00000
SLEEP,102219.00000,102267936.00000,102267872.00000
IO_COMPLETION,366.00000,57577.00000,79.00000
ASYNC_IO_COMPLETION,26.00000,10828.00000,0.00000
RESOURCE_SEMAPHORE,0.00000,0.00000,0.00000
DTC,0.00000,0.00000,0.00000
OLEDB,35111.00000,1943038976.00000,92694416.00000
FAILPOINT,0.00000,0.00000,0.00000
RESOURCE_QUEUE,25068.00000,306792736.00000,102271544.00000
ASYNC_DISKPOOL_LOCK,540.00000,0.00000,0.00000
UMS_THREAD,0.00000,0.00000,0.00000
PIPELINE_INDEX_STAT,0.00000,0.00000,0.00000
PIPELINE_LOG,0.00000,0.00000,0.00000
PIPELINE_VLM,0.00000,0.00000,0.00000
WRITELOG,2083.00000,29616.00000,251.00000
PSS_CHILD,0.00000,0.00000,0.00000
EXCHANGE,0.00000,0.00000,0.00000
XCB,0.00000,0.00000,0.00000
DBTABLE,0.00000,0.00000,0.00000
EC,0.00000,0.00000,0.00000
TEMPOBJ,0.00000,0.00000,0.00000
XACTLOCKINFO,0.00000,0.00000,0.00000
LOGMGR,0.00000,0.00000,0.00000
CMEMTHREAD,0.00000,0.00000,0.00000
CXPACKET,0.00000,0.00000,0.00000
PAGESUPP,0.00000,0.00000,0.00000
SHUTDOWN,0.00000,0.00000,0.00000
WAITFOR,0.00000,0.00000,0.00000
CURSOR,0.00000,0.00000,0.00000
EXECSYNC,0.00000,0.00000,0.00000
LATCH_NL,0.00000,0.00000,0.00000
LATCH_KP,0.00000,0.00000,0.00000
LATCH_SH,0.00000,0.00000,0.00000
LATCH_UP,0.00000,0.00000,0.00000
LATCH_EX,0.00000,0.00000,0.00000
LATCH_DT,0.00000,0.00000,0.00000
PAGELATCH_NL,0.00000,0.00000,0.00000
PAGELATCH_KP,0.00000,0.00000,0.00000
PAGELATCH_SH,1347.00000,516.00000,516.00000
PAGELATCH_UP,926.00000,552.00000,536.00000
PAGELATCH_EX,173.00000,30.00000,30.00000
PAGELATCH_DT,0.00000,0.00000,0.00000
PAGEIOLATCH_NL,0.00000,0.00000,0.00000
PAGEIOLATCH_KP,0.00000,0.00000,0.00000
PAGEIOLATCH_SH,1047.00000,47477.00000,281.00000
PAGEIOLATCH_UP,363.00000,8713.00000,126.00000
PAGEIOLATCH_EX,215.00000,6816.00000,0.00000
PAGEIOLATCH_DT,0.00000,0.00000,0.00000
TRAN_MARK_NL,0.00000,0.00000,0.00000
TRAN_MARK_KP,0.00000,0.00000,0.00000
TRAN_MARK_SH,0.00000,0.00000,0.00000
TRAN_MARK_UP,0.00000,0.00000,0.00000
TRAN_MARK_EX,0.00000,0.00000,0.00000
TRAN_MARK_DT,0.00000,0.00000,0.00000
NETWORKIO,1787.00000,13312.00000,0.00000
Total,171297.00000,2352281856.00000,297235744.00000

*********************************************************************
Print out DBCC Input buffer for all blocked or blocking spids.
*********************************************************************

DBCC INPUTBUFFER FOR SPID 60
Language Event
0
DBCC CheckDB([ship])


[Fig 1.6]

Conclusion

This article has covered how to take advantage of the Microsoft Knowledge Base article 271509 to monitor blocking and capture the SQL statements that are involved in blocking to a file. This was accomplished by updating the procedure from KB Article 271509 in such a way that it writes the output directly to a file.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved