We have a MS SQL cluster 2005 with 4 CPUs dual core and currently host only 1 database instance. The web application that connected to this database is doing online booking for members to book golf game via internet. During normal day, the database is working
fine and the % processor time is very low which is not more than 10%. The CPU bottleneck happens only during every Thursday morning from 8.30am to 8.40am where all 16 processors spike up to 100%. During this 10 mins peak period, around 200 users connected
to database at the same time and the application trigger a lot of SELECT statement to query for golf slot availability. User has complained the online booking is very slow.
The perfmon has captured the System: Context Switches/Sec during that 10 mins has continuesly hit around 20k++. Uncertain whether could this be the reason that cause the CPU bottleneck, we have tested by reducing the processors to SQL Server to 12. The %
processor time for the 12 processors are 100% and the System: Context Switches/Sec drop to 13k. But it didn't help yet it worsen the slowness issue. Therefore we got no choice but to revert the setting to all processors to SQL Server. But after the revert,
the online booking doesn't go back to where it used to be. Users encountered a lot of system hang during the booking process. The perfmon shows that the % pro
View Complete Post