Database Tuning – Five Basic Principles according to Shasha

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