qua_table_usage

Contains one row for each table and view that has been used, reporting when it was last read, when it was last written to and when its statistics were last calculated, with the counts behind those dates.

Reading this view requires the VIEW SERVER STATE permission.

Column name	Data type	Nullable?	Description
database_id	int	false	Identifier of the database the object belongs to.
object_id	int	false	Object_id of the table or view. Can be used to join with sys.objects.
schema_name	sysname	false	Name of the schema the object belongs to.
name	sysname	false	Name of the object.
type	nchar(2)	false	U = table, V = view.
last_read	datetime	true	When a statement last read the object. NULL when none has.
last_write	datetime	true	When a statement last wrote to the object. NULL when none has.
last_statistics	datetime	true	When the statistics of the object were last calculated. NULL when they never were.
read_count	bigint	false	How many statements have read the object.
write_count	bigint	false	How many statements have written to the object.
writes_since_statistics	bigint	true	How many writes the object has taken since its statistics were calculated. NULL when they never were.

An object nothing has been observed for has no row at all, rather than a row of empty dates. Counting starts when object usage tracking is installed, not when the object was created.

Statements a user or an application runs are counted, whoever issued them. Engine housekeeping is not: refreshing a materialized view does not make its source tables look read, importing metadata records nothing, and calculating statistics moves last_statistics rather than last_read. An object a statement writes to is counted as written and not also as read.

Unlike SQL Server’s sys.dm_db_index_usage_stats, this view covers plain views as well as tables, reports dates for reads and writes separately, and survives a restart.

The statistics catalog

The same statistics date is reported through the SQL Server catalog for tables, so a client that already knows how to read it needs no Querona-specific query:

• sys.stats lists one statistics object per index of a table, numbered by the index identifier sys.indexes reports, and one auto-created object per column that carries statistics, numbered after the indexes and named the way SQL Server names them.

• sys.stats_columns lists the columns each of those objects is built on, an index’s key columns in key order and a column statistic’s single column.

• STATS_DATE(object_id, stats_id) returns when the object’s statistics were last calculated, or NULL when they never were, when the statistics object does not exist, or when the object does not exist.

Querona calculates statistics for a whole object at a time rather than per statistics object, so every statistics object of one table answers STATS_DATE with the same date. A heap has no statistics object, as in SQL Server. Plain views have none either, which is why this view is the only place a view’s statistics date can be read.

Example

Find the objects nothing has read in the last ninety days:

select u.schema_name, u.name, u.type, u.last_read
  from sys.qua_table_usage as u
 where u.last_read is null
    or u.last_read < dateadd(day, -90, getdate())
 order by u.schema_name, u.name;

Find the tables whose statistics are furthest behind their writes:

select u.schema_name, u.name, u.last_statistics, u.writes_since_statistics
  from sys.qua_table_usage as u
 where u.writes_since_statistics > 0
 order by u.writes_since_statistics desc;

See also

• Object usage settings