Storage Performance Optimization, I/O Efficiency

by Jan 5, 2017

Over time, the blocks in a relational database will typically contain fewer and fewer rows of data making each I/O operation less efficient.

                                                           

Imagine if the storage subsystem shipped a block all the way to the database and it was empty.  A lot of work was expended for no benefit.  A storage block is like a shipping container.  The computer’s operating system does I/O operations on blocks, not rows.  In turn, a block contains rows of data.  The question becomes “how many rows? “.

Relational databases simply mark a row when it is deleted; it still consumes space.  This is called wasted (white) space due to logical deletes.  Applications like PeopleSoft inserts rows into a table, use the data for processing (joins), and then delete all of the rows. 

When data is added, a new block may be allocated to store the new row; a block with one single row.  This is like opening a shipping container halfway around the world, opening the door, and finding one package of noodles.  While everyone loves a steaming hot meal and is grateful for it, let’s make its creation a little more efficient.  Relational databases offer reorgs, rebuilds, and drop/create to pack each block with rows so that each I/O operation is efficient.  Space will be recovered too.    

Minimizing time accessing storage will reduce the execution time of SQL statements.  It will reduce contention and the likelihood that locking & blocking will interfere with processing.  Faster processing means better application performance and more productive users.

                                                        

Reorgs and rebuilds can be completed ad hoc, scripted & scheduled, or automated with a tool like IDERA’s Defrag Manger:  Link to Defrag Manager.

 

World’s largest container ship picture thanks to Wikipedia:  Wikipedia.org link