Update Nov 27, 2014: Just posted this YouTube video that shows how to easily identify top SharePoint Performance Problems: SharePoint Performance Analysis in 15 Minutes
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.
- 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.
- 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.
- 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:
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