Examples of querying the monitoring tables

This section provides examples of querying the monitoring tables.

To return a list of all available monitoring tables:
```
select TableName
from master..monTables
```
To list the columns in a specific monitoring table, enter:
```
select ColumnName, TypeName, Length, Description
from master..monTableColumns
where TableName=”monProcessSQLText”
```
You can determine the columns for any of the monitoring tables by substituting its name in the where clause and running the query.

To determine which currently executing queries are consuming the most CPU, and to list the text of those queries, enter:

select s.SPID, s.CpuTime, t.LineNumber, t.SQLText
from master..monProcessStatement s, master..monProcessSQLText t
where s.SPID = t.SPID
order by s.CpuTime DESC

To determine the hit ratio for the procedure cache for the life of Adaptive Server, enter:
```
select "Procedure Cache Hit Ratio" = (Requests-Loads)*100/Requests
from master..monProcedureCache
```
The following query also provides the hit ratio for a data cache. In this example, the hit ratio is calculated for a 10-minute interval rather than for the entire life of the server:
```
select * into #moncache_prev 
from master..monDataCache
waitfor delay "00:10:00"
select * into #moncache_cur
from master..monDataCache
```
```
select p.CacheName, 
"Hit Ratio"=((c.LogicalReads-p.LogicalReads) - (c.PhysicalReads - p.PhysicalReads))*100 / (c.LogicalReads - p.LogicalReads) 
from #moncache_prev p, #moncache_cur c 
where p.CacheName = c.CacheName
```
To calculate performance metrics for specific sample periods, create a baseline table that stores monitor values at the beginning of the sample period. You can calculate the change in monitor values during the sample period by subtracting the baseline values from the values at the end of the sample period.

Use queries from the following examples to calculate the hit ratio for a data cache, create a baseline, and calculate the amount of activity during the sample period.

To create a stored procedure that prints the executed SQL and the backtrace for any process currently executing stored procedures, enter:

create procedure sp_backtrace @spid int as
begin
select SQLText
from master..monProcessSQLText
where SPID=@spid
print "Stacktrace:"
select ContextID, DBName, OwnerName, ObjectName 
from master..monProcessProcedures
where SPID=@spid
end

To identify any indexes that were used for the table in the database with dbid 5 and object ID 1424005073, enter:

select DBID, ObjectID, LastUsedDate, UsedCount
from master..monOpenObjectActivity
where dbid=5 and ObjectID=1424005073 and IndexID > 1

To determine if you can remove an index because it is not used by the applications running on your server:

Run all queries in your applications that access the table in question. Ensure that Adaptive Server runs long enough so all applications have performed their selects.

To determine whether your application did not use any of the indexes in your database, execute:

select DB = convert(char(20), db_name()),
TableName = convert(char(20), object_name(i.id, db_id())),
IndexName = convert(char(20),i.name),
IndID = i.indid
from master..monOpenObjectActivity a, 
sysindexes i
where a.ObjectID =* i.id
and a.IndexID =* i.indid
and (a.UsedCount = 0 or a.UsedCount is NULL)
and i.indid > 0
and i.id > 99 -- No system tables
order by 2, 4 asc