Monitoring ADO.NET Performance

One of the most overlooked parts of .NET is the glue that links your application through to a database known as ADO.NET (an evolution of ActiveX Data Objects). The lowly connection string is the only real access we get to the underlying technology.

I was with a client recently and noticed they had the following connection string:

I was interested in why they had a pool size setting of 1500. The answer lay in the fact that 2 years ago they had a pooling issue (much like the one we’ll explore shortly!) and had set the Max Pool Size to 15 times more than the default!  We then set about seeing if that was needed with their current application.

Using AppDynamics we monitored the calls to their database from the Application’s perspective, you can automatically detect the calls:

Down to the methods where the connection and queries are run:

Extracting the Connection String (without the password) and seeing how long it takes to get a connection from the pool:

These snapshots provide deep visibility into an application’s health and performance, but in the case of performance issues – what’s the easiest way to tune production applications? What happens we you start to see the following?:

ADO.NET pools connections for reuse, there are a number of key rules for ADO.NET and using these connections:

  •       “Open Late and Close Early” – Don’t hold on to connections in code, only use them when you need to.
  •       Beware of “Pool Fragmentation” – Connections are pooled according to the connection string plus the user identity. Therefore, if you use Integrated Security, you may get a Connection pool for every user.

To aid tuning the following parameters can be used:

Using Windows performance counters you can monitor multiple ADO.NET metrics, for any .NET deployments I’d recommend monitoring:

  •       The Number of Pooled Connections
  •       Number of Active Connections

Within AppDynamics, you can monitor and baseline in your production application the health of your ADO.NET connection pool strategy.

The following illustrates a custom dashboard created to simply monitor the database throughput rate and response time against the size of a connection pool. In this scenario by slowly ramping up the number of concurrent users on the application, you can visualise whether your application will scale.

As new users are added to the application, the Connection Pool size increases automatically. This metric can be monitored in conjunction with database response time, error rates, and overall load.

SQL Server, for example, has a default maximum connection pool of 100 and default connection timeout of 30 seconds. I often see organisations dropping the Connection Timeout to 10-15 seconds if you are going to fail to get a database connection it’s better to fail sooner than later, leaving your user hanging for 30 seconds!

Utilize AppDynamics database view to monitor average and maximum pool times:

By registering a performance counter, AppDynamics will automatically baseline and then enable you to configure effective alerts for your application.

The following performance counters are off by default (they can be enabled in the Web.Config file see ActivatingOffByDefault):

  •       NumberOfFreeConnections
  •       NumberOfActiveConnections
  •       SoftDisconnectsPerSecond
  •       SoftConnectsPerSecond

By enabling NumberOfActiveConnections, you can visualize how the connection pool size increases.

To add any performance counter into AppDynamics use the following tool:

http://community.appdynamics.com/t5/eXchange-Community-AppDynamics/Windows-Performance-Counter-Configuration-Extension/idi-p/9713

Start monitoring your .NET application, check out a FREE trial today!

.NET Application Performance delivered with AppDynamics 3.3

It’s official: AppDynamics support for Microsoft .NET and Windows Azure is finally here! We’ve got the same Kick Ass Product with the same Secret Sauce–but now it sports a shiny new CLR agent. So whether your apps are Java,  .NET or hybrid, with AppDynamics you have the best of both worlds when it comes to managing application performance.

We thought it was only fair to share our secret sauce and love with the Microsoft community, given that 40,000+ users of the Java community have been enjoying it for over 18 months. Our mission is to simplify the way organizations manage their agile, complex, and distributed applications. For .NET, this means that AppDynamics supports the latest and greatest technologies from Microsoft, including their new PaaS platform Windows Azure.

So, what does this mean for organizations with .NET or Azure apps? Let me summarize:

#1 You get to visualize in real-time what your .NET application actually looks like, along with its health and performance across any distributed production environment. It’s the 50,000 foot view that shows how your application and your business performs in your data center or Windows Azure.

#2 Ability to track all business transactions that flow through your .NET application. This gives you insight into business activity, health, and impact in the event that a slowdown or problem occurs in your production environment. This unique context and visibility helps you troubleshoot through the eyes of the business, so you can see their pain instantly in production and resolve it in minutes. We auto-discover and map every business transaction automatically–so don’t worry about configuration. We’ve got that taken care of.

 

#3 Deep diagnostic information on how your business transactions actually execute through your CLRs and/or JVMs (if you’ve got a hybrid app). This means complete call stacks of code execution with latency breakdowns across all your namespaces, classes and methods, which your business transactions invoke. You get maximum visibility in production with zero configuration, allowing you to perform root cause analysis in minutes.

#4 Ability to plot, correlate, and trend any CLR or OS metric over time–whether it’s logic thread counts, garbage collection time, or simply how much CPU your application CLR is burning. We let you report and analyze all this so you understand your CLR run-time and OS system resource.

Don’t believe us? Sign up for our free 30-day trial and we’ll provision you a SaaS login. You can then download and install our lightweight agents and see for yourself just how easy it can be!

As well as our .NET support, we’ve also crammed in some great innovation into the 3.3 release.

Real-Time JVM MBean Viewer:

In addition to trending standard JMX metrics from the JVM, users can now discover and trend any MBean attributes on the fly for short term analysis in real-time. Our new UI dialogue allows the user to browse through hundreds of available metrics which are automatically discovered and reported at the touch of a button. If the user wishes to convert any MBean attribute into a standard JMX metric they can just click “Create Metric” and AppDynamics will collect and report that metric as standard in the JMX Metrics viewer.

Search Business Transactions by their content/payload:

For example, you might have launched a new product on your application or website and need to understand its performance by looking at all business transactions that interact with that product. With AppDynamics v3.3 users can now search business transactions by any transaction payload. For example, the below screenshot shows how a user can search for all business transactions that relate to the book “Harry Potter”.

Additional Platform Support:

  • Auto-discovery and mapping of LDAP, SAP and JavaMail tiers to business transaction flows for increased visibility.
  • MongoDB support allowing users to see BSON queries and associated latency for calls made from Java applications.
  • Enhanced support for WebSphere on Z/OS with automatic JVM naming pools to help customers identify and manage short-living and dynamic JVM run-times.
All in all another great release packed full of innovation from the AppDynamics team. Stay tuned over the next few weeks for more information on specific 3.3 features.
App Man.