Database Tuning as a process and automation

Online inspection,  statistics gathering,  query usage and resource consumption are common database server monitoring activities.  On the basis of monitoring ,  depending on the diagnosis database tuning activity occurs.

Automating database tuning is the goal of many Systems Managers.  The issue is : Can all types of tuning activity be automated?

Two key factors generate Database Tuning requirements

1)  Regular System changes

-          users

-          data volume changes

-          number of transactions

-          availability requirements (HA,DR)

-          server consolidation

2)   Database server Requirements change

-          company acquisition

-          application changes

 This requires a Tuning process. Some DBAs  think of Database Tuning as reactive task.  i.e   a user reports performance degradation, and the DBA applies  database tuning methods , solves the problem and closes the Helpdesk request.

 Abstracting the user and DBA the steps are:

1)   Identify existing Problem  (user call, metrics )

2)   Monitor the system and define  cause (root) of problem (performance methodology)

3)   Make changes to problem  (adjust configuration, increase capacity, clear bottleneck)

4)   Problem Solved

 The four steps are repeated.  Some problems are straightforward – such as Log files have filled and no more transactions can be written.  Other problems are more complicated – such as memory bottlenecks.

 An interesting aspect of abstracting the process  is considering automation.  To what extent can a root cause be identified or if not identified?

Can automated responses be created  to common performance problems?

 I spend hours\days on some performance issues , and automating that process would be difficult.  The other day a battery stopped working on a mirrored SVC, forcing traffic to reroute onto another SVC.  The bottleneck caused write activity performance to degrade.  Write activity continued but  10 times slower.

The battery is monitored , but Operations did not act quickly enough,  treating it as a minor.

A DBA,  SAN engineer and network engineer spent a few hours diagnosing the problem and fixing.  Better monitoring and a faster response would have assisted,  but human intervention is required.

 Related posts:

Database Tuning – Five Basic Principles according to Shasha
SQL Server Rapid Tuning