SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help to diagnose LCK_M_IX Query


Help to diagnose LCK_M_IX Query

Author
Message
n00bDBA
n00bDBA
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 467
Hi all,

I have a query that is stuck (running for few hours but normally takes 2-5 mins)..

Id like to find out why its basically stuck! and see what can be done to not have it happen again. (its happened a few times now)

Any one know a tutorial or willing to go step by step though it with me?

Not sure what info would be needed.. I have sp_whoisactive so know some info..

Query:


INSERT INTO VPX_EVENT WITH (ROWLOCK)
(EVENT_ID, CHAIN_ID, EVENT_TYPE, EXTENDED_CLASS,
CREATE_TIME, USERNAME, CATEGORY, VM_ID,
VM_NAME, HOST_ID, HOST_NAME, COMPUTERESOURCE_ID,
COMPUTERESOURCE_TYPE, COMPUTERESOURCE_NAME, DATACENTER_ID,
DATACENTER_NAME, DATASTORE_ID, DATASTORE_NAME, NETWORK_ID,
NETWORK_NAME, NETWORK_TYPE, DVS_ID, DVS_NAME, CHANGE_TAG_ID
)
VALUES
(@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14,
@P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24
)



Basic info:

session_id 160
sql_text
login_name vmware_admin
wait_info (33604359ms)LCK_M_IX
CPU 0
tempdb_allocations 0
tempdb_current 0
blocking_session_id 490
reads 0
writes 0
physical_reads 0
used_memory 2
status suspended
open_tran_count 2
percent_complete NULL
host_name VMWare




Hope you can help
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51007 Visits: 10844
Whenever I have a wait stats question, I look up Paul Randall's list: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

According to this page:

This is simply the thread waiting for a lock to be granted and indicates blocking problems. These could be caused by unwanted lock escalation or bad programming, but could also be from I/Os taking a long time causing locks to be held for longer than usual. Look at the resource associated with the lock using the DMV sys.dm_os_waiting_tasks. Don’t assume that locking is the root cause.


From using the DMV he mentions, you should be able to be able to determine the session and figure out what's in contention for the row.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
n00bDBA
n00bDBA
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 467
hi Ed,

Thanks for the reply..

still digging around trying to resolve it, possibility missing the point! (cant see the wood though the trees)

But using something like



USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO



I get 2 LCK_M_IX that just wont go away it seems.

What do people like to do in this situation.. I know that one query is a agent job. So could kill it and re-run the code? Or is there more i can do?

As a side. could anyone explain what the WAIT_RESOURCE is and how to use it? (They both have the same : "OBJECT: 10:181575685:0 ")
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51007 Visits: 10844
So you probably have a deadlock situation - both pieces of code are waiting on a lock to the same object. I believe the X in the last part of the name denotes an exclusive lock, as opposed to a shared lock, which would mean a shared lock.

If it were me, I would kill one and let the other run. Then, if necessary, re-run the first. There are probably some better ways of dealing with it than this, but this is what I would do.

If the code is waiting on an exclusive lock to the whole page, it can also result in other updates to rows in the same page being blocked. I would address it as soon as possible. The hard part comes in determining why the lock occurred in the first place.

As for the wait resource 10:181575685:0:
10 = the file of the object being waited for
181575685 = in the file, the page of the object being waited for
0 = in the page, the row being waited for

Paul Randall has an excellent post on MSDN about using RID to obtain information. He explains far more than I ever could hope to, so I'm going to defer to his wisdom on it. http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx.

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
n00bDBA
n00bDBA
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 467
Thanks for the reply Smile

Its a not a deadlock, one is blocking another query. not blocking each other (ie session 160 is blocking 230. 230 is blocking 400)..

So is the wait resource saying that they both need access to a page that is currently used by something else?

Checking out the link now..and doing examples.. hopefully will shed some light on things..

Thanks again for the help!
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41420 Visits: 14413
If it were a deadlock scenario it would be sorted by the database engine (one would be chosen as a victim and be killed) in fairly short order.

You'll want to see what the thread at the head of the blocking chain is doing. If you're using sp_whoisactive then look for the blocking_session_id column and trace along the chain until you find the culprit. Check the sql_text and the wait_info columns to see what that session is running. If you want to open things up that is the session you would kill, but obviously that is a sledgehammer approach and as it sounds this is starting to become a recurring issue so best to get to the bottom of the issue.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 599
Correct me if i'm wrong, you could also use the below script to trace back to what's being blocked by whom.

select * from sysprocesses
order by blocked desc

Then run the below script to view what the actual blocking spid is doing. (replace 87 with the real blocking spid)

select * from sysprocesses p
cross apply sys.dm_exec_sql_text(sql_handle)
where p.spid = 87

then if needed you could kill the blocking spid if the proccess can be safely removed.

kill 87

This would also work when you have issues with activity monitor loading... or when you're unlucky enough to be in an environment where you cannot add sp_WhoIsActive.

.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41420 Visits: 14413
Why use sysprocesses here, a deprecated feature of SQL Server? One other side note, if you're not in master then you have to fully-qualify it as sys.sysprocesses in SQL 2008.

sp_whoisactive is meant to replace a lot of the need to open Activity Monitor. If you haven't tried it I would highly recommend it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 599
opc.three (2/6/2013)
sp_whoisactive is meant to replace a lot of the need to open Activity Monitor. If you haven't tried it I would highly recommend it.


I agree, though we cannot add sp's in our environment. so we're a bit behind on that specific side sadly.

.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41420 Visits: 14413
I have run into that too. Are there restrictions on compiling permanent or temporary stored procs into tempdb?

If permanent procs are disallowed you could change the CREATE portion of the proc definition to one of these:

-- only available in your session
CREATE PROC #sp_WhoIsActive



-- available to more than your session
CREATE PROC ##sp_WhoIsActive



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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