September 25, 2003 at 6:31 am
We are having a very high CPU usage of 97% approx. since a few weeks now. We are on SQL7 with 4 processors and Server is configured to use both these.
A few SQLServer Counters are also attached. Can someone help or suggest anything. Server response is also very slow. There is a lot of activity going on but is there any way we can improve the performance.
Processor: % Privileged Time
[Glenister, Faye] CPU 1: 3.5%, CPU 2: 3.4%. CPU 3: 3.5%, CPU 4: 4.7%
System: Processor Queue Length
[Glenister, Faye] Average: 9.8 Max: 34.00 (This is high this indicates a processor congestion).
SQLServer:Buffer ManagerBuffer Cache Hit Ratio949962671537003008
SQLServer:Buffer ManagerBuffer Cache Hit Ratio Base9501293021073939459
SQLServer:Buffer ManagerPage Requests/sec950694303272696320
SQLServer:Buffer ManagerExtendedMem Requests/sec2324917 272696320
SQLServer:Buffer ManagerExtendedMem Cache Hit Ratio0 537003008
SQLServer:Buffer ManagerExtendedMem Cache Hit Ratio Base 23249171073939459
SQLServer:Buffer ManagerExtendedMem Cache Migrations/sec 0 272696320
SQLServer:Buffer ManagerFree Buffers248665536
SQLServer:Buffer ManagerCommitted Pages11468865536
SQLServer:Buffer ManagerReserved Page Count398365536
SQLServer:Buffer ManagerStolen Page Count906665536
SQLServer:Buffer ManagerLazy Writes/sec29361272696320
SQLServer:Buffer ManagerReadahead Pages/sec3642002272696320
SQLServer:Buffer ManagerCache Size (pages)527765536
SQLServer:Buffer ManagerPage Reads/sec4346569272696320
SQLServer:Buffer ManagerPage Writes/sec1796100272696320
SQLServer:Buffer ManagerCheckpoint Writes/sec998895272696320
SQLServer:Buffer ManagerLazy Writer Buffers/sec176397122726
SQLServer:General StatisticsLogins/sec114355272696320
SQLServer:General StatisticsLogouts/sec114122272696320
SQLServer:General StatisticsUser Connections23265536
SQLServer:Memory ManagerConnection Memory (KB)751265536
SQLServer:Memory ManagerGranted Workspace Memory (KB)2969665536
SQLServer:Memory ManagerLock Memory (KB)48865536
SQLServer:Memory ManagerLock Blocks Allocated510165536
SQLServer:Memory ManagerLock Owner Blocks Allocated544165536
SQLServer:Memory ManagerLock Blocks369265536
SQLServer:Memory ManagerLock Owner Blocks435765536
SQLServer:Memory ManagerMaximum Workspace Memory (KB)66422465536
SQLServer:Memory ManagerMemory Grants Outstanding1565536
SQLServer:Memory ManagerMemory Grants Pending065536
SQLServer:Memory ManagerOptimizer Memory (KB)115265536
SQLServer:Memory ManagerSQL Cache Memory (KB)204065536
SQLServer:Memory ManagerTarget Server Memory(KB)92101665536
SQLServer:Memory ManagerTotal Server Memory (KB)92101665536
Edited by - h.chhabra on 09/25/2003 07:20:10 AM
September 25, 2003 at 7:56 am
Try to find out whether it is caused by SQL Server process or by other processes.
September 25, 2003 at 8:29 am
It is because of the SQLServer.exe process. It is taking all the cpu.
September 25, 2003 at 9:09 am
Have you tried to upadte database statistics? Can you also look the performance in I/O? You need also start Profiler to see how queries performed in SQL Server.
September 25, 2003 at 9:27 am
Have you tried to upadte database statistics? Can you also look the performance in I/O? You need also start Profiler to see how queries performed in SQL Server.
No havn't updated the statistics till now.
For I/O performance do I need to look for any specific counters.
I have already recorded a trace and queries appear to be running efficiently.
Just to let you know about an area of concern which may be causing this, there is a table with close to 26 million records with an index also on that. This table is written to very frequently also. As per the timings in the trace the record addition to this table is not taking any significant time.
Thanks for your responses till now. Please suggest.
September 25, 2003 at 10:42 am
1. Any new code released to production? Can be an infinite loop of sql queries bounding the system.
2. Parallelly check all processor time & SQL Server:Databases: Transactions/sec. If they are directly proportional then it can be a processor bottleneck.
3.Profiler output is a great clue. You can filter the queries, and see which queries are frequent and cause cpu time a lot. Work on the queries.
4. How is your Physical Disk doing with reads/sec and writes/sec. Network can also be checked in perfmon.
5. If Processor:%User Time is high then, it is sql server working on the queries,sps etc. I am positive your db is configured for auto update statistics. Even then, you can issue update statistics on your database.
6.Your regular transaction log backup size will tell the trend, whethere you are having a heavy usage.
Post your perfmon outputs with average, and also in gbs, or %s, so that people dont have to calculate them for you.
HOpe the above helps. I have been constant troubles like this, one way or other, and so for solving them, one by one.
September 25, 2003 at 11:36 am
Begin disk performance monitoring by looking at the following counters:PhysicalDisk: Percent Disk Time, PhysicalDisk: Current Disk Queue Length and PhysicalDisk: Avg. Disk Queue Length.
The PhysicalDisk: Percent Disk Time counter monitors the percentage of time that the disk is working. Check the PhysicalDisk: Current/average Disk Queue Length counter to see the number of requests that are queued up waiting for disk access.
Also check SQL Server/SQL Statistics : Batch requests/sec to measure how busy SQL Server is.
http://www.sql-server-performance.com/performance_monitor_counters.asp
September 25, 2003 at 1:18 pm
Here's a thought, do you have the latest service pack to ensure you don't have Mr. Slammer?
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 26, 2003 at 2:28 am
Dear Ray,
Thanks for the response. Isn't only SQL2000 impacted by slammer worm. We are on SQL7 aon service pack 6.
September 26, 2003 at 3:19 am
Oops, looks like you are right, sorry. You have sp6 on the OS, do you have SP4 on the DB? Here is the fixlist for it http://support.microsoft.com/default.aspx?scid=kb;en-us;Q313980& , maybe one of those performance fixes may help...
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 26, 2003 at 3:37 am
Hi,
It doesn't appear to be a I/O bottleneck currently but from the link you have sent it definitely appears to be a CPU bottle neck. Context Switches/Sec is 9089(Average) when CPU consumption is over 90% and Average processor Queue is close to 5. I will see the results by using the NT Fibres option and post an update.
http://www.sql-server-performance.com/performance_monitor_counters.asp
[/quote]
September 26, 2003 at 7:23 am
You should be able to figure out which user/sql-process is consuming cpu by looking at the cpu columns of sysprocesses. From the other info in there you might be able to figure out who or what is causing the high cpu usage for this process
September 26, 2003 at 8:15 am
quote:
You should be able to figure out which user/sql-process is consuming cpu by looking at the cpu columns of sysprocesses. From the other info in there you might be able to figure out who or what is causing the high cpu usage for this process
Looking at the Process Info under management I get different values to sysprocesses table. Even simple select statement are showing 14000 under CPU.
September 26, 2003 at 11:00 am
How much memory in your server? What size is your page file and what disk is it on?
September 28, 2003 at 5:29 am
14000 (ms) is quite high for a 'simple select' but are you sure you are looking onyl at one 'select'? The cpu column is cumulative for all the work done by the spid.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply