locked
High CPU Utilisation and I\O Read Bytes RRS feed

  • Question

  • Hi,

    I have an instance of SQL Server running on my Live DB server. Lately  i started receiving alerts stating a high CPU usage fluctuating between 70-100 %. The task manager shows that the sqlserver.exe process seems to consume a lot of memory and also the I\O Read Bytes keeps increasing endlessly.

    Restarting the service does'nt help.

    Could you please suggest.

     

    Thanks in advance.

     

    Tuesday, October 19, 2010 12:35 PM

All replies

  • Is there any scheduled activity(Jobs) that is run on this DB that is making the DB to take up so much of CPU time or causes it to Read/Write to the disk.

    Tuesday, October 19, 2010 1:04 PM
  • Tuesday, October 19, 2010 1:47 PM
  • Hi Naveen,

    There are couple of SQL Server Jobs but they are scheduled at odd timings. The process consumes more memory even if there is no job running .

    Tuesday, October 19, 2010 1:54 PM
  • Hi,

    I have an instance of SQL Server running on my Live DB server. Lately  i started receiving alerts stating a high CPU usage fluctuating between 70-100 %. The task manager shows that the sqlserver.exe process seems to consume a lot of memory and also the I\O Read Bytes keeps increasing endlessly.

    Restarting the service does'nt help.

    Could you please suggest.

     

    Thanks in advance.

     


    I would startup a profiler trace to capture the sql_statement_completed and sp_statement_completed events and see what is running against the instance.  You could also do that by querying sys.dm_exec_requests:

    select *
    from sys.dm_exec_requests
    cross apply sys.dm_exec_sql_text(sql_handle)

    and then look at the tables and joins and make sure that you have appropriate indexes, specifically on foreign key and columns used to join two tables together.  Most cases where there is high IO and high CPU it is caused by missing indexes, usually because the database was designed and tested with minimal data in it and not at true production data levels.  As the amount of data increases in these types of systems, the resulting table scans and sort operations drive CPU and IO through the roof.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, October 19, 2010 2:00 PM
  • Hi Jonathan,

    Thanks for your reply.

    I queried my instance with the select * from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle)

    I could see just my session alive.

    I have also gone through the indexes and I observe that the Db was not re-indexed since quite a long time and huge data was added during this period. Do you suggest re-indexing ?

    FYI, the  task manager shows that the IO ReadBytes  is just increasing like there is no end to it.

     

    Wednesday, October 20, 2010 7:04 AM
  • Use perfmon to get a better picture of this...also check max and min memory settings for SQL server  ...a memory issue can also lead to high disk i/o and cpu utilization....
    Thursday, October 21, 2010 3:38 AM
  • Quote: "I have also gone through the indexes and I observe that the Db was not re-indexed since quite a long time and huge data was added during this period. Do you suggest re-indexing ?"

    Do you have maintenance for regular indexing, integritycheck and backups. If so the index should be taken care of. If you dont please download indexing scripts, there is one at sqlserverpedia (http://sqlserverpedia.com/wiki/Index_Maintenance). If you don't hava a full solution in place, either use maintenance plans, or the in my opinion better solution provided by Ola Hallengren (http://ola.hallengren.com/). It's in my opinion a brillian solution for indexing, integritycheck and backups.
    //Marten

    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Thursday, October 21, 2010 4:31 AM
  • Hi Guys,

    I have maintenance plans in place and they did execute . After the Rebuild_index task was done I restarted the SQL Service and the CPU usage and I\O Bytes were back to normal values. But then they began to grow with time and are still soaring.

    Thursday, October 21, 2010 7:33 AM
  • Where do you measure I\O Bytes, is it by chance in a DMV query. If so these statistics for example Read I\O is only reset when SQL Server is restarted.

    If this is the case this is normal with ever increasing reads. CPU 70-90% is not abnormal either. Does CPU spike?

    //Marten


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Thursday, October 21, 2010 8:33 AM
  • Hi Marten,

     

    I check the I\O Read Bytes in the Windows task manager. What does this value actually mean ?

    Thursday, October 21, 2010 8:58 AM
  • The I/O Read Bytes in Task Manager is the total since the process was started.  This is expected to go up to inifinity.  This is completely normal and expected behavior.

    What exactly are you having a problem with?

     

    Thursday, October 21, 2010 1:40 PM
  • Hi Tom,

    My observations were /are such ...

    Initially whenever bulk inserts were going on the CPU was fluctuating between 70-100%.Also the I\O Read Bytes were increasing continuously irrespective whether the inserts were going on or not.

    As suggested above I had the Indexes rebuilt on the DB and the restarted the SQLServer service.

    The result was that the CPU utilization and I|O ReadBytes were back to normal.

    I resumed the bulk inserts, this time the CPU utlization does not grow and remains well under 40%. However the I\O read bytes have began to grow once again. Moreover the bulk inserts have slowed down considerably consuming nearly twice of the time they used to take earlier.

     

    Friday, October 22, 2010 6:31 AM