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

Early Bird Registration Discount Expires on August 31, 2018 Conference Registration Link ▸ HERE. Pic...
Blockchain is a new buzzword that promises to revolutionize the way we manage data. If the data is s...
Modern software design has fundamentally changed how we manage applications, causing many to turn to...
Wasabi is the hot cloud storage company delivering low-cost, fast, and reliable cloud storage. Wasab...
David Friend is the co-founder and CEO of Wasabi, the hot cloud storage company that delivers fast, ...
Containers and Kubernetes allow for code portability across on-premise VMs, bare metal, or multiple ...
Andi Mann, Chief Technology Advocate at Splunk, is an accomplished digital business executive with e...
In addition to 22 Keynotes and General Sessions, attend all FinTechEXPO Blockchain "education sessio...
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As au...
Gym Solutions is a software as a service (SaaS) solution purpose-built to service the fitness indust...
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: D...
SUSE is a German-based, multinational, open-source software company that develops and sells Linux pr...
Mid-sized companies will be pleased with StorageCraft's low cost for this solution compared to other...
Yottabyte is a software-defined data center (SDDC) company headquartered in Bloomfield Township, Oak...
All in Mobile is a mobile app agency that helps enterprise companies and next generation startups bu...
Technological progress can be expressed as layers of abstraction - higher layers are built on top of...
Every organization is facing their own Digital Transformation as they attempt to stay ahead of the c...
Lori MacVittie is a subject matter expert on emerging technology responsible for outbound evangelism...
A traditional way of software development efforts reimbursing is pay by the hour, which in case of r...
Your job is mostly boring. Many of the IT operations tasks you perform on a day-to-day basis are rep...