请教各位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.
