select w.WaitTime, w.Waits, i.Description
from monSysWaits w, monWaitEventInfo i
where w.WaitEventID = i.WaitEventID
order by WaitTime
desc
These can all be ran on ASE version 12.5.0.3 and above.
select Object = convert(char(15), object_name(ObjectID, DBID)), IndexID, OptSelectCount, UsedCount, Operations
from master..monOpenObjectActivity
where ObjectID > 99
and IndexID > 0
order by UsedCount
Note: If you want to check the system tables statistics, just put ObjectID < 100 instead of ObjectID >99.
These can all be ran on ASE version 12.5.0.3 and above.
show_missing_stats shows details of useful statistics missing from SARG / Join columns.
It is important to note there are several situations where show_missing_stats will not print.
If you run a “stored-procedure” and there is a current plan in the master..sysqueryplans”, the “show_missing_statistics” will not print anything. You can do one of 2 things.
Run the stored-procedure with [...]
This query turns out to be helpful in determining which statistics are available for a certain user table (remove the double dash from the commented-out code line and add the table name) or for all tables.
SELECT object_name(s.id) tablename,
c.name column_name,
convert(int,c4)actual_steps,
convert(int,c5)requested_steps,
moddate last_updated
FROM sysstatistics s,syscolumns c
WHERE formatid=100
– AND s.id = object_id(”TABLE_NAME”)
AND s.id=c.id
AND s.id >100
AND s.c4 != null
AND s.c5 [...]