none
what are the resons for more CPU/Memoery/IO uitlization

Answers

  • Impossible to answer without having anything to go on. It could be that you have a higher query load, that you have more data in the tables, you get different execution plans than before. Also, exactly what increased can give a clue. If you have more I/O but less CPU, we would suspect that the system is doing more I/O than previously, keeping the CPU waiting for those I/O operations to complete.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by vr.babu Thursday, September 26, 2013 6:31 AM
    • Marked as answer by vijay_1234 Thursday, September 26, 2013 11:57 AM
    Thursday, September 26, 2013 6:29 AM
  • Hello vijay,

    I suppose you already raised question about this on other forum.In below thread of your I shared SQl serevr 2008 performance troubleshooting DOC seems you are not reading answers properly

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/313c3daa-944b-4918-9304-776cb14ac9ab/how-to-find-memory-utilization-


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by vijay_1234 Thursday, September 26, 2013 11:56 AM
    Thursday, September 26, 2013 6:43 AM
  • Hi Shanky


    Yesterday..i given interview for iGATE

    Q: cpu utilization reaches >100% , so wt you do?

    I said like that . i will find wich processing are consuming more memory . by select * from    

             sys.dm_exec_reqsts   order by cpu_time.

      (any bulk log , or any proces.... then i will find more info abt the process )

    Q:2 : how do you find memory utilization , what are the threshold value. ?

    A: I will go through the Task manger and Resource moniotor if ,RAm utilization reaches >50% .

         then i will find process through

    select* from sys_dm_exec_sessions order by memory_usage

    so he is not satisfied , so i have one more interview on Saturday  .

    pls share  your experience primary steps....

    when cpu utilization reaches >80%

    I have no idea memory threshold value and counters, IO



    Hello,

    ANS: CPU utilization cannot go above 100 %.If CPU utilization is high look for

    1. Parallel Queries

    2. Look for queries recompiling

    3. Make sure Index is not fragmented and Stats are updated.

    4. Find out queries taking large CPU time

    5.Your Hardware might not be able to cope up with Workload ..Mode details is there in below doc

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx  look for section CPu bottleneck

    ANS: For memory issue errorlog mostly has Out of memory error ,looking perfmon and task manager on system when locked pages in memory is enabled will not give you correct value instead use below query

    Working of SQL memory is different on 32 bit and 64 bit.With 32 bit things become littlt complex.

     select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb, 
    locked_page_allocations_kb/(1024) as awe_memory_mb, 
    total_virtual_address_space_kb/(1024) as max_vas_mb,
     virtual_address_space_committed_kb/(1024) as sql_committed_mb,
     memory_utilization_percentage as working_set_percentage, 
    virtual_address_space_available_kb/(1024) as vas_available_mb,
     process_physical_memory_low as is_there_external_pressure, 
    process_virtual_memory_low as is_there_vas_pressure 
    from sys.dm_os_process_memory 
    go 

    Also you can use DBCC memorystatus output and see which clerk is consuming meory.http://support.microsoft.com/kb/271624/en-us

    Use DMV sys.dm_os_ring_buffers,sys.dm_os_memory_clerks,sys.dm_os_buffer descriptors.You can also use above doc for performace troubleshooting provided http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx 

    Last of all please start some hands on SQl server system facing issue ,you will automatically be able to tell him how to handle this situation.This is just very basic I told you

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by vijay_1234 Thursday, September 26, 2013 4:13 PM
    Thursday, September 26, 2013 12:20 PM

All replies

  • Your SQL Server is the one who eats most of the memory, the problem comes when OS and SQL Server both chase each other to eat memory. Remember SQL Server runs under the OS, so OS should have sufficient memory to run SQL Server as well as other process.

    By default SQL Server is set to consume unto 2TB of memory, however you can restrict it to 1 GB or what ever you foresee will be the requirement, you can go to SQL Server Database (SSMS) -> Right click properties -> Memory and restrict it to some MB or GB



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

    • Marked as answer by vijay_1234 Thursday, September 26, 2013 4:17 AM
    • Unmarked as answer by vijay_1234 Thursday, September 26, 2013 4:17 AM
    Thursday, September 26, 2013 4:13 AM
  • Impossible to answer without having anything to go on. It could be that you have a higher query load, that you have more data in the tables, you get different execution plans than before. Also, exactly what increased can give a clue. If you have more I/O but less CPU, we would suspect that the system is doing more I/O than previously, keeping the CPU waiting for those I/O operations to complete.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by vr.babu Thursday, September 26, 2013 6:31 AM
    • Marked as answer by vijay_1234 Thursday, September 26, 2013 11:57 AM
    Thursday, September 26, 2013 6:29 AM
  • Hello vijay,

    I suppose you already raised question about this on other forum.In below thread of your I shared SQl serevr 2008 performance troubleshooting DOC seems you are not reading answers properly

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/313c3daa-944b-4918-9304-776cb14ac9ab/how-to-find-memory-utilization-


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by vijay_1234 Thursday, September 26, 2013 11:56 AM
    Thursday, September 26, 2013 6:43 AM
  • Usually it is lack of the index or improperly written queries


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 26, 2013 6:53 AM
  • Hi Shanky

     

    Yesterday..i given interview for iGATE

    Q: cpu utilization reaches >100% , so wt you do?

    I said like that . i will find wich processing are consuming more memory . by select * from    

             sys.dm_exec_reqsts   order by cpu_time.

      (any bulk log , or any proces.... then i will find more info abt the process )

    Q:2 : how do you find memory utilization , what are the threshold value. ?

    A: I will go through the Task manger and Resource moniotor if ,RAm utilization reaches >50% .

         then i will find process through

    select* from sys_dm_exec_sessions order by memory_usage

    so he is not satisfied , so i have one more interview on Saturday  .

    pls share  your experience primary steps....

    when cpu utilization reaches >80%

    I have no idea memory threshold value and counters, IO



    Thursday, September 26, 2013 11:51 AM
  • Hi Shanky


    Yesterday..i given interview for iGATE

    Q: cpu utilization reaches >100% , so wt you do?

    I said like that . i will find wich processing are consuming more memory . by select * from    

             sys.dm_exec_reqsts   order by cpu_time.

      (any bulk log , or any proces.... then i will find more info abt the process )

    Q:2 : how do you find memory utilization , what are the threshold value. ?

    A: I will go through the Task manger and Resource moniotor if ,RAm utilization reaches >50% .

         then i will find process through

    select* from sys_dm_exec_sessions order by memory_usage

    so he is not satisfied , so i have one more interview on Saturday  .

    pls share  your experience primary steps....

    when cpu utilization reaches >80%

    I have no idea memory threshold value and counters, IO



    Hello,

    ANS: CPU utilization cannot go above 100 %.If CPU utilization is high look for

    1. Parallel Queries

    2. Look for queries recompiling

    3. Make sure Index is not fragmented and Stats are updated.

    4. Find out queries taking large CPU time

    5.Your Hardware might not be able to cope up with Workload ..Mode details is there in below doc

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx  look for section CPu bottleneck

    ANS: For memory issue errorlog mostly has Out of memory error ,looking perfmon and task manager on system when locked pages in memory is enabled will not give you correct value instead use below query

    Working of SQL memory is different on 32 bit and 64 bit.With 32 bit things become littlt complex.

     select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb, 
    locked_page_allocations_kb/(1024) as awe_memory_mb, 
    total_virtual_address_space_kb/(1024) as max_vas_mb,
     virtual_address_space_committed_kb/(1024) as sql_committed_mb,
     memory_utilization_percentage as working_set_percentage, 
    virtual_address_space_available_kb/(1024) as vas_available_mb,
     process_physical_memory_low as is_there_external_pressure, 
    process_virtual_memory_low as is_there_vas_pressure 
    from sys.dm_os_process_memory 
    go 

    Also you can use DBCC memorystatus output and see which clerk is consuming meory.http://support.microsoft.com/kb/271624/en-us

    Use DMV sys.dm_os_ring_buffers,sys.dm_os_memory_clerks,sys.dm_os_buffer descriptors.You can also use above doc for performace troubleshooting provided http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx 

    Last of all please start some hands on SQl server system facing issue ,you will automatically be able to tell him how to handle this situation.This is just very basic I told you

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by vijay_1234 Thursday, September 26, 2013 4:13 PM
    Thursday, September 26, 2013 12:20 PM
  • Thank you very much.
    Thursday, September 26, 2013 4:13 PM