February 4, 2013 at 7:57 am
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
February 4, 2013 at 8:38 am
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.
February 4, 2013 at 9:49 am
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 ")
February 4, 2013 at 11:10 am
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
February 4, 2013 at 3:08 pm
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!
February 5, 2013 at 7:00 am
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
February 6, 2013 at 8:38 am
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.
.
February 6, 2013 at 10:02 am
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
February 6, 2013 at 10:22 am
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.
.
February 6, 2013 at 10:36 am
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
February 6, 2013 at 11:11 am
I'll have to look into that. I'm not familiar with those. Thanks!
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply