100 Tech Tips: #75: Runstats slower than usual?

Does runstats seem to be running longer than usual?  There could be a few explinations for this.  First you should consider if there is a significant amount of data than before, this could just be normal.  If you are in a situation where you will be working with a larger volume of data, you may consider different sampling options with runstats. When collecting detailed statistics collection can consume considerable CPU and memory for large tables, and may take longer than planned. The various sampling options can provide accurate statistics with less resource usage.

However, there may be a chance that the runstats is taking longer simply because the data in the database is highly fragmented.  If a reorganization of the data has not been done in a long time, and the runstats utility seems to take longer and longer, this may be an indication of highly fragmented data.  If you are unsure that a reorg would be of benefit you can run the reorgchk tool.  You may likely find that after a reorg of your key tables, the runstats performance should improve.  

 

I/O is one of the most expensive operations that happens.  Once a table is reorg-ed, the number of small, scattered i/o should go down, and larger, sequential i/o can happen, which would drastically improve IO.  Often the number of trips made to storage will be a big factor in the throughput.  With fewer, trips, getting large chunks of data, the throughput will go up.  When making a lot of small i/o requests (and bouncing around the disk) we have all the overhead with making and processing the storage request  and you can expect this type of frequent activity can consume alot of time.  If there are frequent DML activities like inserts/deletes, fragmentation is going to happen, and that will effect the I/O effectiveness of the runstats if reorg maintenance isn't done.