Digital Edition

SYS-CON.TV
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 that 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

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

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.

For the rest of the steps, and further insight, click here for the full article.

About Andreas Grabner
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

In order to post a comment you need to be registered and logged in.

Register | Sign-in

Reader Feedback: Page 1 of 1

It would be good if you could put high resolution pictures.




ADS BY GOOGLE
Subscribe to the World's Most Powerful Newsletters

ADS BY GOOGLE

Isomorphic Software is the global leader in high-end, web-based business applications. We develop, m...
The standardization of container runtimes and images has sparked the creation of an almost overwhelm...
Enterprises are striving to become digital businesses for differentiated innovation and customer-cen...
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As au...
David Friend is the co-founder and CEO of Wasabi, the hot cloud storage company that delivers fast, ...
The now mainstream platform changes stemming from the first Internet boom brought many changes but d...
Everyone wants the rainbow - reduced IT costs, scalability, continuity, flexibility, manageability, ...
In this presentation, you will learn first hand what works and what doesn't while architecting and d...
In his session at 21st Cloud Expo, Michael Burley, a Senior Business Development Executive in IT Ser...
"Calligo is a cloud service provider with data privacy at the heart of what we do. We are a typical ...
When building large, cloud-based applications that operate at a high scale, it’s important to mainta...
To Really Work for Enterprises, MultiCloud Adoption Requires Far Better and Inclusive Cloud Monitori...
Every organization is facing their own Digital Transformation as they attempt to stay ahead of the c...
Technological progress can be expressed as layers of abstraction - higher layers are built on top of...
DX WorldExpo LLC Leased space at the hotel to present its 22nd International event on November 12-13...
SUSE is a German-based, multinational, open-source software company that develops and sells Linux pr...
Having been in the web hosting industry since 2002, dhosting has gained a great deal of experience w...
In his general session at 19th Cloud Expo, Manish Dixit, VP of Product and Engineering at Dice, disc...
Your job is mostly boring. Many of the IT operations tasks you perform on a day-to-day basis are rep...
Dynatrace is an application performance management software company with products for the informatio...