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 12»»

Help to diagnose LCK_M_IX Query Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
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
Post #1415317
Posted Monday, February 4, 2013 8:38 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,053, Visits: 3,490
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
Post #1415349
Posted Monday, February 4, 2013 9:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
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 ")



Post #1415384
Posted Monday, February 4, 2013 11:10 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,053, Visits: 3,490
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
Post #1415415
Posted Monday, February 4, 2013 3:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Thanks for the reply :)

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!
Post #1415510
Posted Tuesday, February 5, 2013 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1415813
Posted Wednesday, February 6, 2013 8:38 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.


.
Post #1416552
Posted Wednesday, February 6, 2013 10:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1416623
Posted Wednesday, February 6, 2013 10:22 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
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.


.
Post #1416635
Posted Wednesday, February 6, 2013 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1416638
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse