Examples of querying the monitoring tables

This section provides examples of querying the monitoring tables.

This query determines what monitoring tables are available:
```
select *
from master..monTables
```
This query determines which parameters will improve performance by including them in a where clause:
```
select * from master..monTableParameters
where TableName="monOpenObjectActivity"
```
See “Processing information through search arguments” for more information.
This query determines what columns exist in a specific monitoring table:
```
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.
This example determines which queries are consuming the most CPU:
```
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
```
This query also provides the hit ratio over the life of Adaptive Server, and must be rewritten to apply to a specific time period.
This query determines the hit ratios for the data cache for the life of Adaptive Server:
```
select "Procedure Cache Hit Ratio" = (Requests-Loads)*100/Requests
from master..monProcedureCache
```
This query also provides the hit ratio over the life of Adaptive Server, and must be rewritten to apply to a specific time period.

Because the values for LogicalReads and CacheSearches are accumulated over time, you must rewrite this query for a specific sampling period (for example, use the changes of values over a 10-minute period). For example, the following queries the monitoring tables for the sampling period:
```
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)*100 / (c.CacheSearches - 
p.CacheSearches)
from #moncache_prev p, #moncache_cur c
where p.CacheName = c.CacheName
```

This query creates a stored procedure that prints the executed SQL and the backtrace of any stored procedures for diagnostic purposes:

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

Identifies any indices that are not currently in use and can be dropped:

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

To determine if an index can be dropped:

All queries that access the table in question have been run. Typically, you can determine this by ensuring that Adaptive Server has been running long enough so that all applications have performed all of their selects on the table.

Ensure that the object has remained open. That is, the table and its indexes have not been scavenged. You can determine this by looking at the Reused column from the output of sp_monitorconfig for number of open indexes and number of open objects. For example:

exec sp_monitorconfig 'number of open indexes'
exec sp_monitorconfig 'number of open objects'

Usage information at date and time: Oct 22 2002 1:49PM.
Name                   Num_free  Num_active Pct_act  
Max_Used Reused
------------------------- ----------- ----------- ------- 
----     ------
number of open indexes    496         4           0.80 
4        No
Usage information at date and time: Oct 22 2002 1:49PM.
Name                   Num_free  Num_active Pct_act  
Max_Used Reused
------------------------- ----------- ----------- ------- 
----     ------
number of open objects    494         6           1.20     
6          No