Andreas Grabner About the Author

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

Database Access Patterns Gone Wild: Inside Telerik, SharePoint and ASP.NET

Telerik Controls are great for building modern, rich clients, and web applications often seen at the enterprise level. I just worked with a company that uses these controls in their soon to be released customer facing SharePoint portal. They ran a small load test which showed that the response times were ranging between 150ms and 1.3s for most of their pages – which is acceptable. Still, they wanted my opinion on the data – so they shared a dynaTrace session with me. I observed a number of problems prior to the production deployment:

  • “Unbalanced” load balancer will bring a server-cluster down
  • Data driven performance problems will kill a database server
  • Wasteful database connection handling will impact app server performance

Check out the following screenshots that explain my steps and findings while I analyzed their performance data. I hope this is also a good guide for any of your own work you do on Telerik, SharePoint or any other frameworks. It is important to understand what happens underneath the hood – – just because it works on a developer’s machine or in a small scale load test, doesn’t mean it will scale in production.

Step #1: Analyzing Transaction Flow

The transaction flow visualizes the flow of all requests end-to-end through the system. Not only is it interesting to see where my performance hotspots are but it is also very interesting to see how load is distributed, how these tiers are communicating with each other and how they interact with external systems such as the database.

Transaction Flow highlights load balancing, failures and bad database access patterns

Transaction Flow highlights load balancing, failures and bad database access patterns

Key Takeaways

  • Operations: Make sure your load balancers are correctly configured. You may also want to add a load balancer between your Web Server and App Server as currently there is a 1:1 relationship.
  • Developers: Analyze the performance hotspots and the database access pattern of your application. It should not be necessary to execute that many SQL statements per transaction.
  • Database Admins: Check with engineering on which database statements they execute a lot and which of them can be optimized on the database server.

Step #2: Analyze Database Access Pattern

The Database view shows me which SQL Statements are executed. It is especially interesting for me to identify SQL Statements that are executed multiple times per single request and whether these statements are then prepared or not. If the same statement is executed more than 5 times I wonder if this access pattern can’t be optimized, e.g: through a different SELECT, through a stored procedure or by caching the result in the app.

Database view shows which SQL statements are called up to 90x on average per request. Most of them are not prepared

Database view shows which SQL statements are called up to 90x on average per request. Most of them are not prepared

Key Takeaways

  • Operations: A good metric to monitor is the number of SQL Statements executed per transaction as well as the same SQL executed per transaction. As things like this can always slip into production it makes sense to monitor this for every transaction.
  • Developers: Optimize your database access. If these statements have to be executed that many times, make sure these statements get prepared. Otherwise think about caching this data in the app instead of requesting it all over again.

Step #3: Data Driven Access Patterns per Request

Drilling deeper into the individual transactions – we call them PurePaths – revealed an even more interesting access pattern. The same web request executed the same SQL statement at different times. It turned out that this was a typical data-driven access pattern. Depending on the user, the web request returned the number of items the user could see – and this varied between only a few and several hundred. The following two screenshots show PurePaths that have lots of SQL Calls that could be optimized. Not only are they using a lot of resources on the database, they also keep database connections busy that might be required by other requests and therefore keep the application from scaling.

Calling the same SQL 493! Times without preparing it is a very bad practice as it causes overhead in both App, Connection Pool and Database

Calling the same SQL 493! Times without preparing it is a very bad practice as it causes overhead in both App, Connection Pool and Database

One slow stored procedure and up to 240 similar SQL statements that follow cause 90% of the transaction performance

One slow stored procedure and up to 240 similar SQL statements that follow cause 90% of the transaction performance

Key Takeaways

  • Operations: Monitor the execution time and count of critical stored procedures. Especially when they show huge differences in response time, it is important to bring this to the DBA’s attention.
  • Database Admins: Analyze these long running stored procedures and see if they can be optimized. Work with Operations to setup correct monitoring and alerting.
  • Developers: Why execute so many similar SQL statements? Wouldn’t this be a candidate to also put into a stored procedure?

Tips for Developers

Do not just accept the fact that these frameworks work based on their functional specification. Look behind the scenes using the tools you have on your desktop – whether it is a debugger, a profiler or a performance management solution. The following screenshots show you how you can see what is actually going on in these frameworks:

Get insight into the frameworks you use to better understand where time is spent and where you can start to optimize your usage of these frameworks

Get insight into the frameworks you use to better understand where time is spent and where you can start to optimize your usage of these frameworks

A lot of the time is spent in waiting for a database connection triggered by Moving through a Telerik Result Set

A lot of the time is spent in waiting for a database connection triggered by Moving through a Telerik Result Set

Tips for Operations & Database Admins

Sit down with engineers and database admins and specify database related monitoring measures to monitor in your production environment. Here are some you should watch out for:

  • Overall number of SQLs executed -> allows you to see general trends
  • Number of SQLs executed per Request -> allows you to see whether you have data driven issues or a recent build changed the data access behavior
  • Connection Pool Size, Usage and Acquisition Time -> allows you to identify sizing problems of your pools or bad connection handling in the app code
  • Slowest Statements executed
  • Top Statements executed

Comments

  1. Good article… Is the app you profiled using Telerik Data Access (formerly OpenAccess)? More specifically, what are they using for Data Access – ADO, EF, TDA? If it’s Telerik Data Access there is a profiler that can be embedded in the application’s code which creates a service that streams to the profiler. The profile is simple but nifty and I’ve used it to profile for N+1 issues before. All said, I think the key takeaway here is to always look at the big picture and know what your application is really doing under the hood – so often if it works we walk away.

    • Thanks David for the input on the profiling option. In your case we used dynaTrace and not only analyze this during development or testing but actually found that problem in a production environment. But you are right – developers need to make sure to look at “the big picture” – they need to look “behind the scenes” to figure out what is really going on within these frameworks. As to your question on the data access: in the last screenshot you can see the call stack where you see that they are using OpenAccess

Comments

*


nine − 2 =