SQL Server Performance

60 % Memory usage while cpu usage over 90%

Discussion in 'Performance Tuning for DBAs' started by marnix, Dec 15, 2003.

Page 1 of 2

  1. marnix New Member

    Hi,

    We have a dedicated SQL server (Dual pentium 3.06 Ghz hyper threading CPU with 3 gb internal memory) that is used for a website. At peak times there are about 2400 users on the site. The server then uses about 95% of the CPU power (continiously) however the memory usage is always stable at 1.8 gb. SQL server is configured to dynamically use memory. Is this normal or is there a way to use more memory and less CPU power?
    Any help is appreciated
    marnix, Dec 15, 2003
    #2

  2. Luis Martin Moderator

    Please could you provide:

    SO and SP.
    SQL and SP.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 15, 2003
    #2

  3. bradmcgehee New Member

    If you are using SQL Server Enterprise and the correct version of the OS, and are using the /3GB switch, SQL Server can use more th an 1.8GB, assuming it thinks it needs it. What is your current available memory and your buffer hit cache ratio?

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Dec 15, 2003
    #2

  4. satya Moderator

    For accurate results use SYSMON (perfmon) for the counters Brad asked for.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 15, 2003
    #2

  5. marnix New Member


    The OS we are using is Win2000 server SP4. We are not using the SQL Server Enterprise edition but the standard edition (v8.00.760 SP3). Is there a difference in the use of memory between the SE and the Enterprise edition?

    Marnix
    marnix, Dec 16, 2003
    #2

  6. satya Moderator

    Yes SE uses max of 2GB whereas EE has more than 2GB capability depending on the physical memory available and windows version.

    BTW what are the memory settings on SQL?A
    Are there any other applications running parallely?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 16, 2003
    #2

  7. marnix New Member


    Ah! Will the SE then uses the full 2Gb or does it also reserve some mem for the OS? In other words will SQL server uses more memory when we upgrade to the EE or because it is not using more than about 1.8 Gb this wil stay the same? If it does use more memory is there a fair change that this brings down the CPU usage?
    The memory setting in SQL is: 'Dynamically Configure SQL server memory'. There is only one application using the database (website) which makes use of only one user.

    Marnix
    marnix, Dec 17, 2003
    #2

  8. satya Moderator

    IF you define memory settings as dynamic then it uses all available memory which is max of 2GB in SE. ANd its better to leave the settings for a dedicated SQL server where no applications are sharing system resources.

    Any chance of getting counters using SYSMON?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  9. marnix New Member

    The buffer hit cache ratio = 99.83 and the available mem = 973280 Kb

    Marnix
    marnix, Dec 17, 2003
    #2

  10. satya Moderator

    Seems ideal, how about PROCESSOR counters?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  11. marnix New Member

    Are there any specific counters you would like to know?
    At this moment the CPU is not to busy because there are about 950 users on the site
    %DPC = 0.2
    %Processor: 31,5
    %Interupt time 0,11

    The %Processor counter goes to 95 when there are about 2400 users with sometimes flatliners at 100%! The memory usage does not change.

    Marnix
    marnix, Dec 17, 2003
    #2

  12. satya Moderator

    May refer tohttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp link.

    Resolving high CPU utilization issues can be very time-consuming, especially when you don't know where the problem lies. With proper optimization techniques, such as adding proper indexes, redesigning badly written queries, and so on, you can avoid almost all of these issues

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  13. marnix New Member

    Thanks for the link.

    We have observed via Profiler all database activity. There are no long queries and every query used it's defined index, the amount of queries however are uge. All database code is transfered from the application to stored procedures however we do have some cursors and use globs which I know is not the best for performance but in this case inevitable. The thing however I found strange is that the CPU usage is not related at all to memory usage! If this is because of the SE I can imagine but if I understand you correct if this is the case the mem usage should be 2Gb instead of 1.8Gb.

    Marnix
    marnix, Dec 17, 2003
    #2

  14. satya Moderator

    By default, SQL Server changes its memory requirements dynamically, based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system.

    So in this case its not compulsory to use 2GB and it reached 1.8gb anyway.

    Also capture counter for Process: Working Set & SQL Server: Memory Manager: Total Server Memory (KB)

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  15. marnix New Member

    The Working Set counter for SQL server = 1,78 Gb; The Total Server Memory for SQL server = 1.68 Gb
    What does these figures say/

    Marnix
    marnix, Dec 17, 2003
    #2

  16. satya Moderator

    The Working Set counter shows the amount of memory used by a process. In your case relatively ideal as memory set for dynamic settings.

    Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  17. marnix New Member

    If we upgrade the memory, will this also mean upgrade to EE to make use of the memory?
    What is a good thumb rule for the amount of Total Server Memory for SQL server vs. Phisical memory?
    It now is 2:3

    Your help is very much appreciated.
    marnix, Dec 17, 2003
    #2

  18. Luis Martin Moderator

    First upgrade to EE.
    After upgrade look again the counters to find out if more memory is neccesary.
    I live SQL dynamic use of memory from 0 to all.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 17, 2003
    #2

  19. satya Moderator

    To get advantage you've to upgrade to EE and SQL can take more of the memory as specified.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2

  20. gaurav_bindlish New Member

    When you see these spikes in the CPU utilization, what is the value of the counter processor queue length?

    Also high value of CPU may mean large computations and hash joins in the system. I would drill down into the queries to see what is actually happening and why the CPU utilization is so high.

    Also if it is a web based application, check if connection poooling is being used or not.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Dec 17, 2003
    #2

  21. cejar New Member

    I dont know if this will help with problem, but I ran sp_updatestats on a database that was raising our CPU utilization out the window. I even captured the trace for it notice how the numbers dropped once sp_updatestats ran. We were getting close to 80% CPU then was dropped to 14% after I executed the command.


    EVENTCLASS CPU DURATION TEXTDATA
    SQL:BatchCompleted1298530890SELECT Rep_ID_F,
    SQL:BatchCompleted1159330563SELECT Rep_ID_F,
    SQL:BatchCompleted2778130516SELECT Rep_ID_F,
    SQL:BatchCompleted1698430530SELECT Rep_ID_F,
    SQL:BatchCompleted5871871393EXEC sp_updatestats
    SQL:BatchCompleted32198390SELECT Rep_ID_F,
    SQL:BatchCompleted25167420SELECT Rep_ID_F,


    Cheers!
    cejar, Dec 17, 2003
    #2

  22. satya Moderator

    True, it helps but in some cases if h/w itself is a bottleneck then you need to choose to upgrade.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 17, 2003
    #2
Page 1 of 2

Share This Page