You might think you have an OLTP transactional database. Or, you might think you have a Data Warehouse database. But what does your database think? How is it, or the queries within it, really performing?
The Average Result Set Size
The ARSS Formula
Using a database snapshot (Use dbsnap2.txt from the class preparation instructions), simply divide the number of rows selected by the number of SELECT statements (ROWS_SELECTED / SELECT_SQL_STMTS).
A shameless marketing moment
You can learn more about the Average Result Set Size (ARSS) here from Brother-Eagle's Advice, or you can download, install, and run Brother-Eagle Standard Edition ( get it FREE ) to have this and several other metrics computed automatically for you.
FREE is a very good price.
Knowing what kind of database you have is very important as this will influence which key performance indicators and metrics are of most value to ascertaining the health and efficiency of your database.
In future posts, you will often see "If you have an OLTP database, then..." or "If you have a Data Warehouse database, then...".
If you are reading this blog and following along, I hope you'll post a comment and let everyone know what your ARSS is. As we go through the class, it becomes particularly valuable to all participants to have awareness of other participant's calculated values.
If you thought you had an OLTP database, but your ARSS is a rather large number (substantially greater than 10), then you've definitely got an opportunity for improvement (that's a polite synonym for PROBLEM)" /> on your hands. It's possible that your application is retrieving large result sets from the database, then doing its own filtering within the application instead of pushing the filtration down to DB2. This is something that should definitely be investigated.
Until next time,
President & CEO, DBI
IBM GOLD Consultant