CATEGORY: Database Performance & Systems Management

REVIEWER: Richard Leeke, technical architecture advisor for Landonline, Land Information New Zealand.

BACKGROUND: Land Information New Zealand (LINZ) is the government department responsible for the registry of land title and survey information for New Zealand. LINZ recently completed a major project to automate survey and title processing. The project, Landonline, took seven years and involved the conversion of 7 million survey and title records as well as radical changes to the operation of the department. The resulting Landonline system provides online access to digital survey and title records for internal staff and several thousand registered external customers.

PLATFORMS: Sql Power Tools is monitoring an Informix database running on a Sun E4500 under Solaris 8. The Windows client is running on Windows 2000 workstations.

PROBLEM SOLVED: The diagnosis and resolution of application performance issues has been a difficult ongoing task during the rollout of Landonline, due to the complexity of the application and the mixture of technologies involved. The application incorporates both high-volume OLTP-style transactions and complex and resource intensive spatial operations. The application has also integrated document image management software from Filenet for accessing the millions of scanned images of historical records. In the early stages of the rollout, it was frequently difficult to determine the root cause of performance problems reported by users because monitoring of individual components of the infrastructure did not provide statistics in a format that could be correlated to the end-user experience. We purchased the Sql Power Tools Zero Impact products to provide the data necessary to perform the diagnosis of such performance issues. The tools are also used to help prioritize effort on tuning activities by identifying which areas would have most overall impact on performance. One of the major benefits has been the provision of an objective measure of system performance.

PRODUCT FUNCTIONALITY: Using the summary metrics captured by the Zero Impact Service Level Monitor, we publish a real-time graphical view of system performance on the LINZ intranet, which is followed closely by both management and support staff. This has clearly shown when system problems have been responsible for reducing staff productivity, which leads to backlogs in processing. Analysis of summary statistics derived from the tools allows a quick determination of whether problems are related to the database or elsewhere in the infrastructure. For database issues, drilling down into the detailed data provided by the tool has led to a prioritized series of performance improvements, which have provided a major contribution to the elimination of processing backlogs. One further unexpected benefit has been the ability to diagnose obscure and intermittent application issues days after they occur by inspecting the Zero Impact logs from the time of the incident.

STRENGTHS: The product's main strength is the ability to capture comprehensive details of all SQL processed in a high-volume production environment, with little or no impact on the performance of the system. In the LINZ environment, we run the monitoring tool on the production Sun server in order to provide visibility of SQL generated "in-box" by a middle-tier server component. We log 4 to 5 million SQL statements per day, generating several gigabytes of log files, with only minor additional load on the server.

WEAKNESSES: The architecture of the product means that it can only see SQL statements passed to the database over the network, but not operations called from within stored procedures, triggers or views. Similarly, as the tool relies on inspecting the network traffic between client and server, it cannot provide diagnostic information about what is actually happening at the database level. Both of these issues are outweighed by the advantage of being able to capture comprehensive SQL in a production environment.

SELECTION CRITERIA: Zero Impact was the only tool that was sufficiently unobtrusive to be viable for comprehensive monitoring and logging of all SQL processed by our production database.

DELIVERABLES: The product outputs both details of individual SQL statements and derived summary statistics and reports to flat log files for subsequent analysis. A GUI tool allows real-time monitoring of service levels and displays details of SQL statements meeting defined alert criteria.

VENDOR SUPPORT: The responsiveness and accuracy of technical support has been outstanding. A number of new features have been added to the product in response to our requests, and turnaround time has been outstanding.

DOCUMENTATION: The documentation is accurate and reasonably comprehensive; however, it is likely that support assistance may be required in addressing configuration details in some specific environments.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access