Stale SQL Statements/Statistics (Or: Why do I have a Huge ^rINDEXSQL Global?)

Looking at my database I see I have a very big ^rINDEXSQL global? Why is that? 😬

In the Management Portal SQL page, under "SQL Statements" I see a 'Clean stale' button - what does this do? 🤔

In the list of Statements some have a 'Location' value and some don't? How is that? 🤨

Well, indeed these are all related.

In general we keep basic statistics for SQL queries that were run. When you purge cached SQL queries, we do not delete the statement entry in the statement list, and keep the statistics (because this might be useful for future comparison). We do "clear" the Location column (as it does not point anymore to an existing cached query).

If you want to clean out these "stale" statements (ones that don't point anywhere anymore) you can press the "Clean stale" button.

This is what this could look like (System Explorer -> SQL -> SQL Statements):

[Note in older versions, e.g. v2020.1 there was a background Task that would clean these statements - "Clean SQL Index - Clean up stale SQL Statement Index entries", see also here, but this has been removed in newer versions, see MAK5205]