none
SQL Server 2005 CPU bottleneck

    Question

  • Hi,

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

    Friday, April 15, 2011 3:57 AM

Answers

  • The default is for "automatically set processor affinity mask" to be checked so it sounds as though you may have unwittingly deviated from this configuration via your testing/experimentation.

    I am of the opinion that you should always leave this setting at the default unless you have a specific and proven reason for not doing so, for example on some older AMD technology it was recommended that each Scheduler be affinitised to each CPU core in a 1 to 1 mapping. This was to avoid issues with NUMA technology by disabling Schedulers from dynamically moving between cores in the same NUMA node.

    Also, the recording of context switches alone and in isolation is not necessarily an indicator of an issue. Don't act upon this metric alone by using it as the entire basis for your decision making.

    I suggest returning the "automatically set processor affinity mask" setting to the default in order to begin your testing/troubleshooting from the recommended configuration start point.

    What CPU technology are you using and which service pack have you installed on SQL Server 2005?

    Once done, I suggest looking to identify the queries that are consuming the most CPU and then subsequently understanding why. Once you have identified the high CPU consumers you can begin the process of performance tuning them. Often you can see high CPU consumption in queries that could benefit from a more optimal query plan such as via the creation of an index. 

    Take a look at the following post for guidance on how to identify these queries: How To Identify Most Costly SQL Server Queries Using DMVs

    Other options include running a Profiler Trace at the time you performance drop is experience in order to record the activity at this time.

    My preference is to begin a performance tuning effort at the software level (query performance in this case) before moving to investigate the hardware level.


    John Sansom | SQL Server DBA Blog | Twitter
    • Proposed as answer by Kalman Toth Friday, April 15, 2011 8:26 AM
    • Marked as answer by hooifong Thursday, April 21, 2011 1:51 AM
    Friday, April 15, 2011 6:10 AM

All replies

  • Can users only get the slot availability on thursday's? Or are they doing this throughout the week and it only spikes on thursday?

    More likely your issues are coming from the selects. Check for your DMV's for the top CPU bound queries. You can use these as starting point: http://sqlserverperformance.wordpress.com/2008/01/21/five-dmv-queries-that-will-make-you-a-superhero/

    You can also get a profiler trace and check that for CPU intensive queries and optimize these first.

    You either select each box at Procesor Affinity or you select Automatically set. You don't have to do both.

    Boost SQL Server priority will boost SQL but you could run into a situation where SQL gets more resources then the operating system. This will get you in more trouble then you asked for.

    Check the queries first, look at their showplans. The CPU load is coming from a query, not from the load (nr. of users).


    Robert Hartskeerl - http://sqlblog.hartskeerl.nl - http://twitter.com/rhartskeerl
    • Proposed as answer by Kalman Toth Friday, April 15, 2011 8:26 AM
    Friday, April 15, 2011 5:49 AM
  • The default is for "automatically set processor affinity mask" to be checked so it sounds as though you may have unwittingly deviated from this configuration via your testing/experimentation.

    I am of the opinion that you should always leave this setting at the default unless you have a specific and proven reason for not doing so, for example on some older AMD technology it was recommended that each Scheduler be affinitised to each CPU core in a 1 to 1 mapping. This was to avoid issues with NUMA technology by disabling Schedulers from dynamically moving between cores in the same NUMA node.

    Also, the recording of context switches alone and in isolation is not necessarily an indicator of an issue. Don't act upon this metric alone by using it as the entire basis for your decision making.

    I suggest returning the "automatically set processor affinity mask" setting to the default in order to begin your testing/troubleshooting from the recommended configuration start point.

    What CPU technology are you using and which service pack have you installed on SQL Server 2005?

    Once done, I suggest looking to identify the queries that are consuming the most CPU and then subsequently understanding why. Once you have identified the high CPU consumers you can begin the process of performance tuning them. Often you can see high CPU consumption in queries that could benefit from a more optimal query plan such as via the creation of an index. 

    Take a look at the following post for guidance on how to identify these queries: How To Identify Most Costly SQL Server Queries Using DMVs

    Other options include running a Profiler Trace at the time you performance drop is experience in order to record the activity at this time.

    My preference is to begin a performance tuning effort at the software level (query performance in this case) before moving to investigate the hardware level.


    John Sansom | SQL Server DBA Blog | Twitter
    • Proposed as answer by Kalman Toth Friday, April 15, 2011 8:26 AM
    • Marked as answer by hooifong Thursday, April 21, 2011 1:51 AM
    Friday, April 15, 2011 6:10 AM
  • When one user connecting to sql server it will allocate 8kb ram memory for each user.

    At that particular time connecting so many users.

    For cpu bottleneck the followings are the main reasons.

    1. no.of. user connection.

    2. long running queries

    3. blocking and deadlocks

    4. index fragmentation

     

    Friday, April 15, 2011 8:13 AM
  • 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.


    What happens exactly THU 8:30am? Some golf related publication?

     


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Friday, April 15, 2011 8:29 AM
  • Thanks guys for your valuable inputs. Today's booking went smoothly after I revert the setting to "automatically set processor affinity mask".

    I only realised there is difference between manually checking the affinity option for all CPUs and the check on the setting "automatically set processor affinity mask" after I read up the post below.

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/85a21a35-99c2-4633-912e-33f5ca1406b1

    Of course what I did doesn't resolve the slowness issues, we will continue to work on the query tuning to improve the performance.

    Thanks again to everyone.

    Thursday, April 21, 2011 1:51 AM