locking issues

[wbodger]

I have something odd going on and a having a hard time tracking it down. I manage our CRM, which is an older version of Onyx running on SQL Server 2000 SP4. Every now and then (not very often really, but twice a year is too much) we get these issues where random process start causing blocking issues and I get locks like 1222s when I try to get into Current Activity to see what is going on. I can still get into QA and run sp_who2, but that just tells me there is an issue (sometimes) and what the process id is. So, I can just kill it, but I would rather track down the issue and find out what is going on. But where do I start?

For instance, if you had an odd locking issue, what would be your steps in tracking down and isolating the problem?

Willie

 

[mrdenny]

The places which I would start are:

The code of the query which is causing the issues.
The execution plan of the query which is causing the issues.

Take a look at my FAQ faq962-6561. This procedure called sp_who3 will give you the full query being executed.

You can then check out the execution plan in Query Analyzer.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

That is part of the problem. I have not been able to identify a query that seems to be causing the issues. When I look at sp_who2 it might look like one query, but then it changes to another one and then it goes away, jsut to be replaced a few minutes later (or a few seconds), by another blocking issue.

For now I will go with this and see if I can find perhaps some processes that need to be tuned up a bit. Thank you for the new sproc, that looks to be very useful.

Willie

 

[mrdenny]

When using sp_who3 check out the blocked flag which will allow you to see all blocking and blocked processes in a single screen.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

Yes, thank you. Unfortunately (well, fortunately really) I have no blocking issues right now on which to test this, but I'm sure that will change before too long...