Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL SERVER LOCKING ISSUES


SQL SERVER LOCKING ISSUES

Author
Message
stanley_1982
stanley_1982
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 478
I found a blocking issues using the DMV .

[url=http://postimage.org/image/4eibr8uvn/][/url]

from the captured result . I use DBCC IND('DBNAME', 'M_SYSTEM_CONFIG',-1), but i did not see the page number 1:1009316
I use dbcc PAGE (DBNAME, 1 , 1009316 ,3) WITH TABLERESULTS , it show me that the page Metadata: IndexId 1 and Metadata: ObjectId 645577338 . number 645577338 is the object id for table m_system_config . DBCC CHEDKDB show zero consistency error .

Is these normal to the database ? can someone explain how should i proceed to troubleshoot this blocking ? the only 2 statament that involve in table M_SYSTEM_CONFIG while blocking is update and select from a different rows . only 17 rows is in the table . i do not know select will trigger an exclusive lock .
baabhu
baabhu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1417 Visits: 1215
Setup a server side trace to identity what is causing the blocking. There are groups available for blocking in SQLserver. Hope this helps you to identify the blocking.
-- Babu
stanley_1982
stanley_1982
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 478
Unfortunately , the blocking happened again . and blocked process report capture nothing . I run the SOL SEVER report- activity -all blocking transaction during the blocking to show the blocking transaction .

blocking statement

key 59 --
key 51 update M_SYSTEM_CONFIG set sys_value = sys_value +1 where Getdate() > eff_start_dt and sys_key= 'LAST_USED_LOG_ID'

what kind of blocking is not able to trace by the profiler ? and why sql server showing -- in the blocking statement ?
stanley_1982
stanley_1982
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 478
The blocking occur intermittent

i have also set sp_configure 'blocked process threshold', 10. .

and when the blocking happened , i try to select other rows in the config table , it return the result .

But when i try to select the row which condition where Getdate() > eff_start_dt and sys_key= 'LAST_USED_LOG_ID' , it will keep loading because these particular key was blocked by the update statement .

But what have cause the blocked for these update statement ? I can see exclusive lock . but there are no other insert or update to the config table except the only one which blocked .
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 6.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


homebrew01
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 9071
If the process is coming from another server, such as a LinkedServer query it can be trickier to figure out.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search