- The Concept of Software Scalabililty
- Factors Affecting Application Performance
- Steps Involved in Tuning
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Steps Involved in Tuning
In this section, I'll provide some general guidelines that I hope you'll find helpful in your tuning efforts. Of course, each development environment is likely to have its own intricacies that won't be covered here; however, applying these principles to your specific environment will get you well on your way to drafting your own optimization strategy.
Step 1: Document Your Application
Before throwing the heavy artillery and many dollars into improving any of the areas mentioned earlier, step back and invest into a thorough study of the application. If you already have the documentation, chances are that such documents can be updated and/or revised. Very few applications remain static; the majority will require bug fixes and service packs after being deployed. Sadly, such efforts often remain undocumented.
Be sure to take notes about the overall application architecture, server configuration, network configuration, any settings of the software used, database schema, scheduled jobs, and any other factors that might affect performance—positively or negatively.
Step 2: Monitor the Application
Monitor the application over a certain period and determine any spikes or drops in performance over time. The length of monitoring depends on the nature of your software, number of users supported, and the type(s) of user activity. Any serious application will require at least two to four weeks of monitoring to come up with a trend analysis. Monitoring tools are available from various software vendors as well as third parties.
Be forewarned that many monitoring tools provide numerous counters you can record and analyze. The more counters you track, however, the more difficult it is to digest the wealth of collected data—it's easy to get carried away. I recommend determining the four or five most important counters, and analyzing only their values. If you don't have sufficient results, you can always go back and track another set of counters.
The type of monitoring you perform depends on the nature of the application. Most applications can benefit from tracking the number of concurrent users, number of transactions per unit of time, duration of the longest and shortest transactions, and the average response time.
Step 3: Determine Acceptable Performance
Get with your users and determine what level of performance they would consider acceptable. You can tune the application for the rest of your life, but whether the query takes two or four seconds to complete might not make a difference to the user. It's easier to get to the goal when you see the goal.
At times users might not be terribly concerned about having to wait a few seconds, but could be happier if you provide "eye candy"—a message indicating that the program is attempting to load a web page or a user interface screen. Designing such an attraction is much cheaper than rewriting the application, so be sure to document any requests of this nature.
It's well worth the effort to document the client hardware used to load your application. If your application's web pages include many pretty graphics, outdated client hardware might cause slow performance. Make sure that users utilize the approved type and version of the browser(s) for viewing web pages.
Keep in mind that the goal you set for your application must be achievable. If your user expects to retrieve a million records within a second, you should really rethink marketing to such a user.
Step 4: Do the Research
Regardless of how many dozens of years you've worked with information systems, it never hurts to do research. See if other companies have had similar issues and how they resolved similar problems. Attending your local user group meetings can be a great way to network and get to know your colleagues. Many professionals will be happy to discuss their tuning experiences with you over lunch. Consider learning from your colleagues, since it rarely pays to reinvent the wheel.
Step 5: Identify the Bottleneck(s)
Once you have input from your users and output from your monitoring tools, you can deduce the portions of the application that need to be optimized. If the entire application is slow and every screen takes several minutes to load, consider rewriting the software from scratch. It's more likely that certain portions of your application are performing fairly well, however, and others don't live up to the users' expectations.
Attempt to identify the application tier that's causing the most problems. Consider the hardware where your web and database servers are running. Is it time to upgrade these computers? If the server load has grown exponentially, you might need to invest in additional servers or consider implementing replication. If the hardware is fine, examine the network capabilities—if the client base has grown from a few to a few thousand, chances are that you need additional bandwidth.
If the infrastructure seems appropriate, it's time to examine the application architecture. Are complex business rules enforced in the database code? The application might benefit from implementing such business rules in the middle-tier components. If you consistently take snapshots of your data due to the reporting requirements, you might benefit from adding a separate, read-only database for reporting; or, better yet, you could build a data warehouse for all of your reporting needs. If you must provide 24/7 uptime, consider clustering your servers.
Once you've thought through the infrastructure and architecture and everything looks good, it's time to examine the data model and code.
Step 6: Define the Priorities
It would be nice if you could fight all the fires simultaneously, but the day won't stretch beyond 24 hours. Your budget will also be limited to a set number of dollars. Once you've identified the application's issues, you need to determine the order in which you can mitigate them.
Priorities are often dictated by the budget. If you have the bucks for the bigger, better, and faster servers, the answer is clear (at least for your executives). More often than not, though, it makes sense to invest in brainpower—in-house developers or outside consultants—to optimize the code or application design. Occasionally it might be more cost-effective to buy a third-party tool and replace the existing application altogether, or at least a portion of it.
Regardless of the direction, determine what's hurting you the most and what you can accomplish with the resources at hand.
It also helps to determine which steps you can perform in parallel. For instance, you can have one team of developers examining the front-end code, while other developers look through the middle-tier components. At the same time, the DBA team can examine the effectiveness of the data model and stored procedures.
Step 7: Take Action
Finally, it's time to implement the decisions you came up with in the previous steps. The next few articles of this series will discuss database-specific optimizations. Database optimizations might have nothing to do with the T-SQL code. There could be a regular maintenance task that can help the performance, or you could benefit from changes in SQL Server configuration.
If you need to tune other portions of your application, be sure to check out the rest of InformIT and get some fine ideas.
Step 8: Document the Resolution
Systems development works in cycles. It's very important to document the work you did today—for the issues you might run across next year. In addition, if you document what helped you in a particular situation, you'll have more knowledge and experience than when you started.
Once you document the problem and resolution, be sure to share it with other IT folks in the community and ask for comments. You'll be surprised by the number of people who will appreciate such sharing. They might also provide a thought-provoking comment that will help you make your solution 100 times more effective.