May 14, 2008 at 7:56 am
Guys,
I was trying track wait types which are causing blocks and eventually timeouts, I found the possible wait types. The wait type that was causing timeouts in LCK_M_S.
The listed below are lock wait types, are there implications of Latch Wait types on the performance.
DO you guys know what are the implications of each wait types and which wait type needs to be monitored.
Any suggeestions/inputs would help.
LCK_M_SCH_S
LCK_M_SCH_M
LCK_M_S0x03
LCK_M_U0x04
LCK_M_X0x05
LCK_M_IS
LCK_M_IU
LCK_M_IX
LCK_M_SIU
LCK_M_SIX
LCK_M_UIX
LCK_M_BU
LCK_M_RS_S
LCK_M_RS_U
LCK_M_RI_NL
LCK_M_RI_S
LCK_M_RI_U
LCK_M_RI_X
LCK_M_RX_S
LCK_M_RX_U
LCK_M_RX_X
Thanks
May 14, 2008 at 8:39 am
there is a nice article on books online here http://msdn.microsoft.com/en-us/library/ms190615.aspx that might help you sorting it out
May 14, 2008 at 11:25 am
Hi
Hmmm waittypes and queue's very important to understand and if you do then it is a great performance tuning factor ..
Looking at your list of waittypes it is relatively unclear what is happening...to look at waittype issues we need to see more info ..but what I have seens is
LCK_M_S 0x03
LCK_M_U 0x04
LCK_M_X 0x05
Lck_MS_S is a shared lock
Lck_M_U is a update lock
Lck_M_X is a exclusive lock
You will need to look at the waittimes for these waittypes and waitresorces to point out problems ....looking at the other waittypes it seems that an update statement is causing your issue
what I suggest you do is use PSSDIAG and enable the blocker stuff , download a tool by the name of sherlock and analyse it ..
For sql 2005 you can enable the blocker process in sp_configure
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;
GO
set the threshold to 5 seconds ...will give you enough info
The start a profiler trace
Under the
errors and warning eventclass select blcoker process report
locks
Locks timeout > 0
also in the same breath check for long running queries and CPU intensive queries...also never forget to do perfmon it is vital that is why I am suggesting PSSDIAG ..you can download it from microsoft or 20005 has in build-in pssdiag.exe .....
Good article to read for waittypes:
ww.sqlskills.com ...go to white papers and look for Performance_Tuning_Waits_Queues.doc
Also look for SQL Server 2000 Wait Types on the net
May 15, 2008 at 6:33 am
Thanks CoetzeeW - I will play tool to know more.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply