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, as well as 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.
|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.