sa_diagnostic_cachecontents table

The sa_diagnostic_cachecontents table is owned by the dbo user. When diagnostic tracing is enabled, periodic snapshots of the cache contents are taken. The sa_diagnostic_cachecontents table records the number of table pages for each table in the cache at the time the snapshot was taken, and the number of rows in each table. The optimizer can use this information to recreate the conditions under which a query was originally optimized, and then make optimization decisions.

Data in the sa_diagnostic_cachecontents table is updated every 20 seconds, as long as there is query activity.

There are two versions of this table: sa_diagnostic_cachecontents, and sa_tmp_diagnostic_cachecontents.

Columns
Column name Column type Column constraint Table constraints
logging_session_id UNSIGNED INT NOT NULL
"time" TIMESTAMP NOT NULL Primary key.
original_table_object_id UNSIGNED BIGINT NOT NULL Primary key.
pages_in_cache UNSIGNED INT NOT NULL
num_table_pages UNSIGNED INT NOT NULL
num_table_rows UNSIGNED BIGINT NOT NULL

logging_session_id   A number uniquely identifying the logging session during which the diagnostic information was gathered.

"time"   The time at which the snapshot of the cache was taken.

original_table_object_id   The object ID of each table represented in the snapshot.

pages_in_cache   For a specified table in the snapshot, the total number of pages in cache at the moment of the snapshot.

num_table_pages   For a specified table in the snapshot, the total number of pages for the table.

num_table_rows   For a specified table in the snapshot, the total number of rows in the table.