April 14, 2011 at 9:16 pm
Hi,
We have a MS SQL cluster 2005 with 4 CPUs dual core & 16GB RAM 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 % processor time for the 16 processors are 60% in average and the System: Context Switches/Sec went up to 30k.
I'm totally clueless now. How do I get the SQL Server to at least go back to the stage before I did the processor changes? I checked the processor setting in SQL Server 2005 and noticed that even though I have all 16 processors checked in Processor Affinity and I\O Affinity, but the 2 checkboxes "automatically set processor or I/O affinity mask for all processors" are unchecked. Do I have to ensure these 2 checkboxes are checked? Will the "Boost SQL Server priority" help to resolve my problem if I turn it on?
Please help. Thanks.
April 15, 2011 at 12:50 am
Really guessing, but if it happens only on Thursdays and only between 8:30am and 8:40am, could it be that some scheduled job that is running during that time or some windows scheduled task?
(I know that would be too simple solution, but just to make sure it hasn’t been missed...)
April 18, 2011 at 5:29 am
Have you looked to see what queries are being run during these times? Sounds like they might be hitting parallelism. It's worth checking to see what they're doing and how in order to reduce the overhead to the system.
"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
April 19, 2011 at 6:40 am
1) you should never have messed with processor affinities. set back to using all automatically, not explicit ones.
2) The problem here isn't your CPUs, it is one or more of the following: schema design, indexing, application code, poor IO. You can hunt and peck on a forum for days or weeks trying to figure out what is wrong. Or you can hire a performance tuning professional to dig into the issue and most likely find the cause(s) and recommend fixes within a few hours.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply