CPU Utilisation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

CPU Utilisation

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
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/

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

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
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.
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%
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
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
[email protected] When life gives you a lemon, fire the DBA.
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.
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.
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.
What is the MAX Server memory setting in SP_configure?
are you not seeing any other errors in SQL Error log
]]>