Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 20, 2004

Monitor Blocking in SQL Server 2000 - Part 1 - Page 2

By Muthusamy Anantha Kumar aka The MAK

Step 4

Execute the job "Job_BlockMonitor" as shown in 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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM