DBUTLTY DEFRAG CBS Heuristic Database

Document ID : KB000107692
Last Modified Date : 24/07/2018
Show Technical Document Details
The index of the CBS Heuristic database (DBID 1006) is growing.
Is DEFRAG allowed for system database DBID 1006 and would it help? 
Since DEFRAG will free up space in an index (IXX) when there are lots of deletes. the DEFRAG of 1006 may not be much help since Heuristic data tends to change some but not be deleted very often. 
Heuristics records are not particularly large. There is one record per program and it basically has 10 'buckets' that we can update the key values so we can see when one key has been selected enough times to sync in on it. 

But, we only save heuristic data when they have multiple keys that can't be eliminated and we have to do population counting. So, you likely have a lot of keys in the tables are read that have the same fields in them. The way CBS

works, it tries to eliminate as many keys as possible in favor of better keys. If after it goes through its elimination process there are still multiple keys that could be used, then it does population counting and that is when we save the heuristic data.               

You might want to look at key usage. You may have some keys that are not being used much. If you are getting a lot of population counting being done, that suggests you have a lot of keys with similar fields and too many keys (especially ones that are not used much) that may be adding overhead every time you add or update a record.                                            
So, if you have a lot of keys that aren't being used, you might be able to eliminate some. You can look into using Accounting tables to see if there are keys that are not being used.