Now that we’ve addressed an easy configuration change that one of our customers made in order to collect the full command text for large SQL queries, it seems appropriate to provide a quick tip for further fine tuning of AVIcode’s monitoring and data collection related to SQL queries.

By default, AVIcode’s solutions are configured to collect only the first 20 parameters and associated values within a SQL command. There are cases, though, where an application passes more than 20 parameters, and application performance troubleshooting efforts require you to extend this diagnostic data collection.

This is a straight forward configuration change, much as collecting the full command text change described earlier:

1.      Locate, backup and edit the configuration file on the monitored server: %Program Files%/AVIcode/Intercept/Agent/%Version%/Configuration/

a.      If using the .NET 1.1 framework, the configuration file is PMonitor.default.config

b.      If using .NET 2.0 or later, the configuration file is PMonitor2_0.default.config

2.      Next, find the class with the command definition related to the database provider. For example, System.Data.SqlClient.SqlCommand or System.Data.Odbc.OdbcCommand.

3.      Within the command definition, edit the detailLevel value for the parameters member. The default value is “3/20”, where the second number is the maximum number of parameters collected. AVIcode recommends changing the value to “3/40”, but depending on your specific needs, a different number may be required.

4.      Finally, save the configuration file and recycle IIS to start collecting all parameters values for the SQL queries and stored procedures .

Application monitoring requirements vary greatly between applications and organizations. There is no one-size-fits-all approach. AVIcode’s application performance monitoring solutions are designed to be highly scalable and easily configurable in order to meet varied and evolving monitoring needs.  This quick tip demonstrates just another example of AVIcode’s extensibility and flexibility.

Detecting, identifying and diagnosing the root cause of application failures or performance bottlenecks can be a challenge without an application performance monitoring solution that is suited for in-production, always-on monitoring.  The problem resolution challenges are further complicated when you take into consideration the distributed nature of applications and the multiple dependencies of that application.

AVIcode’s low-overhead APM solutions are uniquely suited for in-production monitoring. Recently, we had a customer tell us about the value of monitoring in-production when it came to an issue with a dependency on a database.  AVIcode was able to help that customer detect the details of a performance issue, including the SQL command text. The challenge for the customer was that AVIcode’s default configuration limited the command text to collect only the first 4KB of data, which is generally enough to isolate a problem. But, in this situation, the application in question was using very large queries, instead of stored procedures, and extending the default collection limits was the best option.

Fortunately, this was an easy configuration change: 

1.       The customer located, backed up and edited the configuration file in this folder on the monitored server: %Program Files%/AVIcode/Intercept/Agent/%Version%/Configuration/.  If using .NET 1.1, the file to modify is PMonitor.default.config. In this case, and with .NET 2.0 and later framework versions, the file to modify is PMonitor2_0.default.config.

2.       Next, the customer found the class with the command definition related to the used database provider like SQL, ODBC, DB2, OleDb. For example, this could be System.Data.SqlClient.SqlCommand or System.Data.Odbc.OdbcCommand.

3.       Within the command definition, the stringLimit value for the cmdText member was edited. The default value is “4196”, and we recommended adjusting this value to “10240” for the large SQL statement.

4.       The last step was to save the configuration file and recycle IIS. The full SQL command text was then received.

With this easy configuration change, AVIcode's customer was able to capture the full command text valuesof their SQL queries and quickly identify and resolve their application performance issue.

By default, Intercept Studio does not collect parameters or member variables if they have a null value. This is because, typically, seeing a null value is not critical while examining the root cause details of application problems. However, if you are struggling with a System.NullReferenceException or System.ArgumentNullException issue, collecting null values may be critical to successfully diagnosing and troubleshooting the problem.

Enabling Intercept Studio to collect null values is simple. First, update the agent configuration file, PMonitor.config, by adding the following item to the "options section": <ss:emptyObjects print="true" />. Then save the configuration file and restart the application. That's it - you're on your way to resolving the null reference or argument exception.

In our previous post, we provided an example of a security issue that could not be diagnosed solely with event details collected by ASP.NET health monitoring. Some developers have asked us about the open source Error Logging Modules and Handlers (ELMAH) and how that would have helped in this same situation.

ELMAH is becoming a popular tool for error logging. Many find it more flexible and easier to use than ASP.NET health monitoring. Both tools provide good visibility into application health state changes, including start, shutdown, heartbeat, audit messages, etc. ELMAH extends the exception problem details available via health monitoring to include the ability to log error data from custom error handlers.

The ELMAH API also provides support for different data storage methods and web-based access for error data. In production environments, this data often helps improve and speed support team access to error data.

This information is very valuable for troubleshooting efforts. However, AVIcode's ability to collect runtime parameters that are passed through the methods in the call stack makes root cause diagnosis much easier and more straightforward than health monitoring and ELMAH. In addition, AVIcode's monitoring solutions provide greater visbility into all the problem details for both handled and unhandled exceptions, as well as performance problems. These details can also be consolidated and correlated with key performance counters over time to provide a more complete picture of application behavior and its effect on the IT environment.

One common example of a typical exception that is very difficult to diagnose with ELMAH or other standard error logging APIs is a NullReferenceException. For this (and all other) application problems, AVIcode collects the runtime parameters and member variable values, enabling analysis of the code stack and determination of the application health state. The screenshot below shows a typical example with the AVIcode collected data. 

In addition, AVIcode enables linking to the PDB file, even after data is collected in production, in order to show the source file name and precise line number where the exception occurred. This is accomplished without the PDB files needing to be deployed to a production server, which is typically against corporate IT policy and certainly not recommended. 

Another advantage that AVIcode provides over ELMAH is a secured, centralized web console for managing data access.  The AVIcode console can be integrated with Windows or Forms authentication and allows for differentiating access privileges based on logical hierarchies and account/application log access rules.  The console is web-enabled, so authorized users can view, search, filter and group data by various parameters. For example, grouping errors by unique issues, with a count of instances or occurrences, helps to eliminate the clutter created by multiple instances of the same error.

ASP.NET health monitoring is an easy way to monitor the health of deployed web applications and to gather information related to the health status and performance characteristics of instrumented applications.

What happens, though, when an ASP.NET health monitoring event does not contain the root cause diagnostics related to isolate and resolve an exception? It's a situation AVIcode encounters with organizations quite often, even those companies that are .NET experts and masters at configuring ASP.NET health monitoring.

We recently came across this situation with a security problem that remained undiagnosed by ASP.NET health monitoring. Because the application was already in production, re-configuring health monitoring and modifying the application code was not a reasonable option. Rather, the organization chose to install Intercept Studio.

The following screenshot depicts a typical exception event, similar to what this organization was able to collect, for the security failure. All runtime information, including the call stack, page and user details, parameters passed, custom user code methods, and additional details throughout the exception chain are provided to ensure rapid problem identification, triage and resolution.

In the case of the security problem this particular organization was encountering, Intercept Studio also provided the resource name, failed action details and security context.

The following screenshot shows the event details for a more sophisticated security issue which occurred within the SQL server executing a stored procedure that caused the application code to fail. 

The fact is, it is not possible to be always-prepared for any problem that can occur. And, problems do occur. When instrumentation is not enough, and when modifying in-production code is not an option, a low-overhead solution like Intercept Studio can be implemented on-the-fly to monitor the application state and speed problem resolution by clearly showing you what is going wrong and why.