As a database administrator (DBA), you’re probably very technically minded. This is essential, since you’re likely charged with evaluating the features and functions of new versions of database management system software as well as monitoring and tuning the myriad business applications you support.
However, in addition to being technically savvy, it’s also imperative you have a strategic outlook. Tactics are extremely useful for reacting to issues; however, if you want to get ahead of potential problems, you need to know what strategies lead to success.
Here are two hands-on strategies that will help you transition from reactive to proactive.
Know Application Breaking Points (aka Potential Application Failure Points)
As the DBA, you’re usually the principle expert on SQL performance and tuning. So it’s only natural you will encounter and accumulate data on SQL and application performance during application development and testing as well as after implementation.
This knowledge, as well as a robust load testing program, provides useful information you can use to prevent performance issues, schedule required maintenance and develop recovery strategies. In addition, if systemwide resource-constraint issues arise, you have valuable knowledge about how various applications will be affected.
Application design and database design are the first points where you begin gathering information. Important items include:
• Capacity. How big are the initial table and index allocations and how fast will they grow? There may be a maximum size constraint for some objects, depending on available disk storage, table and index partitioning schemes, or the time available for object backups or reorganizations.
• Speed. Many transactional systems accumulate data into tables. How fast will this happen? Is there a bulk load or mass insert performance requirement? Are there queries and data extracts that return large result sets and must execute quickly?
• Complexity. Some database designs may involve hundreds of tables or more. Are there queries that may union or join many of these? One example would be a design that has a “union all view,” where a view definition consists of a union of multiple tables. If tables are added to this view, will any SQL statement that uses the view malfunction?
• Design features. Database designs sometimes accommodate business logic. A requirement to purge or archive old data may lead to a table partitioning scheme by date. Referential integrity constraints can be enforced through declarative means (primary and foreign keys), implementing triggers or by application logic. A need to minimize contention may result in row-level locking.
• Data archive and purge. As data accumulates in tables, it may become stale or invalid. This is especially true for time-series or historical data. Consider a table that contains customer order data. As orders are fulfilled, one option is to move corresponding rows to a fulfilled orders table. This reduces the amount of data in the original table and can improve performance. Access to the fulfilled orders is still possible. Designers should consider how much data should be retained, and in what form, based on the application's needs and any compliance or regulatory requirements.
As a project moves into the testing phase, you should have additional information, which hopefully includes a complete record of SQL statements as well as a first look at performance monitoring data. Later, vigorous load testing can produce significant performance and resource usage data.
With all of this data gathered, you now have a much better idea of the conditions where an application will be stressed or break. In addition to basic deductions, such as the maximum number of rows that tables can contain, you can develop shrewd guesses about the following potential breaking points: