SYS-CON MEDIA Authors: Elizabeth White, Peter Silva, Liz McMillan, Yeshim Deniz, Pat Romanski

Related Topics: .NET, Java, Linux, AJAX & REA, Web 2.0, Big Data Journal

.NET: Article

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.

More Stories By 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

Comments (1)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Latest Stories
Artifactory binary repository management system. As one of the most widely used binary repositories and the only repository that offers a high availability clustered solution, the integration with Artifactory helps customers easily adopt CA Release Automation to optimize their entire software development lifecycle. “Artifactory is a standard maker at the continuous-integration domain and provides the user a powerful repository experience with the freedom to choose his own tools set and ecosyste...
This talk focuses on the application of DevOps fundamentals to include network infrastructure. It draws from real deployment case studies on the extension of today's paradigms to address the challenges of the network infrastructures' ability to seamlessly and cohesively integrate into agile workflows. In this session at DevOps Summit, Arista Networks will focus on configuration management using automation with a nod to future work necessary to include telemetry and ephemeral state information....
Avnet, Inc. has announced that it ranked No. 4 on the InformationWeek Elite 100 – a list of the top business technology innovators in the U.S. Avnet was recognized for the development of an innovative cloud-based training system that serves as the foundation for Avnet Academy – the company’s education and training organization focused on technical training around top IT vendor technologies. The development of this system allowed Avnet to quickly expand its IT-related training capabilities around...
SYS-CON Events announced today that dcVAST, a leader in IT infrastructure management, support service and cloud service, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. dcVAST provides cutting-edge IT services and IT infrastructure management services. dcVAST builds robust systems that are simple, secure and serviceable. dcVAST’s IT infrastructure support and IT services expertise can help companies r...
DevOps Summit, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long developmen...
While Docker continues to be the darling of startups, enterprises and IT innovators around the world, networking continues to be a real mess. Indeed, managing the interaction between Docker containers and networks has always been fraught with complications. Without automation in networking, the vision of running Docker at scale and letting IT run the same apps unchanged on the laptop and in the data center or for any cloud cannot be realized.
SYS-CON Events announced today Isomorphic Software, the global leader in high-end, web-based business applications, will exhibit at SYS-CON's DevOps Summit 2015 New York, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Isomorphic Software is the global leader in high-end, web-based business applications. We develop, market, and support the SmartClient & Smart GWT HTML5/Ajax platform, combining the productivity and performance of traditional desktop software ...
The OnPage SOAP API provides programmatic access to OnPage messaging services. Typical applications include sending messages to individual OnPage users or groups and receiving tracking information for messages such as delivery and read timestamps or reply information. The API is based on open standards known collectively as "Web Services," which includes the Simple Object Access Protocol (SOAP), Web Services Definition Language (WSDL), and the XML Schema Definition language (XSD).
SYS-CON Events announced today the DevOps Foundation Certification Course, being held June ?, 2015, in conjunction with DevOps Summit and 16th Cloud Expo at the Javits Center in New York City, NY. This sixteen (16) hour course provides an introduction to DevOps – the cultural and professional movement that stresses communication, collaboration, integration and automation in order to improve the flow of work between software developers and IT operations professionals. Improved workflows will res...
Docker is becoming very popular--we are seeing every major private and public cloud vendor racing to adopt it. It promises portability and interoperability, and is quickly becoming the currency of the Cloud. In his session at DevOps Summit, Bart Copeland, CEO of ActiveState, discussed why Docker is so important to the future of the cloud, but will also take a step back and show that Docker is actually only one piece of the puzzle. Copeland will outline the bigger picture of where Docker fits a...
Ayla Networks, whose agile Internet of Things (IoT) platform makes it easy for manufacturers to deliver secure, connected products, today announced it has been included in the list of "Cool Vendors" in the Internet of Things report by Gartner, Inc. “Gartner knows how important it is that manufacturers of all kinds of products have the right IoT solution to help turn their products into connected ‘things,’” said David Friedman, CEO and co-founder of Ayla Networks. “The market for Ayla’s IoT pla...
A new definition of Big Data & the practical applications of the defined components & associated technical architecture models This presentation introduces a new definition of Big Data, along with the practical applications of the defined components and associated technical architecture models. In his session at Big Data Expo, Tony Shan will start with looking into the concept of Big Data and tracing back the first definition by Doug Laney, and then he will dive deep into the description of 3V...
SYS-CON Events announced today that CenturyLink, Inc., a leader in the network services market, has been named “Platinum Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. CenturyLink is the third largest telecommunications company in the United States and is recognized as a leader in the network services market by technology industry analyst firms. The company is a global leader in cloud infrastructure and ...
ScriptRock has been included in the list of "Cool Vendors" in the "Cool Vendors in DevOps 2015" report by Gartner, Inc.* ScriptRock provides visibility into the configuration state of an organization's IT environments, enabling the continuous delivery of mission critical services. For enterprises where downtime is not an option, ScriptRock's system-wide overwatch offers the assurance that misconfigurations and anomalies are caught before they affect the business. By satisfying this fundamental ...
As cloud gives an opportunity to businesses to buy services externally – how is cloud impacting your customers? In his General Session at 15th Cloud Expo, Fabio Gori, Director of Worldwide Cloud Marketing at Cisco, provided answers to big questions: Do you see hybrid cloud as where the world is going? What benefits does it bring? And how does Cisco connect all of these clouds? He also discussed Intercloud and Cisco’s investment on it.