Database Self-Tuning

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:

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.

CPU Cores

Windows\Linux OS

Database Server

Cache

FC HBA

Fibre Channel Switch

Storage Controller

Storage Controller Cache

Disk

LUN

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)

 

 

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 Self-Tuning

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