SQL Server Performance

CPU stuck between 30 and 50 %

Discussion in 'Performance Tuning for DBAs' started by ddbb, Oct 28, 2003.

  1. ddbb New Member

    I have following configuration:

    1. active/passive cluster, Win2000, SQL2000
    2. 4 CPUs in each node, 4GB RAM in each node
    3. Disk arrays configured for optimal performance (different arrays for diff things, diff RAID levels etc.)

    I have few periods during the day when CPU utilization is stuck between 30 and 50% without a single peak on any side. That can last for more than hour. All CPUs are behaving the same way. Response on the client side is very slow. The only process in that period of time that is using CPUs extensively is sqlservr.exe. I've checked deadlocks - nothing. I was running Profiler to check slow queries - nothing. Other perfmon counters seams to be OK (one does give me concern actually - buffer cache hit ratio is actually over 100, around 100.150 or similar?!). Without any performed action, this behaviour stops as sudden as it started and utilization on all CPUs is back on expected 0-100%!

    Any ideas?

    Appreciate your help!

  2. satya Moderator

    How about status of Total Server Memory (KB), pages/sec counters?
    And how about memory settings on SQL Server?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. Luis Martin Moderator

    How about Disk usage during that time?



    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
  4. ddbb New Member

    Almost 1GB of RAM is still available, pages/sec on average < 20.

    SQL Server is set to use memory dynamically.


    quote:Originally posted by satya

    How about status of Total Server Memory (KB), pages/sec counters?
    And how about memory settings on SQL Server?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  5. satya Moderator

    Is that total server memory is constant or variable?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. ddbb New Member

    Disk usage is fine - avg. queue lenght is less than 0.1 and %disk time is 1.5%.


    quote:Originally posted by LuisMartin

    How about Disk usage during that time?



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

    Any parallel jobs or process are running during this slow transition period?
    How about DBCC checks?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. ddbb New Member

    Which counter do you actually have in mind?
    Memory:Available Bytes is variable and is between 500MB and 1GB.

    Thanks for help


    quote:Originally posted by satya

    Is that total server memory is constant or variable?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  9. ddbb New Member

    No, server is dedicated to SQL and sqlservr.exe is the only process that takes resources.
    I haven't tried DBCC checks - what should I look for?


    quote:Originally posted by satya

    Any parallel jobs or process are running during this slow transition period?
    How about DBCC checks?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  10. satya Moderator

    I'm concerned about total server memory (KB) counter.
    So it seems no DBCC checks are in place, what is the size of the database and userbase.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. Luis Martin Moderator

    Why not DBCC DBcheck or checkalloc to see if there is some problem?



    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
  12. ddbb New Member

    0 allocation errors and 0 consistency errors



    quote:Originally posted by LuisMartin

    Why not DBCC DBcheck or checkalloc to see if there is some problem?



    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
  13. Luis Martin Moderator

    Database Grow, is set automatic 10%?.
    If yes and Database is big, may be SQL has extra work to extend it.
    In this case I suggest to leave automatic but with fix amount, less than 10%.



    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
  14. satya Moderator

    What is the size of database?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. ddbb New Member

    automatic off


    quote:Originally posted by LuisMartin

    Database Grow, is set automatic 10%?.
    If yes and Database is big, may be SQL has extra work to extend it.
    In this case I suggest to leave automatic but with fix amount, less than 10%.



    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
  16. ddbb New Member

    7.5GB


    quote:Originally posted by satya

    What is the size of database?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  17. satya Moderator

    Then DBCC DBREINDEX during less traffic on the database and check the performance.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  18. Luis Martin Moderator

    Or DBCC INDEXDEFRAG if you can't run DBCC DBREINDEX on line.

    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
  19. ddbb New Member

    indexes are recreated each night


    quote:Originally posted by satya

    Then DBCC DBREINDEX during less traffic on the database and check the performance.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  20. Luis Martin Moderator

    How about Update Statistics?


    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
  21. Luis Martin Moderator

    How about Update Statistics?


    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
  22. ddbb New Member

    automatic



    quote:Originally posted by LuisMartin

    How about Update Statistics?


    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
  23. Luis Martin Moderator

    Automatic in database setting, I gess.
    But I suggest to Upadate Statists full on night or non peek hours at least every week.



    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
  24. ddbb New Member

    I have "Auto Update Statistics" database setting ON.
    Are you sugessting something in addition to that? Can you be more precise please?
    Thanks.


    quote:Originally posted by LuisMartin

    Automatic in database setting, I gess.
    But I suggest to Upadate Statists full on night or non peek hours at least every week.



    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
  25. Luis Martin Moderator

    Yes, in addition to that.
    Schedule a job with sp_updatestats.




    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
  26. satya Moderator

    Do you have any other database settings like Auto_Close or Auto_Shrink?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  27. ddbb New Member

    What I'm doing each night is running Data Maintenance Plan with option Reorganize data and index pages. So statistics are updated.


    quote:Originally posted by LuisMartin

    Yes, in addition to that.
    Schedule a job with sp_updatestats.




    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
  28. ddbb New Member

    No Auto_Close, no Auto_Shrink.


    quote:Originally posted by satya

    Do you have any other database settings like Auto_Close or Auto_Shrink?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

  29. Luis Martin Moderator

    Maintenance plan defaul is update statistics 10%.
    Do you change this value to 100%?



    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
  30. ddbb New Member

    When you choose Reorganize data and index pages then Update statistics option is actually disabled.


    quote:Originally posted by LuisMartin

    Maintenance plan defaul is update statistics 10%.
    Do you change this value to 100%?



    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
  31. satya Moderator

    When DBREINDEX (re-org of data/index) is selected it automatically updates the stats.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  32. vikvin74 New Member

    I guess I had the same problem. I ran sp_updatestats (as suggested by people in here). This really works. When you first create a table, SQL Serve starts gathering statistics on it and uses it to answer the queries. SQL Server maintains this statistcis on each table and when user run queries, SQL Server uses this statistics to guess the number of rows to be retrieved by the query. Hence more accurate the statistics are, more fast the query execution is. This statistics get out-of-date gradually even if you set the autostatistic mode on (my personal experiance). So it's better to keep statistics updated from time to time.

    Cheers...
  33. satya Moderator

    Yes, in case if you don't maintain DBCC checks on the database.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page