s**m
发帖数: 1564 | 1 请教各位DBA一个关于SQL Server database 的问题。
One night, many processes were suspended. There were ~5 root blocking
processes, which blocked tens of other processes in chain. It happened
overnight but nobody was notified in a timely manner.
Next morning, I had to kill those rooting processes and a few other
processes to bring database/application back to run again. I wish I had more
time to investigate before killing those processes.
1. Was it in a deadlock state at the worst time of that incident overnight?
2. Does it affect the whole database or only the part involved in deadlock?
3. After killing those blocking process, later I still noticed one or two
process showing suspended occasionally for 1-2 minutes and went through. Is
it normal to have some processes suspended? Should it be concerned for such
short-time suspended process?
4. What could be the cause?
5. If a simple select statement queries a large core table inside a
transaction but the transaction is not committed for a period of time, can
it cause other update/insert/delete operations to this core table to be
suspended, and then cause further deadlock?
6. If (5) is true, the deadlock may still exist even if initial select
operation is committed later, is this correct?
7. Does SQL Server itself chooses a deadlock victim and then break deadlock?
8. Is (7) done by configuration setting or through programmatically in
application or database?
9. Is it possible to find what database operation causing such problem after
many hours? How?
10. How to find out immediately and automatically?
11. What can be done to solve the problem immediately after it occurs? How
about running a script to detect blocking process and/or deadlock and then
kill it after waiting for some time? How soon should a blocking process
should be killed after after it is detected?
12. What can be done to improve database quality to avoid this kind of
problem?
Thanks. |
s**********o
发帖数: 14359 | 2 显然你做DBA不久吧，SQL SERVER 所有的 SELECT QUERY包括STORED PROCEDURE都要用
WITH NOLOCK，否则长时间的READ也可以BLOCK其它的UPDATE INSERT，UPSERT一旦被
BLOCK住，LOCK就会ESCLATE的。不是有PROFILER吗，还有其它的MONITOR TOOLS比如
QUEST的spotlight或者idera sql diagnostic manager |
s**m
发帖数: 1564 | 3 能不能把original post里的问题解答一下。
谢谢
SQL Server 2005之前这种说法也不总对吧，查了一下，好像SQL Server 2005及以后更
不推荐这种做法了吧？
否则长时间的READ也可以BLOCK其它的UPDATE INSERT，UPSERT一旦被
我猜测这是可能的原因之一。怎么证明呢？
不是有PROFILER吗，还有其它的MONITOR TOOLS比如
Profiler 需要在问题正在发生的时候才能有帮助吧？
【在 s**********o 的大作中提到】
: 显然你做DBA不久吧，SQL SERVER 所有的 SELECT QUERY包括STORED PROCEDURE都要用
: WITH NOLOCK，否则长时间的READ也可以BLOCK其它的UPDATE INSERT，UPSERT一旦被
: BLOCK住，LOCK就会ESCLATE的。不是有PROFILER吗，还有其它的MONITOR TOOLS比如
: QUEST的spotlight或者idera sql diagnostic manager
|
s**********o
发帖数: 14359 | 4 自己去看书吧
【在 s**m 的大作中提到】
: 能不能把original post里的问题解答一下。
: 谢谢
:
: SQL Server 2005之前这种说法也不总对吧，查了一下，好像SQL Server 2005及以后更
: 不推荐这种做法了吧？
: 否则长时间的READ也可以BLOCK其它的UPDATE INSERT，UPSERT一旦被
: 我猜测这是可能的原因之一。怎么证明呢？
: 不是有PROFILER吗，还有其它的MONITOR TOOLS比如
: Profiler 需要在问题正在发生的时候才能有帮助吧？
|
