Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL SERVER LOCKING ISSUES Expand / Collapse
Author
Message
Posted Thursday, December 6, 2012 12:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:36 AM
Points: 33, Visits: 455
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 .



Post #1393356
Posted Friday, December 7, 2012 1:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:38 PM
Points: 1,287, Visits: 1,117
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
Post #1393916
Posted Friday, December 7, 2012 2:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:36 AM
Points: 33, Visits: 455
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 ?
Post #1393935
Posted Friday, December 7, 2012 2:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:36 AM
Points: 33, Visits: 455
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 .
Post #1393941
Posted Friday, December 7, 2012 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1393945
Posted Friday, December 7, 2012 7:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
If the process is coming from another server, such as a LinkedServer query it can be trickier to figure out.


Post #1394046
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse