April 26, 2007 at 7:30 am
Hello,
How does one go about diagnosing a situation where on occasion a SQL 2000 database server locks up and anywhere from one to dozens of processes get blocked? In some cases, I am seeing processes blocking themselves, and although sometimes the blocking resolves itself, at other times the processes remain blocked and require manual intervention to kill the blocking process.
I'm not sure there's anyway to completely prevent this kind of problem, but at least I would like to know what is causing the situations that don't resolve themselves and at least minimizing or eliminating those. Specifically, I want to know how one can log the blocking activity and isolate any specific queries or procedures that are causing the blocking and (if possible) the series of processes that led up to the blocking.
Thanks in advance for any help.
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 27, 2007 at 6:50 am
code using improperly disposed cursors can cause blocking. Study your procedures.
...
-- FORTRAN manual for Xerox Computers --
April 27, 2007 at 7:02 am
This might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
April 27, 2007 at 10:06 am
Processes do not really block themselves as such. In Sp4 they made a change so things look like they do ...
April 27, 2007 at 10:20 am
The process is not really blocking itself - one thread of the process is blocking another thread of the same process. There will be one thread that is either the root or blocked by another process. You can follow the blocking chain to find the root. Once you examine the root, you'll know what type of problem you have. If the root is idle, an open transaction is likely the problem - perhaps an old application has a window open that locks data. (We have an old OMNIS program that does this. It will be replaced soon.) I've seen badly written queries result in parallism, CXPACKET waits, and many locks. These queries can finish quickly or take many minutes, depending on resources. Perhaps somebody is running a large ad hoc report or, worse, using EM to look at data.
It's not always easy to be watching at just the right time. I've set up a job to check each minute for blocking > 15 seconds - very little overhead. It will log process and lock info if there is a blockage. Another process sends an email with the logged process and lock info. It's handy.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
April 27, 2007 at 10:37 am
Thanks to everyone for the advice.
rstone - would you be willing to share the details of the job you created that runs every 15 sconds and logs/emails info? At least that might allow me to see whether there is one query that is the same culprit over and over again, or if it is different ones (we have many systems and many I did not write myself but are written by other parties even though they run on our servers). The logging you describe sounds like it would let us know what we are up against.
I also found this link that I intend to read through as soon as possible.
http://www.sql-server-performance.com/articles_audit.asp
Thanks again!
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 27, 2007 at 12:47 pm
Much of the code has been hacked from stuff I've found here on sqlservercentral. It would only be fair for me to share it; however, I will need to check with my boss.
I've created a command file to do the install - we also have many servers. It installs a "DBA" database and three jobs. One job monitors for blocking and logs blocking info. One job logs waitstats info. The last job handles email of blocking reports (blocking, blocking change, and blocking resolved). An attachment includes process and lock info for the root and blocked processes. The processing time is generally between ~10 ms (no blocking) and ~ 300ms (blocking). I scheduled the blocking monitor job to run each minute. The monitor will only alert if there is at least one blocking lasting more than 15 seconds.
Send me a private message - so I can have your email address - and I'll see what I can do next week. If it works out (and the code's clean enough), perhaps I can put out in the public domain.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
April 30, 2007 at 7:45 am
Hi,
Incidentally, I am facing the EXACT issue webrunner is facing and right now I am searching for the same script that you offered in your last post. Have you been able to release the script?
Regards,
Khurram
Visit SK Web Technologies
"I try to do what i can do when i can do it."
"If we fight, we may not always win, but if we don't fight, we will surely lose..."
April 30, 2007 at 8:34 am
I'm not sure I totaly agree with you guys on processes not being able to block themselves. A process can show up as blocking iteself when there is network or disk bottlenecks. You'll need to look at the process wait type to see what is causing the block. It's obvious that you are made aware when these situations arise as you've stated that you must take manual action. The next time this happens, run SQLDiag.exe prior to killing the offending process. You can then refer to the locking/blocking section of the output and see which process is the head blocker. You'll also be able to look at the locks that process has and the last statement that process has submitted to SQL Server.
May 1, 2007 at 1:11 pm
I'm working on it in my spare time - which is short today.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
May 1, 2007 at 1:20 pm
There is always a bottleneck. Waiting on resources is normal in any process that is not blocked. The root blocker can be idle, running, or waiting on a resource and not be blocked. The process is blocked only if all its threads are blocked - not just waiting. At least one of the threads will not be blocked by another thread in the same process. If a blocking chain does become closed (e.g., no unblocked root), SQL Server issues a deadlock. I've never heard of a deadlock within one process. Within one process, locks are not relevant because all threads belong to the same transaction. Perhaps a badly behaving resource could do it? SQL Server would have to be able to detect the deadlock within the resource.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
May 1, 2007 at 6:40 pm
I know the feeling. Looking forward to getting the script if you can post it.
Thanks again!
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 6, 2007 at 11:37 am
Hi!
Self blocking is beacuse the process need to wait
for it own processes (paralel processes,
pages-i/o disk bottleneck) check out the
syslockinfo system table and the lastwaittype column.
-This link can be helpful to understand waittype.
Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
http://support.microsoft.com/kb/822101
-Another useful link: it does report the
locking activity on your sql, simple sp that
can be run as a schedule trace.
How to monitor blocking in SQL Server 2005
and in SQL Server 2000
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply