ASP .NET Application Monitoring

This article describes the basic monitoring required to drill down any ASP .NET related performance issues. It's always good to have more performance data than not enough, especially when you experience a problem that is not easily reproduced. This article will describe some necessary Performance Counters and logs required to be looked into while analysing ASP .NET application's performance issues.

Monitoring Performance Counters:
There are many performance counters that are useful for monitoring performance of ASP .NET application. Below are the few important ones. 

Processor(_Total)\% Processor Time

Process(aspnet_wp)\% Processor Time

Process(aspnet_wp)\Private Bytes

Process(aspnet_wp)\Virtual Bytes

Process(aspnet_wp)\Handle Count

Microsoft® .NET CLR Exceptions\# Exceps thrown / sec

ASP.NET\Application Restarts

ASP.NET\Requests Rejected

ASP.NET\Worker Process Restarts (not applicable to IIS 6.0)

Memory\Available Mbytes

Web Service\Current Connections

Web Service\ISAPI Extension Requests/sec

Monitoring Logs:

Event Log:

It is critical to monitor the event log for messages from ASP.NET and Microsoft Internet Information Server (IIS). ASP.NET writes messages to the application log, for example, each time the aspnet_wp worker process terminates IIS 6.0 writes messages to both the application and/or system logs, for example, each time the w3wp worker process reports itself unhealthy or crashes. It is quite easy to write a .NET application that reads the application log and filters out messages from ASP.NET and IIS, and fires an alert (sends e-mail or dials a pager) if necessary.

W3C and HTTPERR Logs:

First, enable W3C logging for IIS through the Internet Information Services (IIS) Manager. This log can be configured to include various data about the requests, such as the URI, status code, and so on. Scan the log for error codes such as 404 Not Found, and take action to correct links, if necessary. It also contains substatus code in the log and is useful for debugging. IIS uses substatus codes to indentify specific problems. For example, 404.2 indicates that the ISAPI extension handling the request is locked down. 

New for IIS 6.0, malformed or bad requests and requests that fail to be served by an Application Pool are logged to the HTTPERR log by HTTP.SYS, the kernel-mode driver for handling HTTP requests. Each entry includes the URL and a brief description of the error.

Check the HTTPERR log for rejected requests. Requests are rejected by HTTP.SYS when the kernel request queue is exceeded, and when the application is taken offline by the Rapid Fail Protection feature. When the first issue occurs, the URL is logged with the message QueueFull, and when the second occurs, the message is AppOffline. Check the HTTPERR log for requests lost due to a worker process crash or hang. When this occurs the URL will be logged with the message,Connection_Abandoned_By_AppPool, for each in-flight request. An in-flight request is one that was sent to a worker process for processing, but did not complete before the crash or hang.

IIS 6.0 Tuning for Performance by Peter A. Bromberg

In the Patterns and Practices Group's "Improving .NET Application Performance and Scalability", which is available in full text online and as a PDF download from the above link, as well as in softcover through MSPress and major booksellers, there are over 1000 pages and appendixes of detailed information about how to improve .NET application performance and scalability, written by the top experts in the business. One area that is both little understood and potentially confusing is the tuning of Internet Information Services 6.0.

The skinny about all this is that the PAP group says the default settings shipped with IIS and the .NET Framework should be changed. They provide detailed information in pages 332 through 342 in Chapter 6 on ASP.NET, and they provide even more information in Chapter 17. I'll summarize some of the more important points here, since I know that, human nature being what it is, most people running IIS and reading this article probably have not waded through this lengthy but excellent publication. Once you see the quality of the information you can get from it, it may encourage you to do so; it is an investment of your time as a professional ASP.NET developer that I highly recommend. The fact that the book is made available free online by Microsoft should not in any way diminish its importance or value to developers who are interested in achieving the absolute best performance and scalability from their .NET Applications.

NOTE:  This "helper" article focuses almost totally on the IIS-related issues and settings. However, Chapter 6 and additional information in the various checklists and in Chapter 17 address many other issues that are related to, but do not specifically involve IIS 6.0 settings. Some of these can be addressed at the machine.config level, others are "best practices" coding techniques, and some can be addressed in web.config. Paragraphs marked "Discussion" are my individual comments. The rest is (mostly) untouched snippets from the PAP publication itself.

First, lets examine some of the "reduce contention" formula settings. All this information, and a lot more, is right in the book:

GoDiagram Components Add diagrams to improve your user interface, allowing users to more easily visualize and manipulate their data. Bug Tracking Saves Time Save hundreds of development hours each month. Click to see how... Demo Builder - Create Flash Presentations Create interactive Flash movies that allow you to show how applications and systems work.  Download a FREE trial now.

Formula for Reducing Contention

The formula for reducing contention can give you a good empirical start for tuning the ASP.NET thread pool. Consider using the Microsoft product group-recommended settings that are shown in Table 6.1 if the following conditions are true:

• You have available CPU.
• Your application performs I/O bound operations such as calling a Web method or accessing the file system.
• The ASP.NET Applications/Requests In Application Queue performance counter indicates that you have queued requests.

Table 6.1: Recommended Threading Settings for Reducing Contention

Configuration setting Default value (.NET Framework 1.1) Recommended value

maxconnection	2	12 * #CPUs
maxIoThreads	20	100
maxWorkerThreads	20	100
minFreeThreads	8	88 * #CPUs
minLocalRequestFreeThreads	4	76 * #CPUs

To address this issue, you need to configure the following items in the Machine.config file. Apply the recommended changes that are described in the following section, across the settings and not in isolation. For a detailed description of each of these settings, see "Thread Pool Attributes" in Chapter 17, "Tuning .NET Application Performance."

• Set maxconnection to 12 * # of CPUs . This setting controls the maximum number of outgoing HTTP connections that you can initiate from a client. In this case, ASP.NET is the client. Set maxconnection to 12 * # of CPUs.
• Set maxIoThreads to 100 . This setting controls the maximum number of I/O threads in the .NET thread pool. This number is automatically multiplied by the number of available CPUs. Set maxloThreads to 100.
• Set maxWorkerThreads to 100 . This setting controls the maximum number of worker threads in the thread pool. This number is then automatically multiplied by the number of available CPUs. Set maxWorkerThreads to 100.
• Set minFreeThreads to 88 * # of CPUs . This setting is used by the worker process to queue all the incoming requests if the number of available threads in the thread pool falls below the value for this setting. This setting effectively limits the number of requests that can run concurrently to maxWorkerThreads ? minFreeThreads . Set minFreeThreads to 88 * # of CPUs. This limits the number of concurrent requests to 12 (assuming maxWorkerThreads is 100).
• Set minLocalRequestFreeThreads to 76 * # of CPUs . This setting is used by the worker process to queue requests from localhost (where a Web application sends requests to a local Web service) if the number of available threads in the thread pool falls below this number. This setting is similar to minFreeThreads but it only applies to localhost requests from the local computer. Set minLocalRequestFreeThreads to 76 * # of CPUs.

Discussion: The proviso above indicates that these settings should be used when your application has I/O bound operations and the Applications/Requests In Application Queue perfcounter indicates you have queued requests. However, I have found that settings approaching those indicated can improve performance on ASP.NET apps that do not exhibit these conditions. I recommend using the "Homer" web stress tool from at least one remote machine (and preferably more than one machine, with the supplied ASP controller page), or the .NET ACT Application Center Test application, to throw a good solid load at your app and carefully measure the performance statistics with each set of both the default and the above settings. In particular, pay close attention to the Requests per second and the time to last byte readings. This baseline testing scenario should provide the basis for further tuning if it is necessary, and it doesn't take long at all. You can only improve something if you have metrics, and the way you get the metrics is to take the time to get them! You can easily script all kinds of "user paths" through your ASP.NET application with testing software such as is mentioned here, and get the important baseline metrics you need. One more thing-- rule number 1 of software testing and debugging:

"When you are going to change something, ONLY CHANGE ONE THING AT A TIME!" Test it, get the metrics, and only then, proceed.

Kernel Mode Caching

If you deploy your application on Windows Server 2003, ASP.NET pages automatically benefit from the IIS 6.0 kernel cache. The kernel cache is managed by the HTTP.sys kernel-mode device driver. This driver handles all HTTP requests. Kernel mode caching may produce significant performance gains because requests for cached responses are served without switching to user mode.

The following default setting in the Machine.config file ensures that dynamically generated ASP.NET pages can use kernel mode caching, subject to the requirements listed below.

<httpRunTime enableKernelOutputCache="true" . . ./>

Dynamically generated ASP.NET pages are automatically cached subject to the following restrictions:

• Pages must be retrieved by using HTTP GET requests. Responses to HTTP POST requests are not cached in the kernel.
• Query strings are ignored when responses are cached. If you want a request for http://contoso.com/myapp.aspx?id=1234 to be cached in the kernel, all requests for http://contoso.com/myapp.aspx are served from the cache, regardless of the query string.
• Pages must have an expiration policy. In other words, the pages must have an Expires header.
• Pages must not have VaryByParams .
• Pages must not have VaryByHeaders .
• The page must not have security restrictions. In other words, the request must be anonymous and not require authentication. The HTTP.sys driver only caches anonymous responses.
• There must be no filters configured for the W3wp.exe file instance that are unaware of the kernel cache.

Discussion: The "enableKernelOutputCache = "true" setting IS NOT present in the default machine.config "httpRunTime" element. Since it is not present, we should be able to expect that the default setting of "true" is automatic. Personally, I feel better explicitly putting the attribute in there, and setting it to "true". As an aside, I have found that it is ALWAYS a good idea to KEEP A BACKUP COPY of your machine.config stored somewhere safe.

Tuning the Thread Pool for Burst Load Scenarios

If your application experiences unusually high loads of users in small bursts (for example, 1000 clients all logging in at 9 A.M. in the morning), your system may be unable to handle the burst load. Consider setting minWorkerThreads and minIOThreads as specified in Knowledge Base article 810259, "FIX: SetMinThreads and GetMinThreads API Added to Common Language Runtime ThreadPool Class," at http://support.microsoft.com/default.aspx?scid=kb;en-us;810259.

Discussion: The .NET Threadpool is somewhat limited in its flexibility and is specifically limited in terms of how many instances you may have per process, since it is static. If you have ASP.NET applications that specifically need to run background thread processing, you may wish to investigate using a custom threadpool class. I have used Ami Bar's SmartThreadPool with great success, and have even modified it to provide a ThreadPriority overload. You can have more than one instance of this pool, and each can be custom configured. This type of approach provides maximum flexibility while simultaneously permitting individual threadpool tuning of critical resources.

Tuning the Thread Pool When Calling COM Objects

ASP.NET Web pages that call single-threaded apartment (STA) COM objects should use the ASPCOMPAT attribute. The use of this attribute ensures that the call is executed using a thread from the STA thread pool. However, all calls to an individual COM object must be executed on the same thread. As a result, the thread count for the process can increases during periods of high load. You can monitor the number of active threads used in the ASP.NET worker process by viewing the Process:Thread Count (aspnet_wp instance) performance counter.

The thread count value is higher for an application when you are using ASPCOMPAT attribute compared to when you are not using it. When tuning the thread pool for scenarios where your application extensively uses STA COM components and the ASPCOMPAT attribute, you should ensure that the total thread count for the worker process does not exceed the following value.

75 + ((maxWorkerThread + maxIoThreads) * #CPUs * 2)

Evaluating the Change

To determine whether the formula for reducing contention has worked, look for improved throughput. Specifically, look for the following improvements:

• CPU utilization increases.
• Throughput increases according to the ASP.NET Applications\Requests/Sec performance counter.
• Requests in the application queue decrease according to the ASP.NET Applications\Requests In Application Queue performance counter.

If this change does not improve your scenario, you may have a CPU-bound scenario. In a CPU-bound scenario, adding more threads may increase thread context switching, further degrading performance.

When tuning the thread pool, monitor the Process\Thread Count (aspnet_wp) performance counter. This value should not be more than the following.

75 + ((maxWorkerThread + maxIoThreads) * #CPUs)

If you are using AspCompat, then this value should not be more than the following.

75 + ((maxWorkerThread + maxIoThreads) * #CPUs * 2)

Values beyond this maximum tend to increase processor context switching.

Discussion: There is a long list of attention items that revolve around and are tightly woven into the IIS tuning issue for ASP.NET application tuning and scalability. These include, but are not limted to the following:

• Improving page response times.
• Designing scalable Web applications.
• Using server controls efficiently.
• Using efficient caching strategies.
• Analyzing and applying appropriate state management techniques.
• Minimizing view state impact.
• Improving performance without impacting security.
• Minimizing COM interop scalability issues.
• Optimizing threading.
• Optimizing resource management.
• Avoiding common data binding mistakes.
• Using security settings to reduce server load.
• Avoiding common deployment mistakes.

You can find detailed treatment of most of these issues in Chapter 6 of the above-captioned publication.

I hope this brief synopsis of IIS tuning parameters is useful to you. Once again, I strongly recommend reading all this in the bigger context of the book, and mapping out an optimization plan that includes code review, refactoring, and optimization tuning both at the ASP.NET application and IIS webserver levels. One of the great things about the lessons learned from IIS / ASP.NET testing and tuning optimizations is that they can be carried forward to new applications and will improve your skills and value as a professional developer. I spent nearly three weeks at the Microsoft Testing Lab in Charlotte, NC under the tutelage of Dennis Bass and his fine crew, and the lessons learned there were invaluable. If this book were avalable then, I may not have needed to spend so many nights in hotel rooms.

Daemon and rstatd daemon

In Unix and other multitasking computer operating systems, a daemon  is a computer program that runs as a background process, rather than being under the direct control of an interactive user. Typically daemon names end with the letter d: for example, syslogd is the daemon that implements the system logging facility and sshd is a daemon that services incoming SSH connections.

In a Unix environment, the parent process of a daemon is often, but not always, the init process. A daemon is usually created by a process forking a child process and then immediately exiting, thus causing init to adopt the child process. In addition, a daemon or the operating system typically must perform other operations, such as dissociating the process from any controlling terminal (tty). Such procedures are often implemented in various convenience routines such as daemon(3) in Unix.

Systems often start daemons at boot time: they often serve the function of responding to network requests, hardware activity, or other programs by performing some task. Daemons can also configure hardware (like udevd on some GNU/Linux systems), run scheduled tasks (like cron), and perform a variety of other tasks.

Daemon stands for Disk and Execution Monitor. A daemon is a long-running background process that answers requests for services. The term originated with Unix, but most operating systems use daemons in some form or another. In Windows NT, 2000, and XP, for example, daemons are called "services". In Unix, the names of daemons conventionally end in "d". Some examples include inetd, httpd, nfsd, sshd, named, and lpd.

rstatd Daemon

Purpose
Returns performance statistics obtained from the kernel.

Syntax
/usr/sbin/rpc.rstatd

Description
The rstatd daemon is a server that returns performance statistics obtained from the kernel. The rstatd daemon is normally started by the inetd daemon.

Files
/etc/inetd.conf     TCP/IP configuration file that starts RPC daemons and other TCP/IP daemons.
/etc/services     Contains an entry for each server available through Internet.

Transaction Names as Numbers in Loadrunner Analysis

Problem statement  

While generating analysis report the transaction summary has numbers instead of transaction names. This issue is seen mostly in case of test being run in performance Centre. 

The possible reasons for this behaviour are:

• Version conflict between controller and Load Injector.
• Map file was missing on one of the load generators.

Analysis of the load test run showed that the load test had exceeded its timeslot and ended with the error "Executing run failed to stop in a timely manner". Furthermore no load test results were collated for this load test. As a consequence test result collation was performed manually by setting the collator status to "before collating results" and then collating the results.

      Solution

Install correct version of Load generator on all the LG machines.

In order to resolve this problem, where no map file is used and all information is written to the eve file, revert to the old way of writing the results using these steps:

a) Close all the instances of Controller and make sure no test is running.
b) On the Controller machine search and find the file "Wlrun7.ini".
c) Back up the file to save the original version
d) On the file go to [GENERAL] section and add the line: EveVersion=2, close and save the changes.
e) Launch the controller and run the Load Test. If this is controller machine in Performance Centre implementation, perform this operation on every controller machine that is running a load test.
f) Open the results with analysis and all the transactions should be present.
This problem is fully resolved in Load Runner 11 and Performance Center 11, as information to the MAP file is written during the load test itself and not only at the end of the load tests

      Alternate Workaround

Try the following workaround so that Analysis will display names under transaction names rather than numbers in the Analysis Report.

1. Open a new analysis session and change the option there  in Tools ---> Options ---> Result collection --->Choose "Generate summary data only."

The default is "Display summary while generating complete data."

 2. Then, go to File ---> Open ----> Change the "Type of File"  to "Load Runner Results."  The default is "Analysis Session Files."

 Choose the .lrr file and Click on "Open". 

3. The Analysis report will be generated.  Save the .lra file.

Open the .lra file and check the Summary report for transaction names.

Manual results collation in Loadrunner

First, try the Collate option: Controller -> Results -> Collate Results. If this fails, then,
1. On the controller machine, go to Result -> Results setting to verify the result location.
2. Navigate to the result directory on the controller.
3. Locate the file named remote_results.txt and open it in any word editor
4. On remote_results.txt, you will see the location of the .eve file on remote host. For example
   MyHost=c:\temp\brr1\netdir\test\myhost_1.eve
5. Go to that specific host, and copy of the .eve file to the controller machine's result directory.
6. Open the Result file ( .lrr) in Analysis .

If the scenario crashed, or ended prematuerly then set "FullData=1" in the lrr file of the result folder:
1. Open the Result file ( .lrr) in notepad
2. Search for the [Data Collection] section
3. Create/modify the value or FullData to 1
    Example:
    [Data Collection]
    FullData=1

This will help in cases that the result files ( *.eve) contains useful data but the Controller did not manage to write to the lrr that it has the data. FullData will be zero in this case. Setting it to 1 will enable you to view what data was saved.

If the result still failed to come up, it is very likely that the _t_rep.eve file in the controller is not completely generated. In order to save what can be saved, run the scenario again, for a short interval(5 mins) saving the results in a folder different than the old results folder. The _t_rep.eve file and the .lrr file in the new result directory need to be edited. In the _t_rep.eve file there is a line (first one) which maybe something like this:

"28 11 1018300521 2 4627103 22735576 5481115"

·  The 11 is the event code for start scenario.

·  The 1018300521 is the start time and it needs to be moved backward to match the beginning of the first load test.

The same must be done for the scenario start time specified in the .lrr file. For example,

[Scenario]
Start_time=1018300521

Put here the same number.

After this replace the binary eve files(.eve or .gzl) from the new load test result with the ones collected from the LoadGenerators resulted from old scenario run. Back up the data folder in the new results directory. Copy the data folder from the old results directory in the new results directory. Now the results can be analyzed.

If the exact time of start of the first load test is not known. Select any start time that will definitely fall before the estimated start time of the original scenario. For eg; say 10000 seconds less than the one in the new load test. Make a few iteration until you find out what exactly was the first event's time.

Note:

·  The duration of scenario in the new result will not be accurate.

·  It is advised not to overwrite the old result directory in these cases since it consists of the summary data and the monitoring data created even before collation.

vmstat command in Unix

vmstat command

The first tool to use is the vmstat command, which quickly provides compact information about various system resources and their related performance problems.

The vmstat command reports statistics about kernel threads in the run and wait queue, memory, paging, disks, interrupts, system calls, context switches, and CPU activity. The reported CPU activity is a percentage breakdown of user mode, system mode, idle time, and waits for disk I/O.

Note: If the vmstat command is used without any interval, then it generates a single report. The single report is an average report from when the system was started. You can specify only the Count parameter with the Interval parameter. If the Interval parameter is specified without the Count parameter, then the reports are generated continuously.

As a CPU monitor, the vmstat command is superior to the iostat command in that its one-line-per-report output is easier to scan as it scrolls and there is less overhead involved if there are many disks attached to the system. The following example can help you identify situations in which a program has run away or is too CPU-intensive to run in a multiuser environment.

# vmstat 2
kthr     memory             page              faults        cpu
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 1  0 22478  1677   0   0   0   0    0   0 188 1380 157 57 32  0 10
 1  0 22506  1609   0   0   0   0    0   0 214 1476 186 48 37  0 16
 0  0 22498  1582   0   0   0   0    0   0 248 1470 226 55 36  0  9

 2  0 22534  1465   0   0   0   0    0   0 238  903 239 77 23  0  0
 2  0 22534  1445   0   0   0   0    0   0 209 1142 205 72 28  0  0
 2  0 22534  1426   0   0   0   0    0   0 189 1220 212 74 26  0  0
 3  0 22534  1410   0   0   0   0    0   0 255 1704 268 70 30  0  0
 2  1 22557  1365   0   0   0   0    0   0 383  977 216 72 28  0  0

 2  0 22541  1356   0   0   0   0    0   0 237 1418 209 63 33  0  4
 1  0 22524  1350   0   0   0   0    0   0 241 1348 179 52 32  0 16
 1  0 22546  1293   0   0   0   0    0   0 217 1473 180 51 35  0 14

This output shows the effect of introducing a program in a tight loop to a busy multiuser system. The first three reports (the summary has been removed) show the system balanced at 50-55 percent user, 30-35 percent system, and 10-15 percent I/O wait. When the looping program begins, all available CPU cycles are consumed. Because the looping program does no I/O, it can absorb all of the cycles previously unused because of I/O wait. Worse, it represents a process that is always ready to take over the CPU when a useful process relinquishes it. Because the looping program has a priority equal to that of all other foreground processes, it will not necessarily have to give up the CPU when another process becomes dispatchable. The program runs for about 10 seconds (five reports), and then the activity reported by the vmstat command returns to a more normal pattern.

Optimum use would have the CPU working 100 percent of the time. This holds true in the case of a single-user system with no need to share the CPU. Generally, if us + sy time is below 90 percent, a single-user system is not considered CPU constrained. However, if us + sy time on a multiuser system exceeds 80 percent, the processes may spend time waiting in the run queue. Response time and throughput might suffer.

To check if the CPU is the bottleneck, consider the four cpu columns and the two kthr (kernel threads) columns in the vmstat report. It may also be worthwhile looking at the faults column:

• cpu
  Percentage breakdown of CPU time usage during the interval. The cpu columns are as follows:
  • us
    The us column shows the percent of CPU time spent in user mode. A UNIX process can execute in either user mode or system (kernel) mode. When in user mode, a process executes within its application code and does not require kernel resources to perform computations, manage memory, or set variables.
  • sy
    The sy column details the percentage of time the CPU was executing a process in system mode. This includes CPU resource consumed by kernel processes (kprocs) and others that need access to kernel resources. If a process needs kernel resources, it must execute a system call and is thereby switched to system mode to make that resource available. For example, reading or writing of a file requires kernel resources to open the file, seek a specific location, and read or write data, unless memory mapped files are used.
  • id
    The id column shows the percentage of time which the CPU is idle, or waiting, without pending local disk I/O. If there are no threads available for execution (the run queue is empty), the system dispatches a thread called wait, which is also known as the idle kproc. On an SMP system, one wait thread per processor can be dispatched. The report generated by the ps-k or -g 0 option) identifies this as kproc or wait. If the ps report shows a high aggregate time for this thread, it means there were significant periods of time when no other thread was ready to run or waiting to be executed on the CPU. The system was therefore mostly idle and waiting for new tasks. command (with the
  • wa
    The wa column details the percentage of time the CPU was idle with pending local disk I/O and NFS-mounted disks. If there is at least one outstanding I/O to a disk when wait is running, the time is classified as waiting for I/O. Unless asynchronous I/O is being used by the process, an I/O request to disk causes the calling process to block (or sleep) until the request has been completed. Once an I/O request for a process completes, it is placed on the run queue. If the I/Os were completing faster, more CPU time could be used.
    A wa value over 25 percent could indicate that the disk subsystem might not be balanced properly, or it might be the result of a disk-intensive workload.
    For information on the change made to wa, see Wait I/O time reporting.
• kthr
  Number of kernel threads in various queues averaged per second over the sampling interval. The kthr columns are as follows:
  • r
    Average number of kernel threads that are runnable, which includes threads that are running and threads that are waiting for the CPU. If this number is greater than the number of CPUs, there is at least one thread waiting for a CPU and the more threads there are waiting for CPUs, the greater the likelihood of a performance impact.
  • b
    Average number of kernel threads in the VMM wait queue per second. This includes threads that are waiting on filesystem I/O or threads that have been suspended due to memory load control.
    If processes are suspended due to memory load control, the blocked column (b) in the vmstat report indicates the increase in the number of threads rather than the run queue.
  • p
    For vmstat -I The number of threads waiting on I/Os to raw devices per second. Threads waiting on I/Os to filesystems would not be included here.
• faults
  Information about process control, such as trap and interrupt rate. The faults columns are as follows:
  • in
    Number of device interrupts per second observed in the interval. Additional information can be found in Assessing disk performance with the vmstat command.
  • sy
    The number of system calls per second observed in the interval. Resources are available to user processes through well-defined system calls. These calls instruct the kernel to perform operations for the calling process and exchange data between the kernel and the process. Because workloads and applications vary widely, and different calls perform different functions, it is impossible to define how many system calls per-second are too many. But typically, when the sy column raises over 10000 calls per second on a uniprocessor, further investigations is called for (on an SMP system the number is 10000 calls per second per processor). One reason could be "polling" subroutines like the select() subroutine. For this column, it is advisable to have a baseline measurement that gives a count for a normal sy value.
  • cs
    Number of context switches per second observed in the interval. The physical CPU resource is subdivided into logical time slices of 10 milliseconds each. Assuming a thread is scheduled for execution, it will run until its time slice expires, until it is preempted, or until it voluntarily gives up control of the CPU. When another thread is given control of the CPU, the context or working environment of the previous thread must be saved and the context of the current thread must be loaded. The operating system has a very efficient context switching procedure, so each switch is inexpensive in terms of resources. Any significant increase in context switches, such as when cs is a lot higher than the disk I/O and network packet rate, should be cause for further investigation.

Top 10 performance issues with a Database

Here is a list of top 10 performance issues with a Database and their most probable solutions

Too many calls to the DB - There might be multiple trips to a single DB from various middleware components and any of the following scenarios will occur
1. More data is requested than necessary, primarily for faster rendering (but in the slowing down the entire performance )
2. Multiple applications requesting for the same data.
3. Multiple queries are executed which in the end return the same result
This kind of problem generally arises when there is too much object orientation. The key is to strike a balance between how many objects to create and what to put in each object. Object oriented programing may be good for maintenance, but it surely degrades performance if they are not handled correctly

Too much synchronization – Most developers tend to over-synchronize, large pieces of code are synchronized by writing even larger pieces of code. It is generally fine when there is low load, under high load, the performance of the application will definitely take a beating. How to determine if the application has sync issues. The easiest way (but not 100% fool proof) is to chart CPU time and Execution time
CPU Time – is the time spent on the CPU by the executed code
Execution time - This is the total time the method takes to execute. It includes all times including CPU, I/O, waiting to enter sync block, etc
Generally the gap between the two times gives the waiting time. If our trouble making method does not make an I/O call nor an external call, then it’s most probably a sync issue that is causing the slowness.

Joining too many tables – The worst kind of SQL issues creep up when too many tables are joined and the data is to be extracted from it. Sometimes it is just unfortunate that so many tables have to be necessarily joined to be able to pull out the necessary data.
There are two ways to attack this problem
1) Is it possible to denormalize a few tables to have more data?
2) Is it possible to create a summary table with most of the information that will be updated periodically?
Returning a large result set - Generally no user will go through thousands of records in the result set. Most users will generally limit to only the first few hundreds (or the first 3 -4 pages). By returning all the results, the developer is not only slowing the database but also chocking the network. Breaking the result set into batches (on the database side) will generally solve this issue (though not possible always)

Joining tables in the middleware – SQL is a fantastic language for data manipulation and retrieval. There is simply no need to move data to a middle tier and join tables there. Generally by joining data in the middle tier:
1. Unnecessary load on the network as it has to transport data back and forth
2. Increasing memory requirements on the application server to handle the extra load
3. Drop in server performance as the app tier is mainly held up with processing large queries
The best way to approach this problem is to user Inner and Outer joins right in the database itself. By this, the all the power of SQL and the database is utilized for processing the query.

Ad hock queries – just because SQL gives the privilege to create and use ad-hock queries, there is no point in abusing them. In quite a few cases it is seen that ad-hock queries create more mess than advantage they bring. The best way is to use stored procedures. This is not always possible. Sometimes it is necessary to use ad-hock queries, then there is no option but to use them, but whenever possible, it is recommended to use stored procedures. The main advantage with stored procedures is
1. Pre compiled and ready
2. Optimized by DB
3. Stored procedure in on the DB server, i.e. no network transmission of large SQL request.

Lack of indices – You see that the data is not large, yet the DB seems to be taking an abnormally long time to retrieve the results. The most possible cause for this problem could be lack of or misconfigured index. At first sight it might seem trivial, but when the data grows large, then it plays a significant role. There can be significant hit in performance if the indices are not configured properly.

Fill factor – One of the other things to consider along with index is fill factor. MSDN describes fill factor as a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. The fill-factor setting applies only when the index is created or rebuilt. Why is this so important? If the fill factor is too high, if a new record is inserted and index rebuilt, then the DB will more often than not split the index (page splitting) into a new page. This is very resource intensive and causes fragmentation. On the other hand having a very low value for fill factor means that lots of space is reserved for index alone. The easiest way to overcome this is to look at the type of queries that come to the DB; if there are too many SELECT queries, then it is best to leave the default fill factor. On the other hand if there are lots of INSERT, UPDATE and DELETE operations, a nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table.

My Query was fine last week but it is slow this week?? – We get to see a lot of this. The load test ran fine last week but this week the search page is taking a long time. What is wrong with the database? The main issue could be that the execution plan (the way the query is going to get executed on the DB) has changed. The easiest way to get the current explain plan the explain plan for the previous week, compare them and look for the differences.

High CPU and Memory Utilization on the DB – There is a high CPU and high Memory utilization on the database server. There could be a multitude of possible reasons for this.
1. See if there are full table scans happening (soln: create index and update stats)
2. See if there is too much context switching (soln: increase the memory)
3. Look for memory leaks (in terms of tables not being freed even after their usage is complete) (soln: recode!)

There can be many more reasons, but there are the most common ones.

Low CPU and Memory utilization yet poor performance – This is also another case (though not frequent). The CPU and memory are optimally used yet the performance is still slow. The only reason why this can be is for two reasons:
1. Bad network – the database server is waiting for a socket read or write
2. Bad disk management – the database server is waiting for a disk controller to become free

As always these are only the most common database performance issues that might come up in any performance test. There are many more of them out there...