Database Self-Tuning

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:

1) Throughput

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.

Database Tuning requires in depth expertise on:

1) Application

2) OS

3) Hardware

4) RDBMS

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

5) Repeat

Database Tuner needs to manage trade-offs – between benefits and cost.

Hardware Tuning – Kill it with Iron – ROI

Read More

Performance Tuning series – Performance Stack

RAID levels overview

SQL Server – tempdb and solid state drives

Database Tuning for complex sql queries (DBA DB2)