September 16, 2009 at 7:41 am
Today I ran an 'emergency' script to fix a problem in production. The script does 40,000 updates, each of which fires a trigger that does 1 insert. I should have disabled the trigger first, but didn't (mistake).
This is a very slow (Pentium 4) server, but it has 2 CPUs. MaxDOP is set to 1 (disabling parallel execution), and hyperthreading is turned off in the BIOS.
After 5 minutes, I realized my mistake not dropping the trigger, and canceled my job, effectively rolling back the changes. I waited another 15 minutes, and as expected the CPU usage of the machine was at 50% during this time. I later found out it was not 1 CPU being used 100% as I expected, but both CPUs actually were used 50%, which I still don't understand. During this time, no one was able to use SQL Server on this machine to do anything, which was also unexpected.
I suggested rebooting, saying SQL would continue doing what it is doing after it rebooted, so logically this makes no sense, but I hate just waiting and not doing anything. After the reboot, I expected the database to take forever to recover, and I came back online almost instantly.
Can anyone tell me what happened and why?
September 16, 2009 at 9:14 am
Not being there, it's really hard to know after the fact, but on a guess... You had contention that was slowing down the rollback process. Then, when you rebooted, the contention was clear and the rollback as part of the recovery was able to proceed, quickly & easily.
But that's speculation.
"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
September 16, 2009 at 10:27 am
Speculation is appreciated.
I don't think this was contention however. Soon after the problem started, no one could do anything, so they mostly stopped trying. We also shut down everything that could access SQL Server about 5 minutes before we rebooted.
September 16, 2009 at 10:34 am
Did you look at the server to see if there were other processes running, other connections with locks?
"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
September 16, 2009 at 10:41 am
It could be blocking. That would explain the "not much happening"
September 16, 2009 at 11:11 am
As I said, no one could do anything with sql server, including checking for locks or other activity.
No other processes were using more than 1% of the CPU.
September 16, 2009 at 11:33 am
Ah, well, hard to know then, but I'd still be inclined to some kind of blocking. It makes the most sense based on the evidence. If it happens again, and I hope it doesn't, try using the admin connection to get in to the server.
"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
September 16, 2009 at 12:34 pm
Is there a possibility a single user running a single query could cause significant blocking/locking issues? I would think SQL Server takes care of this.
I do know something else could have been running that blocked this.
September 16, 2009 at 12:43 pm
Especially when you're talking about some huge long running query, a little bitty thing could cause contention with it, yeah. It's entirely possible that one process caused the other problems.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply