SQL Server 2005 CPU bottleneck | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server 2005 CPU bottleneck

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 % 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 IO 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.

Welcome to the forums!.
Is SQL dedicated?
Is there any job runing during that time?.
Did you run Profiler to capture what queries are runing?

What is it from web page that initiated on SQL side, I mean to say Dynamic queries or stored procedures? I have seen similar problem when the t-sql code was not fully qualified means proc1 is called from web page as exec proc1 rather you should qualify with owner name too so it should be exec dbo.proc1.
This will reduce your compilation lock and I am pretty sure 8:30AM to 8:40AM, peak time for your app’s make it this behaviour worst. there are lots others to consider for CPU bottlenecks but I have experienced similar issue and it was compilation lock on object for us.
let us know.


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |