Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 11, 2011

Delivering High Performance with Zero Administration

By DatabaseJournal.com Staff

By Jose Ramos

Advances in self-management capabilities are making it possible to maximize performance and prescribe changes and ongoing maintenance tasks with fewer demands on IT staff.

With IT hiring flat, it's no secret that organizations have been under downward pressure to increase IT efficiency. This has lead to the rapid adoption of technologies and applications that help systems administrators do more with less. Indeed, automation has become a serious business, particularly for software vendors who rely on a database as an integral component of their solution.

In direct opposition to the IT labor trend, the amount of digital information collected and stored each year keeps growing at an exponential rate. In 2009 it grew 62 percent, to about 800,000 petabytes, and it will grow by a factor of 44 by 2020, says IDC1. To manage these much larger and more complex data ecosystems companies are turning to autonomic systems that can perform diagnostics, tuning, maintenance and deployment—among other things—with minimal human intervention.

These self-managing databases can diagnose conditions and make adjustments to optimize performance, availability and resource utilization, ensuring that they are efficient and meet service-level agreements. And because they can do this without increasing IT labor costs, they provide a very low total cost of ownership as compared to traditional databases.

Essential for Embedded, Small and Medium Businesses

The hands-off nature of self-managing database systems make them a handy investment for any organization but there are two common scenarios in which they are essential:

1.  Organizations that want to embed a database in a turnkey software solution or an autonomous embedded system

2.  Small and medium businesses with minimal or no dedicated IT staff

The concept of an embedded database is inherently zero maintenance. Devices such as kiosks, parking meters and some consumer electronics are deployed with embedded database applications that will never be monitored or managed by an administrator. In other cases, ISVs create and sell software applications that are wholly and indefinitely self-sustaining and self-contained.

Because self-managing database systems are inherently zero maintenance, they are a perfect solution for small businesses that don't want to hire a data management expert. This is a very common scenario today with businesses such as private practice law firms, physicians and independent retailers. Unlike traditional client-server databases, a self-managing database is very easy to set up, and once the initial set up is complete, there's very little else that will ever need to be done.

Assuring High Performance

If a database doesn't perform well, little else matters. It's no surprise, then, that performance optimization tops the list of self-managing capabilities.

Cost-based optimization

Self-managing databases use a cost-based optimizer that chooses the execution strategy with the lowest estimated cost for each query upon invocation. This feature is particularly important for ISVs that market applications to a broad spectrum of customers with varying database sizes and workloads.

The cost-based optimizer in SQL Anywhere, for example, is highly adaptive and uses sophisticated algorithms to make its decisions. It utilizes a variety of metrics to estimate a query's execution cost. These metrics include the size of the available buffer pool, estimated disk transfer time, the server multiprogramming level, table cardinality and row length, and the number of tables, table functions or complex subqueries involved in the join strategy. Many of these metrics are automatically adjusted by the database server during normal operation.

Dynamic memory management

When a database system is embedded in an application as part of an installable package, other processes may be executing concurrently, such as an email client, anti-virus software, and management services. This makes it more difficult to predict the system load or the amount of memory that will be available to the database server at any point in time. To combat this, SQL Anywhere possesses several techniques designed to ensure that its buffer pool is kept appropriately sized in relation to its workload and the amount of available physical memory (see Figure 1).

The Feedback Control Loop
Figure 1. The Feedback Control Loop

The database server maintains a single buffer pool to handle all requests, which is advantageous to ISVs as there is no need to pre-configure the number and size of separate buffer pools when the workload is unknown. The database server attempts to set its buffer pool size to match the current server workload, and at the same time respect any request by the operating system to give up physical memory for use by other applications.

In addition, the database server supports adaptive query execution techniques that enable the server to continue to execute SQL requests when the amount of available memory is reduced. Moreover, the database server can intelligently swap internal data structures from the buffer pool to disk, thus permitting the server to continue operation even when the amount of available memory reaches a critical point.

Self-managing and self-healing statistics

Query optimization is dependent on the creation and storage of quality statistics about tables and indexes. Self-managing databases such as SQL Anywhere automatically collect and maintain statistics as part of query execution, for use in subsequent queries. A built-in statistics governor plays the role of the DBA by automatically evaluating the health and usefulness of each statistic in the database. When necessary, self-healing actions are undertaken to correct and/or repair this statistical metadata without impacting database performance. These self-healing actions include:

  • Recording statistics usage and estimation errors using query feedback
  • Automatically repairing or recreating statistical metadata that is inaccurate
  • Ceasing automatic maintenance of statistics as appropriate
  • Creating potentially useful statistics when they are missing from the database
  • Deleting unused statistics

Automatic tuning of the multiprogramming level

The multiprogramming level (MPL) of a database controls how many requests are allowed to execute concurrently. Traditionally, DBAs run load tests on their data to find the optimal MPL level, as setting it improperly can take a toll on application performance. Leading self-managing databases have developed sophisticated algorithms to calculate the optimal concurrency level automatically. Self-managing MPL offers similar performance to the best hand-tuning and can be a boon to ISVs as once again the server can automatically determine the right MPL for any given workload.

Minimizing Administration

Low maintenance is the hallmark of the self-management value proposition. Thus it makes perfect sense that the most common administrative events occur regularly without intervention in a self-managing database.

Triggers and schedules for event-based maintenance

In a self-managing database, events take the place of manual DBA involvement. SQL Anywhere, for example, uses events to automate:

  • Backups
  • Rebuilds
  • Notifications
  • Validations
  • Fragmentation reductions

Such routine tasks are automated by adding an event to the database and providing a schedule for that event. The schedule instructs the database when to perform regular maintenance; similarly, irregular events such as notifications, which are traditionally used to let administrators know when problematic conditions may soon arise, can be automated by defining trigger conditions within a system event. This eliminates the need for DBAs to manually monitor database health statistics.

Automatic recovery from OS or server failure

In the case of an operating system or server failure, the database must be recovered from backup. In a self-managing database, such recovery is entirely automated. At startup, the database makes a self-check to see if the most recent database session shut down cleanly. If not, the database server executes an automatic recovery process to restore all changes up to the most recently committed transaction.

Automatic startup and shutdown

Finally, a self-managing database does not require explicit startup and shutdown. SQL Anywhere, for example, can be started via a simple client API call from the application, and can shut down automatically when the last connection disconnects.

Effortless Deployment and Ongoing Maintenance

It doesn't matter how great the ongoing self-management features of a database are if its deployment is complex and costly. A painless deployment is essential to the low TCO of self-managing solutions, as high deployment costs will quickly negate all the other benefits of self-management.

No need for specialized install program

Because self-management begins with deployment, a proper self-managing database should deploy using a minimal set of files. The location of these files defines where data and configuration alike will reside. A SQL Anywhere database deployment needs less than a dozen files and requires less than 20 MB of available disk space, allowing for quick and effortless distribution.

Portability of database

Because there is no need to create a specialized installation package, a user can easily move a database instance from one server to another, without platform compatibility issues. The database file doesn't need to be converted when moved from Windows to Linux, for example. The entire database can be moved from one machine to another with plug-and-play-like simplicity, which is especially useful during troubleshooting or routine maintenance where the administrator may be running a different OS than the end user.

Design and workload analysis tools

Schema definition is a requirement of database deployment, and self-managing databases are no exception. A poorly designed schema will have a limiting effect on query optimization and performance in general. Sybase has made schema design and workload analysis another facet of self-management. While hand-tuning of the schema is still possible, for those who lack the expertise or motivation to spend time on ongoing database definition, the Application Profiler in SQL Anywhere will make recommendations for schema improvements as conditions change.

Moving Toward an Automated Future

With high performance and low TCO, self-managing databases are an ideal solution for a wide variety of enterprise data uses, not just environments that require zero administration. For more information on Sybase SQL Anywhere 12, visit www.sybase.com/sqlanywhere.

About the Authors

Jose Ramos, Product Manager, SQL Anywhere, Sybase, with contributions from Mohammed Abouzour and Glenn Paulley

1. The Digital Universe Decade – Are You Ready?
May 2010
By John Gantz and David Reinsel

Sybase Archives

Latest Forum Threads
Sybase Forum
Topic By Replies Updated
DB Error Help Prepared Statements sjulian 2 July 5th, 04:58 AM
DB Error Help Prepared Statements sjulian 0 May 31st, 07:19 AM
Test, just a test XRumerTest 0 May 5th, 04:57 AM
Execute Procedure in SQL statement vcs1161 0 August 24th, 07:38 AM