Database tuning and Operating System Tuning

01 August,2011 by Tom Collins

As part of the Performance Tuning series – Performance Stack , this section looks at Operating System tuning.  The purpose of the series is a general approach to Performance Tuning – independent of database server platform.   A large proportion of DBAs support more than one type of database server and a methodology allows them to apply to all platforms.

There are various configurations per Operating System. For example , on Windows setting the start up service account to Lock Pages in Memory , assists in alleviating paging for SQL Server.  Or check OLTP checklist for DB2 configurations.

 DB2 and Threads

 Threads allow application logic to be separated into multiple execution paths.

Starting a thread is less resource-intensive than starting a process.

Database servers , with heavy Disk IO profiling \ multi-user environment , exploit thread management via managing multiple threads. Read More on How to check for DB2 Direct IO and FILE SYSTEM CACHING - DBA ...

For example, one thread may be CPU-bound while another thread is executing a disk write\read.

Minimise context switching caused by short term segments for threads. For example , Microsoft Windows servers moves threads between processors. From a OS perspective this is efficient , but if the system is under heavy stress , reloading data into the processor cache regularly , can have a negative performance impact.Read more on the DB2 process model - DBA DB2

Note: Prior to enabling affinity options , prove that excessive thread migration between processors causes excessive context switching.

Allow DBMS threads high priority over other applications. DB2 and SQL server have priority options.  Ensure testing ,  as assigning priority can have a negative impact on other instances.

Assign dedicated CPU to DBMS.  Various options such as affinity masking.

Set Multi programming level (MPL) of the DBMS.  Increasing the MPL , will increase the database servers throughput , but at a cost of : increased contention, thrashing , query processing impact

 Weigh up costs versus benefits of using these approaches.

 DB2 and File System

 Plan placement of files.

Create IO profile of files and assign to separate drive\LUN

There are increased benefits of performance but disk\SAN engineers are required to create disk profiles

Read More on DB2 Tuning

Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.

Author: Rambler(


Verify your Comment

Previewing your Comment

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

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.


Post a comment on Database tuning and Operating System Tuning

Comments are moderated, and will not appear until the author has approved them. | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright