Its only natural that a DBA (Database Administrator) is commonly associated with “fighting fires” and making reactive decisions to daily operations, but that in itself is not healthy for the organization (or the individual!) It is important that there is time for the DBA to plan, observe and prepare for eventual needs of the RDBMS system(s) within the organization. Setting performance metrics, monitoring for events that cross “standardized” thresholds are both ways to achieve this.
The following article can be found online at this location:
Thoughts on Improving DBA Productivity
November 18, 2011
Database administrators (DBAs) need to move away from reactive, repetitive tasks. Use the ideas presented here to become more productive.
DBAs should standardize problem resolution methods and the process of root cause analysis.
Most large IT organizations have multiple teams with which DBAs must collaborate. So, we need to consider how the extended team works together. If teams are in react mode, they tend to reactively approach problems; they concentrate on eliciting symptoms and applying quick fixes rather than searching for underlying causes. This leads to failure; or, at best, inefficient and overworked teams.
The most common reasons DBAs fail are the following.
Unclear problem definition: DBAs tend to see the symptoms and consider that the problem is now completely defined. For example, a long-running SQL statement may be diagnosed as “bad” and needing to be “fixed.” Work then proceeds on tuning the SQL statement without considering possible causes such as poorly organized data, invalid or unavailable data distribution statistics, data locking delays, incorrect lock granularity (row rather than page), disk synchronous I/O delays, or overall system load. Teams must begin by recognizing the difference between symptoms and their possible underlying causes. One way to accomplish this is to ask why a symptom or problem appears.
Making unfounded assumptions: Many experienced DBAs rely on their instincts or gut feelings only to discover that issues surface because their assumptions were false. Consider a distributed Java application that seems to be “locking” the mainframe DB2 data that it accesses, preventing other applications from reading the data. Without knowing the specific connection method and package bind parameters, the DBA may assume the fix will involve appending “WITH UR” (uncommitted read) to the SQL statements from the application. In some cases, non-standard parameters such a s ACQUIRE (ALLOCATE), ISOLATION(RR) or CURRENTDATA(YES) may be the real culprit.
No structured problem-solving process: Using a standard approach can help technical teams.
Stopping with the first good idea: When some teams encounter a problem, one technician will choose a solution and stop. It’s as if the purpose of the process was to generate a single answer. Rarely does the team consider multiple good ideas, which may then be combined or executed in parallel. Teams also need to consider and implement ideas for detecting the symptom or problem if it recurs, preventing the problem, or processes that will automatically detect and correct the problem.
Document, centralize, standardize. Assign quality measures to documents and processes. Productivity for the IT professional means doing the right work while minimizing wasted effort and redundancy. One of the original sources of continuous improvement comes from the Software Engineering Institute at Carnegie Mellon University. Their Capability Maturity Model (CMM) imposes an organizational structure on software development processes and, by extension, infrastructure support processes such as database administration. The model describes processes and practices in terms of a maturity level that can be used as a guide for gauging current maturity and for planning improvements. Continuous improvement is where you work on tasks that increase process and practice maturity.
Consider a process for database change control. Assume the process is poorly documented, uses non-standard scripts, and has no quality measures. Continuous improvement would involve raising the maturity level from repeatable to defined, which might involve improving documentation, standardizing processes, and adding process quality measures.
When implementing a continuous improvement process, follow these guidelines:
• Make the processes repeatable so earlier successes aren’t wasted. This means standardized JCL and scripts that can be executed by anyone without modification, and instructions for execution and for follow-up on failure. Ensure the entire team is aware that processes exist and you have procedures for controlling changes to documentation and processes.
• Standardize. Processes and documentation are all well-written in a standard format. Complete information is available in a centralized area, indexed for ease-of-use. The entire team should be familiar with approved ways to access and update information.
• Manage. Implement initial quality measures. Processes and documentation should be regularly reviewed for possible updates.
Metrics and Dashboards
Be able to answer, “What have you done for us lately?” with numbers and dollars. Many DBAs fail to realize how much IT management depends on numbers when measuring activity or productivity. In today’s climate, the extended IT enterprise will be forced to increase productivity and reduce costs. How can this happen if management can’t measure what the DBA does?
Measures such as the number of tables, indexes, or columns moved to production, or the number of hours spent on a project concentrate on single, one-dimensional tasks; they fail to consider the DBA and the supported DBMS and infrastructure as an integrated system. Consider the performance of an online application from key perspectives:
• The user dimension includes transaction rates, data throughput, and perceived transaction elapsed times.
• The system dimension includes resource usage (CPU, DASD, etc.), availability of high-performance access paths (e.g., existence of indexes), and data clustering.
• The DBA dimension includes scheduling of utilities such as reorg and copy, along with some subjective items such as how well the DBA knows the tables, the SQL, and the application.
To give IT management the numbers they need, you need to find measures of the application that correlate to its performance. You also need the same thing for yourself. What things correlate to your performance?
Measurements typically summarize resources spent on tasks, or measure the state of documentation and processes. The latter measurements include documentation and process quality (readability, repeatability, etc.). Many shops use a time-tracking system to gather resource use.
Without quality measures, it’s difficult to prioritize improvements. Documentation quality measures go beyond good grammar. Teams must decide what quality measures are important; some of these might be:
• Applicability: Does this documentation apply to a large set of general conditions or only a few specific cases?
• Verifiability: Can the documentation be cross-referenced to other documentation or publications such as product manuals? Are there simple means of verifying that the documentation is correct, current and complete?
• Currency: Is the documentation up-to-date? When will the information expire or become useless? Must it be updated at specific times or at the time of certain events?
• Reference- or task-centric: Is the documentation mostly reference material, or will it be used to complete some process? Does it include checklists, flowcharts, or other means of process management?
Start with these and use simple measures such as a one-to five-point scale. It isn’t necessary to be 100 percent accurate; the important point is to use the results to prioritize improvements.
As you fix problems, document the fixes along with your analysis. Compare the quality of the fix document with that of the process and related documentation. If the fixes are higher quality, then your documentation and processes need to be reviewed and updated. If the documentation and process have higher quality, then you need to improve either your problem-solving skills or your follow-up in documentation of repeatable processes.
The notions presented here can help the DBA move away from reactive tasks, initiate quality measures, and offload basic, repetitive tasks. Hopefully, you can use this material as you work to become more productive. Remember to quantify and document your results, then advertise your value.
Also found a useful, short video that highlights a typical day in the life of a DBA, which is also relevant to this post:
Note that the DBA was well prepared with scripts, procedures and tools that helped him quickly identify and resolve the various customer problems encountered. Given enough time to anticipate typical operational problems is a big part of a DBA’s responsibilities.