SQL Server Performance

CPU Utilisation

Discussion in 'Performance Tuning for Hardware Configurations' started by nayum, May 28, 2005.

  1. nayum New Member

    Hi,

    I m running some heavy scripts in Query Analyzer, but the CPU Utilisation is not going above 10%. The configuration of the Server is W2K Pentium 3 with 2 CPU and 2 GB RAM.
    The Cache Hit ratio is about 8%. Can anybody help me what to do to increase CPU utilisation. Should I change the Server or Increase RAM and yes how to Allocate Memory to SQL SErver. Please Help me

    Thanks In advance.

    Regards,
    Nayum
  2. chopeen Member

    What kind of queries are you running?
    Are they retrieving a lot of data?

    How much RAM is SQL Server using?
    Is there any free RAM left?

    --

    Rediscover the web
    http://www.mozilla.org/firefox/
  3. nayum New Member

    Hi, i m running scriptwhich does update for all the 2 lakhs rows in a table.

    Now how to determine the RAM which SQL Server is Using and how to increase it.

    Thanks and Regards,
    Nayum
  4. deepakontheweb New Member


    1. Run DBCC Showcontig and find out fragmentation level.
    2. Remove fragmentation using DBCC INDEXDEFRAG or DBCC DBREINDEX
    3. Check what all indexes you have on table, check if they are being used by update query
    4. Run performance monitor to find out what was cache hit ratio, memory utilization and CPU usage at different time.

    After implemanting on above steps..Let us know you experience. there are many more steps to improve performance instead of changing server hardware.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  5. satya Moderator

    If you want to know the RAM utilisation then use PERFMON (SYSMON) using memory and SQL memory counters for further assessment.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. nayum New Member

    SQL CACHE MEMORY IS 12-15%
    PROCESSOR TIME IS 8%
    CACHE HIT RATIO IS BELOW 10%

    SYSOJECTS
    Extent Scan Fragmentation ...................: 80.48%

    SYSINDEX
    Extent Scan Fragmentation ...................: 90.00%

    SYSCOLUMNS
    Extent Scan Fragmentation ...................: 86.48%
  7. joechang New Member

    if are running a script which must either read or modify a very large amount of data, then the buffer cache hit ratio has no meaning, because it is stupid to try to load the data into memory if you are just going to read it sequentially,
    i would look into the possibility that you cannot drive sufficient sequential disk reads
  8. derrickleggett New Member

    Look at your physical disk read and write queue lengths. Post them for all drives on the system and explain what the drives are for, and how configured. For example:

    C: 1 - Data - 4 disks RAID 5

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. mmarovic Active Member

    Why did you post system tables fragmentation statistics? This is irrelevant, post fragmentation statistics of the table you are updating. How long does it take to update 200000 rows? I guess 1 lakh = 100000 rows.
  10. gurucb New Member

    There is something wrong here.

    If you see CPU percentage showing only 8 and buffer cache hit ratio less that 10, then there should be lot of paging going on,If paging is going on typically you would see cpu counter going up, as it has go to hard disk fetch the page, put it in memory.

    May be the counters are not reflecting correctly or CPU does not want to do work.
  11. satya Moderator

    SQL CACHE MEMORY IS 12-15%
    PROCESSOR TIME IS 8%
    CACHE HIT RATIO IS BELOW 10%

    Are these are captured using PERFMON?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. gurucb New Member

    What is the MAX Server memory setting in SP_configure?
    are you not seeing any other errors in SQL Error log

Share This Page