The SQLITE_MEMSTAT Virtual Table

1. Overview

The SQLITE_MEMSTAT extension implements an eponymous-only virtual table that provides SQL access to the sqlite3_status64() and sqlite3_db_status() interfaces.

The SQLITE_STMT extension can also be loaded at run-time by compiling the extension into a shared library or DLL using the source code at https://sqlite.org/src/file/ext/misc/memstat.c and following the instructions for how to compile loadable extensions.

2. Usage

The SQLITE_MEMSTAT virtual table is a read-only table that can be queried to determine performance characteristics (primarily the amount of memory being used) of the current instance of SQLite. The SQLITE_MEMSTATE table is essentially a wrapper around the C-language APIs sqlite3_status64() and sqlite3_db_status(). If the memstat.c source file is compiled with the -DSQLITE_ENABLE_ZIPVFS option, then SQLITE_MEMSTAT will also do some file-control calls to extract memory usage information about the ZIPVFS subsystem, if that subsystem as been licensed, installed, and is in use.

The SQLITE_MEMSTAT table appears to have the following schema:

CREATE TABLE sqlite_memstat(
  name TEXT,
  schema TEXT,
  value INT,
  hiwtr INT
);

Each row of the SQLITE_MEMSTAT table corresponds to a single call to one of the sqlite3_status64() or sqlite3_db_status() interfaces. The NAME column of the row identifies which "verb" was passed to those interfaces. For example, if sqlite3_status64() is invoked with SQLITE_STATUS_MEMORY_USED, then the NAME column is 'MEMORY_USED'. Or if sqlite3_db_status() is invoked with SQLITE_DBSTATUS_CACHE_USED, then the NAME column is "DB_CACHE_USED".

The SCHEMA column is NULL, except for cases when the sqlite3_file_control() interface is used to interrogate the ZIPVFS backend. As this only happens when the memstat.c module is compiled with -DSQLITE_ENABLE_ZIPVFS and when ZIPVFS is in use, SCHEMA is usually NULL.

The VALUE and HIWTR columns report the current value of the measure and its "high-water mark". The high-water mark is the highest value ever seen for the measurement, at least since the last reset. The SQLITE_MEMSTAT virtual table does not provide a mechanism for resetting the high-water mark.

Depending on which parameter is being interrogated, one of the VALUE or HIWTR mark measurements might be undefined. For example, only the high-water mark is meaningful for SQLITE_STATUS_MALLOC_SIZE, and only the current value is meaningful for SQLITE_DBSTATUS_CACHE_USED. For rows where one or the other of VALUE or HIWTR is not meaningful, that value is returned as NULL. the interfaces, with the initial