October 7, 2005 at 2:23 pm
Hi all,
We had a production downtime for about 10 hours. After opening system up for user access, very high CPU load and lot of blocking caused very slow system performance.
We addedd some extra drives on our SAN for more space, no changes in code or schema occured. Ultimately, problem just went away after 2 hrs.
I have Perfmon stats captured from the same timeframe week earlier for comparison:
Baseline: CPU 15 % , Disk Queue 1.7, 12 Checkpoints/sec, 3 KB Granted WS Mem., 34 lock wait time (table), 20 SQL Compilations
Problem: CPU 70 %, Disk Queue 12, 24 Checkpoints/sec,529KB Granted WS Mem., 8051 lock wait time (table), 43 Compilations
Batches/sec and User Conections are similar among the two so the utilization was around the same.
Has anyone experieced this after downtime or SQL server restart?
Can the double the compilation rate cuase this?
How come CPU activity is high while blocking occurs (shouldn't it be lower since transactions are waiting)?
Queue lengths are high, nuber of spindles is 4+4 in Raid 1+0.
How about that granted WS memory being much higher (but available MBs is almost the same)?
Can the cause of such bad performance be the fact that all data pages were cached out and all SP's need to recompile after the restart?
Any help would be appreciated.
Thanks,
Dragos.
October 10, 2005 at 11:11 am
The more compilations have an impact on the cpu usage. Cpu usage increased due blocking seems plausible: maintaining & checking locks, managing queues...
Mostly when my server is restarted it gets under heavy load because of everyone using it at the same time, catching up + restart of batches that didn't finish. Also tempdb grows.
Any rolled forward/backwards transactions?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply