PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Article

Sybase ASE 12.5 Performance and Tuning

Troubleshoot performance problems

If one of these words is new to you, please take a look at this chapter.

Chapter 13: Deadlock
A deadlock occurs when at least two connections vie for the same resources. However, the locks on the process prevent the process from moving forward. So the process appears to stall because it's waiting for an opposing process to complete its next step, but the opposing process is doing the same thing. A deadlock is related to the locking mechanism inherent in all databases. This means that all databases will have some deadlocks. This problem exists in all databases and can't be totally avoided. However, the occurrence of deadlock can be minimized through the use of carefully written queries and stored procedures and by following some relatively basic rules. Even selecting with group by or with aggregate functions can cause hidden deadlock in tempdb databases. However, the occurrence of deadlocks can be minimized by using the techniques described in this chapter.

Chapter 14: Configuration
Sybase ASE uses configuration parameters to control various aspects of the Adaptive Server's operation and performance. Each parameter is initialized to a default value when Adaptive Server is installed. The defaults are listed in the system administration manual, and you should carefully review each default to ensure it's appropriate for your system. While it's easy to identify certain parameters that almost always require modification, some other parameters may be more difficult to pinpoint. Administrators often overlook parameters that could improve their server's performance. In this chapter, different configuration parameters that have been added to ASE since version 11.5 are discussed.

Chapter 15: How to Read sp_sysmon Output
With SQL Server system 11 and onward, Sybase introduced a new tool, system stored procedure sp_sysmon. This tool helps monitor SQL Server performance. It should be used to get the baseline performance profile, as it reports several tasks and the internal information of the Adaptive Server. Based on the output received, we are able to understand the causes and thus are able to interpret the results so that further actions could be taken. sp_sysmon is a specialized system procedure used to produce a statistical report on Adaptive Server activity. It provides an overall performance picture for SQL Adaptive Server, allowing you to observe Adaptive Server from the inside. After reading this chapter, you'll be familiar with this procedure and be able to interpret the results properly.

Chapter 16: Network Performance
Obtaining optimal performance from the network can involve tuning one or more of the following: the operating system, network (LAN/WAN) layout, machine configurations, and ASE configurations. The purpose of this chapter is to provide tips and suggestions that will help maximize the performance of the network, and it also describes how to find network problems such as:

  • Queries show transient fluctuations in response time for no apparent reason. The problem resolves itself with no change to application, query, or data structure.
  • Performance and throughput are very sensitive to overall network load/activity. You'll notice unexplained swings in throughput.
  • The server experiences significant spikes in disconnect and timeout errors.
  • Application processes begin to fail due to time slice errors.
Chapter 17: Performance Metrics
How do you measure performance? Unless you can quantify the results of the tweaks you make to your system, how do you know that your changes were positive? From the very first chapter, we stated that your performance changes had to be measurable and reproducible. So, what do you measure? What do you look for? These are the questions answered in this chapter; what I liked was the idea of categorizing what information to look for:
  • CPU: Are there enough processing cycles to handle our load as the load changes?
  • Memory management: Do we have sufficient memory and is it being utilized optimally?
  • I/O: Is it load balanced?
  • Network: Is our system performance being limited by a hardware resource that is beyond the scope of our server?
Chapter 18: The Audit System
Sybase Adaptive Server auditing provides the ability to record the occurrence of various events on the server. Auditing can record every single event on the server, such as logins, logouts, database access, administrative actions, inserts, deletes, and server reboots. For the audit system to work effectively and efficiently, it must be properly installed and configured. On a properly configured and tuned server with adequate capacity, enabling this feature will have minimal impact on the overall performance of the server. If the system has marginal resources, administrators may experience degradation as the number of audited tasks increase. This chapter describes in detail who and what can be audited and how to install and set up the auditing system. If you need auditing, this chapter is a interesting one.

Chapter 19: Abstract Query Plans
This chapter describes the abstract query plans that are saved with their text in the table sysqueryplans. These plans are called full abstract plans, which list exactly what to do in every step of the query; or partial query plans, which list at particular steps what join strategy to use or index or make "hints' as to what the optimizer should pick to arrive at the best plan. The description of the query plan language is quite interesting, so everyone reading this chapter understands and is able to interpret a query plan.

Chapter 20: Miscellaneous Topics
This chapter covers a few topics that don't seem robust enough to require an entire chapter, yet the authors thought that they needed to be addressed in a performance and tuning book. These topics include bcp, dbcc, and tempdb performance issues.

Conclusion
This book answers a lot of the really tough questions about performance and tuning in a clear and simple way. Much of the information is what you would get if you happened to have a real Sybase performance expert onsite. If you've ever had a really tricky performance question, you already know how nice it is to find information about Sybase monitoring tools and performance troubleshooting all within a single source. My opinion is that you don't have to look further. This book appears to have been written by people with a lot of experience troubleshooting performance problems and they are able to describe solutions in an easy-to-understand manner. I wish you a happy Sybase ASE tuning time with this book.

SIDEBAR:
Sybase ASE 12.5 Performance and Tuning
Paperback: 413 pages
Publisher: Wordware Publishing, Inc.
ISBN: 1556229089
Authors: Jeffrey Garbus, Eric Miner, Joel DuPlessis, Alvin Chang, Yuval Malchi

More Stories By Berndt Hamboeck

Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under [email protected]

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.