hd_keywords sqlite_memstat {SQLITE_MEMSTAT virtual table} \ {the SQLITE_MEMSTAT extension}

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].

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 [https://sqlite.org/src/file/ext/misc/memstat.c|memstat.c] source file is compiled with the -DSQLITE_ENABLE_ZIPVFS option, then SQLITE_MEMSTAT will also do some [sqlite3_file_control|file-control] calls to extract memory usage information about the [https://www.hwaci.com/sw/sqlite/zipvfs.html | 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 [https://www.hwaci.com/sw/sqlite/zipvfs.html | 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