March 30, 2011 at 8:56 am
Thank you so much for your help
March 30, 2011 at 11:43 am
Guys, this table is locked again and I don't know what is locking.
How can I find out?
Thank you
March 30, 2011 at 11:51 am
Start here:
SELECT * FROM sys.dm_exec_requests
You'll see what is running and you should be able to see what is locked. Look here
SELECT * FROM sys.dm_db_index_operational_stats(dbid, tableid,NULL,NULL,null) AS ddios
You can at least see what is specifically being accessed. That's just to get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 12:04 pm
yes i have one process running
March 30, 2011 at 12:06 pm
Is that process that is running the same as the one we already looked into, or is a new one?
using the spid of that process, use dbcc inputbuffer to find out what it is doing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2011 at 12:19 pm
CirquedeSQLeil (3/30/2011)
Is that process that is running the same as the one we already looked into, or is a new one?using the spid of that process, use dbcc inputbuffer to find out what it is doing.
inputbuffer? Ewww!
How about joining between sys.dm_exec_requests and sys.dm_exec_sql_text and use the statement offsets to pull out the current statement. You can also join to sys.dm_exec_query_plan to get the plan for the currently executing query. That's how I'd tackle it anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 12:37 pm
1.SELECT * FROM sys.dm_db_index_operational_stats(dbid, tableid,NULL,NULL,null) AS ddios
There is no tableid in this and incorect parameter value.
This is what I have from SELECT * FROM sys.dm_exec_requests
March 30, 2011 at 12:41 pm
I don't know if this is the same process, but the same table from this table is locked.
Thank you
March 30, 2011 at 12:53 pm
EventTypeParametersEventInfo
Language Event0 dbcc inputbuffer (139)
March 30, 2011 at 1:06 pm
I resolved the problem,but not sure for 100%
I ran update statment 2 hours on that table (I thought update was complited) in the query analyzer and tryed to close it just now and got message if I need to commited and I said yes and table is not locked anymore, why did I get this message?is that the reason table was locked?
when I run select statment get this error:
Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Thank you
March 30, 2011 at 1:31 pm
Grant Fritchey (3/30/2011)
CirquedeSQLeil (3/30/2011)
Is that process that is running the same as the one we already looked into, or is a new one?using the spid of that process, use dbcc inputbuffer to find out what it is doing.
inputbuffer? Ewww!
How about joining between sys.dm_exec_requests and sys.dm_exec_sql_text and use the statement offsets to pull out the current statement. You can also join to sys.dm_exec_query_plan to get the plan for the currently executing query. That's how I'd tackle it anyway.
Well, honestly it is a quick and dirty. I would typically go with the option that you proposed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2011 at 1:33 pm
Krasavita (3/30/2011)
I resolved the problem,but not sure for 100%I ran update statment 2 hours on that table (I thought update was complited) in the query analyzer and tryed to close it just now and got message if I need to commited and I said yes and table is not locked anymore, why did I get this message?is that the reason table was locked?
when I run select statment get this error:
Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Thank you
Yes that would be the reason. If you have an explicit transaction open - you need to close it by rolling it back or committing it. Otherwise you could end up locking the table for a long time such as you did.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2011 at 7:09 am
Krasavita, I am with Gail on this one: if this is an important production process that is blocking (and now deadlocking I see), you REALLY need to get a professional to get it fixed. You could go back-and-forth on a forum for many more days (2 already) and still not find out really what is happening where as a pro. tuner could find/fix the problem in a very short period of time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2011 at 7:15 am
Hi, here is another stored procedure I found with a problem with nested Loop.Can you please help me to identifiy which update statment is wrong.
Thank you so much
March 31, 2011 at 7:16 am
Have you rewritten the UPDATE statement? If so, can you post the new execution plan?
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply