reset MDA table counters

SAPSupport — Wed, 29 Jun 2022 02:24:04 GMT

Is there any way to reset the content of monStatementCache without server reboot?
especially the counters in NumSearches, HitCount and NumInserts columns.

I did tried dbcc purgesqlcache, but the NumSearches, HitCount and NumInserts still remains

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

Re: reset MDA table counters

SAPSupport — Wed, 29 Jun 2022 02:24:07 GMT

Unfortunately, you cannot reset counters in MDA tables.

Once enabled, the Adaptive Server statement cache stores the SQL text of ad hoc update, delete, and select commands and other statements likely to be reused. When the statement cache is enabled, the query plans for these statements are saved for reuse. When a new statement is issued, Adaptive Server searches the statement cache for a plan to reuse. If Adaptive Server finds a plan to reuse, the statement does not need to be recompiled, which likely leads to improved performance. The monitoring tables include two tables that can be used to analyze the status and performance of the statement cache: monStatementCache provides a summary snapshot of the statement cache, and monCachedStatement shows detailed information about each cached statement. Using literal parameterization allows it to recognize queries that are the same except for differences in literal values, saving recompiling costs while using statement cache. In addition to performance benefits, literal parameterization leads to enormous space reduction while storing the metrics and statements in the cache.

2206989 - Is there a way to reset MDA table counters? - SAP ASE
https://service.sap.com/sap/support/notes/2206989
1962260 - How can statement cache be cleared of stored query plans or one particular query plan to prevent re-use? - ASE
https://service.sap.com/sap/support/notes/1962260
2487703 - When are the monOpenObjectActivity monitoring table values cleared? - SAP ASE
https://service.sap.com/sap/support/notes/2487703

Question Re: reset MDA table counters in Technology Q&A

reset MDA table counters

Re: reset MDA table counters