02 January,2017 by Jack Vamvas
To get my head back into gear and focused for the New Year , I want to outline some notes on the topic of Database Self Tuning. Database Tuning is all about ensuring service level agreements being met . Database tuning is essential as data sets get bigger with increasing a larger set of access paths to the data.
Following on from my blog post – The future of the SQL Server Performance Tuning Expert , I’ve compiled some notes on Database Self Tuning
Focus is on two aspects
1) What is the current state of Database Self Tuning?
2) How to achieve full automation in the future.
Database Tuning Definition – The sum of all activites completed to make the Database respond within satisfactory levels
Database Tuning focuses on:
2) Response Time
3) Resource Usage
Example, set some constraint e.g memory (Resource Usage) and improve response time (throughput)
What Database Server components can be tuned?
An example Performance Stack.
Fibre Channel Switch
Storage Controller Cache
Database Tuning requires in depth expertise on:
Basic principles of Database Tuning (Shasha)
1) Think globally, fix locally – Track down bottlecnck and resolve
2) Partitioning breaks bottlecnecks – if step 1 doesn’t fix problem , divide the load over more resources or spread the load over time
3) Try to keep things up and running , avoid restarts
4) Render onto server , what is due onto server. Keep the balance between application and DBMS
Read more on the five basic principles Five Basic Principles according to Shasha
Database Tuning – continuous
1) Identify existing Problem
2) Monitor and define cause (root) of problem
3) Apply changes to problem
4) Problem Solved
Database Tuner needs to manage trade-offs – between benefits and cost.
Hardware Tuning – Kill it with Iron – ROI