Database Tuning – Five Basic Principles according to Shasha

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 :

 1)  Think globally, fix locally

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

 2)  Partitioning breaks bottlenecks

 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

3)  Startup costs are high; running costs are low

 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.

 4)  Render onto server , what is due unto server.

 Maintain the relevant workload  balance between application and DBMS.

 5)  Be prepared for trade-offs

By increasing speed , what is the impact on:

Disk IO

CPU

New hardware

Adding an index has a disk storage impact

Return on Investment (ROI)

Speed costs.

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 Expert

Author: Jack Vamvas(http://www.dba-db2.com)
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning – Five Basic Principles according to Shasha

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright