SQL SERVER LOCKING ISSUES

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL SERVER LOCKING ISSUES

SQL SERVER LOCKING ISSUES

Rate Topic

Display Mode

Topic Options

Author

Message

stanley_1982

Posted Thursday, December 06, 2012 12:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:27 PM
Points: 29, Visits: 422

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

baabhu

Posted Friday, December 07, 2012 1:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:58 AM
Points: 1,112, Visits: 970

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

stanley_1982

Posted Friday, December 07, 2012 2:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:27 PM
Points: 29, Visits: 422

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

stanley_1982

Posted Friday, December 07, 2012 2:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:27 PM
Points: 29, Visits: 422

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

GilaMonster

Posted Friday, December 07, 2012 2:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 37,739, Visits: 30,014

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

homebrew01

Posted Friday, December 07, 2012 7:00 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,554, Visits: 7,212

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 »

Permissions