SQL SERVER LOCKING ISSUES

Question

SQL SERVER LOCKING ISSUES

stanley_1982

Ten Centuries

Points: 1251
More actions
December 6, 2012 at 12:44 am

#267795

I found a blocking issues using the DMV .
[/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: IndexId1 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 .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

baabhu SSCertifiable Points: 6205 More actions · Answer 1

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 Ten Centuries Points: 1251 More actions · Answer 2

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 Ten Centuries Points: 1251 More actions · Answer 3

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 .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Guru Points: 55548 More actions · Answer 5

If the process is coming from another server, such as a LinkedServer query it can be trickier to figure out.