Table and index activity can be monitored on a per user basis. This granularity can provide insight into individual user activity, and provide data for evaluating query efficiency. The _UserTableStat and _UserIndexStat VSTs collect the data. For details on the table fields, see Virtual System Tables. You can query the VSTs directly or use PROMON to monitor the activity. For table activity, choose PROMON > R&D > Other Displays > I/O Operations by User by Table; for index activity, choose PROMON > R&D > Other Displays > I/O Operations by User by Index.

There is a cost in memory consumption to monitor I/O by user. The estimated cost is 32 bytes per table per user, and 40 bytes per index per user. Consider a 500 user system with 100 tables and 200 indexes. The total memory cost is as follows:

500 users * 100 tables * 32 bytes/table = 
                              1,600,000 bytes of memory for tables

500 users * 200 indexes * 40/bytes/index = 
                              4,000,000 bytes of memory for indexes

1,600,000 + 4,000,000 = 5,600,000 bytes, or approx 5.3 MB

The base and range of tables and indexes monitored by the _UserTableStat and _UserIndexStat VSTs is established at server startup. By default the first 100 objects are monitored. The table below describes the startup parameters. You can alter the base during runtime, but not the range.

To consume less memory, set the -indexrange and -tablerange parameters to smaller values.