Caché Monitors SQL Scanner find automatic potential database performance bottlenecks. The cached queries are scanned and on each Query the JOINs are checked.
To understand how the SQL Scanner are working it is required to understand the Caché SQL cache, called Cached Queries. What are Cached Queries?
Each time the Caché database receives a new SQL request, it must evaluate and compile the request. For requests that execute repeatedly, this process can represent substantial overhead. To avoid this overhead, the Caché SQL Server automatically maintains a cache of SQL compiled requests and their associated routines. When the Server receives a new request, it compares the request to its catalog of cached queries. If it finds an identical request, it uses the existing compiled routines.
So, if the database is up and running for some days, Caché Monitor can use this cache of SQL compiled requests and scan each SQL statement. With the information Caché Monitor has collected it is possible to tune the database performance. In the first version of the SQL Scanner he only checks that each way a table is joined has an ‘effective index’. But, please consider: Caché SQL optimizer requires up-to-date statistics (TuneTable) for the table and its indices in order to select a good execution plan.
