Detecting CPU Bottleneck

When do you categorize a server to be running into a CPU bottleneck?  And how do you go about collecting that information on Windows and other Unix systems?  The yard stick that is very commonly used is that if an individual user process is consuming greater than 10% of the CPU then there is a need to investigate further.  Also, if the cumulative CPU usage is consistently more than 80%, then this also indicates a CPU bottleneck. If there are no individual processes using a lot of CPU, then you are trying to put too much work on the server, and should upgrade to either faster processors or add more processing power. Here is what you can do to automatically capture this information on different OS:

HP/UX or AIX or Linux or Solaris:

“sar –o <filename> 60 10 >/dev/null 2>&1 &” will sample the CPU activity every minute for 10 minutes and save the output in a file.  If you are running into issues, reduce the time-frame for the data collection.

If user+nice+system > 80% for an extended period of time, then you have a CPU bottleneck.

AIX specific:

“vmstat 10 500″ will display system CPU usage every 10 secs. If id=0 or us+sy>80 for an extended period of time, then this indicates that the system is CPU-bound.

Solaris specific:

“top” will show you the top processes by CPU activity

Note: You may have to install SUNWaccu and SUNWaccr packages if sar is not installed.

Windows:

a) Use the task manager and select the processes tab.
b) Then click either the “CPU” column title to sort the processes

A better approach though is to use perfmon (Performance Monitor) and schedule it to collect the data:

c) Type perfmon from the command line (Start/Run and then perfmon and enter)
d) Then click on the “+” symbol and add “%processor time” for each of the processors you have on your system.  You can save this as a blg file and then do further analysis on it later on.

Profiler and Perfmon Co-relation in SQL Server 2005

One of the neat features introduced in SQL Server Profiler in version 2005 is the ability to co-relate profiler results with perfmon results. This is good because it gives us the ability to correctly see what was going on in the system. There may be times when you see spikes in Performance Monitor on your SQL Server machine such as a high CPU usage, large amounts of memory consumption, or overall slower performance etc..  Before SQL Server 2005, you would have to capture a trace, use sysprocesses, syslockinfo and other system related data from SQL Server and capture your Performance Monitor logs. After doing all this, you would need to manually reconcile what happened between them to figure out why performance was suffering in the system.  Manual re-conciliation of the wait events and queues really became very cumbersome.

With SQL Server 2005, you still need to capture a trace and your Performance Monitor logs. However, Profiler has the capabilities to let you attach Performance Monitor logs and then scroll through your T-SQL statements while Profiler automatically shows graphically what happened in your Performance Monitor logs. The process also works if you click in the Performance Monitor user interface in Profiler, which jumps you to the statement that correlates to that timestamp. This helps immensely in troubleshooting issues in the system.

To demonstrate this, let’s start a perfmon and a profiler trace and let’s use this simple script to generate some small amount of load. The laptop on which this is being run is a very minial configuration so am sure this would be enough to peg the CPU. Here’s the script:

create table t (col1 datetime)
go

declare @i int
set @i = 1
while (@i < 100000)
begin
insert into t values (getdate())
set @i = @i + 1
end

Before we kick it off, let’s start the traces: Start/Run, type in perfmon, and hit Enter. From there, you’ll want to create a new counter log under the Performance Logs and Alerts by right-clicking the Counter Logs node and selecting New Log Settings. Then, you can click the Add Counters button and add % processor time. For the sake of this demo, we can start this on a manual basis (the schedule tab). Once you’re done, hit OK. Now, let’s start up the profiler trace as well using SQL Server Profiler. Just chose the default template for this demo and start both the perfmon and profiler trace.

Now, run that script and once it is done, stop the traces and save the profiler trace. You cannot open up the co-relation with perfmon till you save a profiler trace and then load it back up. Once the profiler trace is saved, in Profiler, from the File menu, select Import Performance Data. Select the location where you stored your Performance Monitor log. Then, select from the File menu Open and then Trace. Select the location where you stored your Profiler trace. Now, you can use Performance Monitor correlation (from File/Import Performance Data) between the two to figure out what effect on the processor a certain SQL statement had. See figure below:

This shows that the CPU was really pegged at 100% when the processing was going on. I also had Reporting Services running at the same time so you see some calls from Report Server as well. This is a great way of troubleshooting performance issues and co-relation between these 2 great tools is a very nice feature in SQL 2005. In future blogs, we will post some of the performance counters and profiler templates that we use for our performance tuning and benchmarking.

Oracle 11g

Last year at their OpenWorld conference, Oracle announced their plans for Oracle 11g – the next release version for Oracle’s RDBMS.  Overall, they have promised 482 new features.  However, not much information has been released about them.  A couple of key ones that were discussed then and in subsequent discussion boards are: .

1. A better table and index compression scheme: This is a feature that IBM also has in Viper (DB2 version 9). The segment compression will work for all DML statements and not just the direct path loads.  So, we can now create tables as compressed and use them in our regular OLTP applications with very less overhead.
2. Capturing and replaying the database workloads: This is one feature that is present in SQL Server (profiler replays).  One can now capture the workload in production and then replay it in development.
3. Virtual columns and virtual indexes: Virtual columns are columns that are actually functions (kind of like computed columns in SQL Server).
4. ADDM for RAC: This will make troubleshooting RAC configurations much easier.
5. Interval partitioning: This scheme will automatically create time based partitions as new data gets added.  This is essentially an automated version of range partitioning.
6. RMAN Improvements: RMAN can now by-pass the UNDO tablespace.
7. ADR: Automatic Diagnostic Repository.  Whenever critical errors will occur in the system, an incident will be automatically created and certain health checks can be run automatically.  This information gathered then can be used by Oracle Support to better help customers.
8. Feature Based Patch-sets: All the one-off patches will be categorized by feature sets.  So, you can easily identify which patches you need based on the features that you use.
9. Automatically tuning the bad SQLs: In the 10g version, the automatic tuning advisor can make tuning suggestions based in the form of SQL profiles.  In 11g, one can automatically apply SQL profiles for statements if the suggested profile gives atleast 3 times better performance – please note that these performance comparisons are done during a DBA specified maintenance window and needs to be carefully evaluated – we will post more details when they become available.
10. Improvements to the Resource Manager: The resource managerin 11g can manager I/O in additon to CPU.  You can even set up priorities with files, file types or the ASM disk groups.
11. A couple of new load balancing utilities like Oracle Data Guard load balancing between standby databases (this was actually introduced in 10gR2 itself and has been improved upon further in 11g).
12. A new hint to cache the data in the data buffers and not the intermediate data blocks that are accessed to obtain the results for the queries.
13. Faster collection of stats using dbms_stats.
14. Automatic memory tuning: This is great – but I bet will not work the way it is supposed to   Oracle had introduced automatic PGA tuning in 9i version, automatic SGA tuning in 10g which still has a lot of issues.  11g promises to tune all memory by setting one parameter.  You will just need to tell Oracle how much memory is allocated to it and it will internally dynamically manage the memory for PGA, SGA and the OS processes.  This is just like SQL Server – minimum and maximum values can be set and leave the rest on Oracle.
15. FGDT: Fine Grained Dependency Tracking: What this means is that when one adds say a cursor to a package, there will be no invalidation of the objects that depend on it.
16. PL/SQL compiler changes: One of the changes is to issue a warning for a “when others” without a raise.
17. Sequences: Normal assignments on sequence values would be possible and will by-pass the DML (sequence_name.nextval).

We will be writing more detailed blogs on these feature sets in the days to come as more information becomes available.  The aim of 11g release is ease of administration and lots of new programming and HA (High Availability) and Scalability features.  Also, the world of SQL Server, Oracle and DB2 LUW (in terms of feature sets) is coming closer as each vendor starts adopting the good features of another one – all this is good for us – the end consumers.