locked
My cpu is using >80% RRS feed

  • Question

  • In SS2k5 My cpu is using >80% can I reduce it to 40% without restart

    Dont give solution for restart because I cant restart , we are givivng 99.9% availability...

    Monday, August 9, 2010 7:10 AM

Answers

  • In SS2k5 My cpu is using >80% can I reduce it to 40% without restart

    What is "normal" CPU usage? You need to figure out queries which a consuming more CPU and start tuning them.

    Refer <<<link removed as it was reported as malware>>


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, August 9, 2010 7:16 AM

All replies

  • In SS2k5 My cpu is using >80% can I reduce it to 40% without restart

    What is "normal" CPU usage? You need to figure out queries which a consuming more CPU and start tuning them.

    Refer <<<link removed as it was reported as malware>>


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, August 9, 2010 7:16 AM
  • Do you have many connections/user to your system?

    If you start for example Profiler against the SQL Server, then you can take a look at what queries that takes alot of CPU.

    You can also check Activity monitor on the server in SSMS to see running queries and their cpu usgae


    Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)
    Monday, August 9, 2010 7:36 AM
  • You can use a statement like this to get the accumulated CPU hogs in your instance and start on tuning them.

     

    SELECT TOP 10 COALESCE(DB_NAME(st.dbid),
                    DB_NAME(CAST(pa.value as int))+'*',
                    'Resource') AS DBNAME, 
           SUBSTRING(text,
            -- starting value for substring 
            CASE WHEN statement_start_offset = 0 
                 OR statement_start_offset IS NULL 
                   THEN 1 
                   ELSE statement_start_offset/2 + 1 END,
            -- ending value for substring
            CASE WHEN statement_end_offset = 0 
              OR statement_end_offset = -1 
              OR statement_end_offset IS NULL 
                   THEN LEN(text) 
                   ELSE statement_end_offset/2 END - 
                       CASE WHEN statement_start_offset = 0 
                              OR statement_start_offset IS NULL 
                                   THEN 1 
                                   ELSE statement_start_offset/2  END + 1
            )  AS TSQL, 
            SUBSTRING(CONVERT(CHAR(23),
                      DATEADD(ms,(total_worker_time/execution_count)/1000,0),
                              121),
                      12,23)  AVG_CPU_MS
    FROM sys.dm_exec_query_stats  
       CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
       OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
        WHERE attribute = 'dbid'  

       ORDER BY AVG_CPU_MS DESC ;

     

    If you which to have a more detailed view, as they happens go for the profiler.

    - Raoul 

    Monday, August 9, 2010 12:04 PM
  • High CPU could mean a few different things.  Can you post back the results from the following and also, let us know the version of SQL Server and the platform is is running on (32/64 bit)

    select CacheSizeMB = SUM(single_pages_kb + multi_pages_kb)/1024.0 
    from sys.dm_os_memory_clerks 
    where [name] = 'TokenAndPermUserStore'
    
    

    select 

        scheduler_id,

        current_tasks_count,

        runnable_tasks_count

    from 

        sys.dm_os_schedulers

    where 

        scheduler_id < 255

    select

          round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) /

                si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_CPUs_Necessary,

          round((((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) /

                si.cpu_ticks_in_ms) * cpu_count) / hyperthread_ratio), 2) as Additional_Sockets_Necessary

    from

    sys.dm_os_wait_stats ws

    cross apply sys.dm_os_sys_info si

    where ws.wait_type = 'SOS_SCHEDULER_YIELD'

    select top 10 *

    from sys.dm_os_wait_stats

    where wait_type not in -- remove system waits  

    (

    'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',

    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',   

    'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',

    'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',

    'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',

    'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',

    'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

    'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',

    'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'

    )

    order by wait_time_ms desc

     

    Check these forums for simialr problems.  Jonathan Kehayias usually replies with what to look for in waits and on

    http://social.msdn.microsoft.com/Search/en-US/?Refinement=112&query=jonathan%20kehayias%20high%20cpu


    Ted Krueger Blog on lessthandot.com @onpnt on twitter

    Monday, August 9, 2010 12:19 PM
  • Although high CPU can be caused by queries, this is much more commonly a result of not setting the max memory setting correctly and the server is paging memory (which uses high CPU).  Check your memory settings also.
    Monday, August 9, 2010 1:45 PM
  • The first thing I would check would be to check all activity on the Server.  Is the Server dedicated to SQL?  is Virus Checker scanning SQL Files? Is the Server also acting as an Application Server?

    If it is purely a SQL box and you've discounted all other software/configurations for the high CPU overhead then check out SQL as per the excellent suggestions already provided.

    Also check that you haven't got any Bulk Processes going on like Index Rebuilds, database shrinks etc.  Ensure that the "Autoshrink" and "Auto Close" options are turned off on all of the databases.  Is there any database maintenance still running?  In particular look at Update Statistics routines.


    Tony C
    • Proposed as answer by Tom Li - MSFT Tuesday, August 10, 2010 10:26 AM
    Monday, August 9, 2010 3:18 PM