July 18, 2008 at 2:25 pm
Hello, I have a client that is complaining of blocking in their app. I have results from sp_blocker_pss80. I am not sure how to read and understand what this means. I ran it through Sherlock but ti say ther is NO blocking. Can someone please review this and help me understand it? Thank you kindly.
-Davis
8.2 Start time: 2008-07-18 16:10:52.783 63
SYSPROCESSES SVR-FNDDB1-FTM 134219768
spid status blocked open_tran waitresource waittype waittime cmd lastwaittype cpu physical_io memusage last_batch login_time net_address net_library dbid ecid kpid hostname hostprocess loginame program_name nt_domain nt_username uid sid sql_handle stmt_start stmt_end
------ ------------------------------ ------- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------- ---------------- -------------------------------- ----------- -------------------- ----------- -------------------------- -------------------------- ------------ ------------ ------ ------ ------ -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ ----------- -----------
106 sleeping 0 0 0x0000 0 AWAITING COMMAND WRITELOG 6232 24056 22 2008-07-18 16:10:52.800 2008-07-16 19:09:41.937 000000000000 TCP/IP 5 0 0 SVR-FNDAPP-FTM.store.chicos.com 0 icam 5 0x19E8045D04326B40A72139C268A02CCF00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0
ESP 233
SYSPROC FIRST PASS
spid ecid waittype
------ ------ --------
106 0 0x0424
Blocking via locks at 2008-07-18 16:10:52.783
SPIDs at the head of blocking chains
spid
------
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode Status TransID TransUOW
------ ------ ------ ----------- ------ ---- ---------------- ------------ ------ -------------------- ------------------------------------
106 0 5 0 0 DB S GRANT 0 00000000-0000-0000-0000-000000000000
ESL 660
DBCC SQLPERF(WAITSTATS)
Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ ------------------------
MISCELLANEOUS 143.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 45279.0 1.8303517E+8 181913.0
LCK_M_U 3638.0 4.2143808E+7 1309.0
LCK_M_X 109.0 27281.0 15.0
LCK_M_IS 3672.0 1.1458291E+7 6162.0
LCK_M_IU 1401.0 3507975.0 2524.0
LCK_M_IX 2263.0 1.2512356E+7 2119.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 744887.0 1.6238734E+8 1.6225728E+8
IO_COMPLETION 488491.0 840949.0 343.0
ASYNC_IO_COMPLETION 52.0 813.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 6988346.0 35656.0 1.3718515E+9
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 1091400.0 4.8391363E+8 1.6221773E+8
ASYNC_DISKPOOL_LOCK 1197.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 517974.0 1952955.0 110378.0
LOGBUFFER 374.0 2576.0 31.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 2870.0 2959.0 79.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 33497.0 9587.0 9336.0
CXPACKET 3118049.0 6.356664E+7 544298.0
PAGESUPP 227124.0 165661.0 37417.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 1.0 60015.0 60015.0
CURSOR 0.0 0.0 0.0
EXECSYNC 0.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 0.0 0.0 0.0
LATCH_UP 19.0 0.0 0.0
LATCH_EX 7944327.0 8973668.0 2919541.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 426.0 157.0 141.0
PAGELATCH_SH 6360703.0 1.2035038E+7 3391458.0
PAGELATCH_UP 15607.0 8007.0 3975.0
PAGELATCH_EX 1077333.0 303535.0 97628.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 446976.0 3479168.0 25623.0
PAGEIOLATCH_UP 1036.0 7222.0 16.0
PAGEIOLATCH_EX 437830.0 4240331.0 38551.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 370639.0 416432.0 0.0
Total 2.9925664E+7 9.9508717E+8 1.7037594E+9
(78 row(s) affected)
(78 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*********************************************************************
Print out DBCC Input buffer for all blocked or blocking spids.
*********************************************************************
DBCC INPUTBUFFER FOR SPID 106
EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------------------
Language Event 0 IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*******************************************************************************
Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.
*******************************************************************************
DBCC OPENTRAN FOR DBID 5 [ent_db]
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
End time: 2008-07-18 16:10:53.800
Best Regards,
~David
July 18, 2008 at 3:39 pm
David,
Ultimately you need to catch the blocking statement so that you can see what that is doing and then start to work through any tuning that is needed. There is a pretty cool little query here - http://a0002.blogspot.com/2008/06/return-query-text-along-with-spwho2.html - that will help with that if you can run this while the blocking is going on.
You could take the output from the script on that site and tweak to look just for blocking and insert results into a table. Schedule that in a job and let it run every minute overnight, etc. You get the picture.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 21, 2008 at 7:01 am
David, thank you for the input. Being that this is just for SS 2005 + I am forced to use sp_who2 and DBCC INPUTBUFFER(spid).
I would like to use a profiler trace to try and capture the actual SQL doing the blocking. The application that seems to be causing the blocking is a Java app. When I use the DBCC INPUTBUFFER command it just shows sp_executesql;1. I am hoping that I can actually capture the SQL through a trace. What is a good trace to set up to capture all necessary blocking information. I am using SS 2000.
Thank you for your time.
-David
Best Regards,
~David
July 28, 2008 at 1:20 pm
Did you try any of the scripts on here?
Also, you could download a trial of Idera's SQL Diagnostics Manager. It may or may not help you but it's been really valuable for me to go back and look at a specific time a user complained about.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply