Unfortunately, DB2 doesn't directly report Synchronous I/O numbers, but it does report Asynchronous I/O numbers and total counts for I/O to the bufferpools. So, in this formula, we first find the Asynchronous Read Percentage, then subtract this from 100 to find the SRP.

The formula is:
SRP = 100 - (((Asynchronous pool data page reads + Asynchronous pool index page reads) x 100) / (Buffer pool data physical reads + Buffer pool index physical reads))

SRP can be computed at the Database level (use 'dbsnap2.txt' to obtain your values), and should also be computed for each of the database's bufferpools and tablespaces.

SRP Guidelines for OLTP databases:

* If the SRP > 90%, congratulations! You have a database that is making good use of high quality synchronous I/O to precisely retrieve the required result sets.

* If the SRP is in the range of 80-90%, this is good, but it is also likely that the database has some latent physical design tuning opportunities for improvement.

* If the SRP is in the range of 50-80%, the database's performance may be marginal at best. There are definitely physical design opportunities for improvement. If your SRP is in this range, DO NOT WASTE MONEY ON UNNECESSARY HARDWARE UPGRADES!

* If the SRP is less than 50%, performance is probably so poor in the database that phones are ringing like crazy. Transaction response times will be taking too long, and CPU utilization will be very high. If your SRP is in this range, absolutely, positively, under to uncertain circumstances, DO NOT WASTE MONEY ON UNNECESSARY HARDWARE UPGRADES!!!

SRP Guidelines for Data Warehouse Databases:

* Data Warehouse queries tend to do a significant amount of data scanning for queries that return larger result sets (see ARSS in previous blog post). If your SRP is greater than 50%, congratulations - this would be uncommonly high. It also indicates that the database is making good use of indexes to satisfy nested loop joins.
* If the SRP is in the range of 25-50%, this is good, but it is likely that additional physical design tuning could achieve better results.
* If the SRP is less than 25%, the data warehouse database is doing too much scanning and would very likely benefit from physical design improvements. If SRP is in this range, HARDWARE UPGRADES MAY BE INAPPROPRIATE AND AN UNNECESSARY WASTE OF BUDGET RESOURCES.

DB2 9 Quick Tip:

The SRP for the database can be computed with the following query:

select 100 - (((pool_async_data_reads + pool_async_index_reads) * 100 ) / (pool_data_p_reads + pool_index_p_reads + 1)) as SRP from sysibmadm.snapdb where DB_NAME = ‘DBNAME’

A shameless, unselfish, marketing moment:

If you are looking for a good book on Database Physical Design, then check out a new book by Sam Lightstone titled "Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more". Sam is one of the lead brains behind IBM's autonomic tuning initiative, and this book is full of great design ideas. Get it here on Amazon.com.

Just for fun...

Since it is summer in the Northern Hemisphere, many of you may be looking for refreshing ways to cool off. Get 6 Lemons, 3 Cups of Cold Water, and 1/2 Cup of sugar. Juice the lemons, then combine all ingredients in a large pitcher and stir with ice. This makes some great tasting lemonade. If you have children, get them involved in "cooking" this recipe - everyone will have fun.

Until next time,
Cheers,
Scott

Scott Hayes
President & CEO, DBI
www.Database-Performance.info
www.Database-Auditing.info