Servers: The Right Compute
Showing results for 
Search instead for 
Did you mean: 

Defrag NonStop Database the Easy Way and Improve Application Performance and Disk Space Utilization


You must have experienced on your Windows PC that the disks, as they get used, become fragmented over time, which also results in poor application performance. There are tools to determine when the disks are fragmented and to reorganize the data to improve disk space utilization and application performance.


On a database system, the same general concept applies. Over time, the data has to be reloaded to improve application performance and disk space utilization. The question is - when is the right time to reload the data?


On HP Integrity NonStop systems, FUP RELOAD physically reorganizes key-sequenced Enscribe files as well as SQL/MP and SQL/MX tables and indexes that are disorganized. However, FUP RELOAD is not a procedure that should be used indiscriminately. Determining when to reload is difficult.


Reload Analyzer (TRA) is a database management tool for NonStop systems that determines when to reload files, tables, indexes or partitions. TRA performs data-block chaining and fragmentation analysis, and generates FUP RELOAD commands, if the percentage of average blocks per chain is below a user-specified threshold (for example, if the percentage of average blocks per chain is under 5%).


TRA performs both vertical analysis of index blocks and horizontal analysis of data blocks, as explained later. TRA supports NonStop Enscribe, SQL/MP and SQL/MX databases. It supports all index and data block sizes, including 32kb block size tables. It can be run any time to analyze a single file, a single partition, all partitions, or a user-specified batch list of files.


What does Reload Analyzer compute?

Reload Analyzer computes and displays the following statistics:

  • Total number of data-block chains
  • Length of longest data-block chain
  • Length of shortest data-block chain
  • Histogram of blocks per chain
  • Average number of blocks per chain


Ideally, the percentage of total blocks in average chain should be 100%. One physical chain is optimum.


Reload Analyzer performs vertical index-block analysis

Too many vertical index-blocks cause poor random access. Reload Analyzer performs vertical index-block analysis to make the random access performance better.


Reload Analyzer performs horizontal data-block analysis

Too many short, broken (non-adjacent) horizontal data-block chains cause excessive physical I/O and/or seeks, and thus, very poor sequential access (up to 128 times slower). Disorganized files cannot take advantage of bulk reads for sequential pre-fetch and bulk writes for sequential updates in files. Reload Analyzer performs in-depth horizontal data-block analysis to make the sequential access performance better.


If you are not already using TRA, I strongly advise you to try it out, and see how it can improve your application performance and disk space utilization by determining when files and tables must be organized. 

0 Kudos
About the Author


Vinay Gupta is an HP Distinguished Technologist and the NonStop Manageability Architect. He joined Tandem in 1994 after graduating from Indian Institute of Technology. He has worked on many NonStop manageability applications over time. He works across various groups within NonStop and HP to ensure consistency and interoperability in manageability interfaces and applications. He is also a member of DMTF workgroups.