05 July,2011 by Jack Vamvas
Database Tuning is the sum of all activites completed to make the Database respond within satisfactory levels. Of course, there are many ways of achieving satisfactory levels.
DBAs employ different Database Tuning techniques, focused on solving emergency problems through to designing a system that scales as workload increases
Beyond the techniques, Dennis Elliot Shasha outlined five basic principles in 1992.
The internals of DB2, Oracle and SQL Server are different but there are performance issues that cut across all systems.
The five basic principles outlined by Shasha are :
Gather sufficient metrics, across the relevant layers to make an informed decision. For example, a DBA identifies a high percentage of one particular wait type, indicating a wait on a resource. Consider correlating with an OS perfmon metric to create accurate diagnostics
The DBA produces diagnostics , identifies and resolves the local bottleneck with minimal intervention
The DBA priority is to resolve the bottleneck and speed up the database response.
If step 1 doesn’t fix problem, the DBA has two options.
a) Divide the load over more resources .e.g scale out , partitioning.
b) Spread the load over time e.g batch , adjust schedules
All layers of the database server consume a high level of resources upon start up.
Applies to : creating execution plans, create database connections
Try to keep things up and running , avoid restarts.
Maintain the relevant workload balance between application and DBMS.
By increasing speed , what is the impact on:
Adding an index has a disk storage impact
Return on Investment (ROI)
Exploring Database Tuning and Five Basic Principles according to Shasha offers solid principles for database tuning. Even in an era where hardware upgrades are becoming cheaper, the database tuning expert has to justify there cost and the The future of the SQL Server Performance Tuning ExpertAuthor: Jack Vamvas(http://www.dba-db2.com)