Performance Advice from a Mysterious Friend
March 5, 2008 Dear Professional
One of your fellow subscribers sent me some performance tips that he says have made enormous performance improvements for his customers. He asked me to share them with you. Here’s the first one:
Change system value QDBFSTCCOL (Database file statistics collection) to *NONE during the day. Change the value to *ALL during non-peak times.
System value QDBFSTCCOL is shipped with a value of *ALL. The database statistics job is always running. Setting the system value to *NONE suspends the collector job. When you change the system value to *ALL, the collector job wakes up and any outstanding statistic requests are subsequently processed. I prefer to wake up the collector job during a period of low activity. Also, all column statistics requests for the same table will be grouped together, which results in a single table scan.