I have been running system monitor(sysmon) on Sybase ASE 15.7(SP139) on HP-UX(Itanium) with below command:
sp_sysmon "00:10:00"
go
Observed that number of inserts are very high in any time of a day, unable to find the source of it and the details e.g.- table name, database name, program name etc. below is sample output from sysmon in the early morning when there is no load:
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 10.7 n/a 6390 n/a
Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
Fully Logged
APL Heap Table 58665.7 5508.5 35199448 100.0 %
APL Clustered Table 0.0 0.0 7 0.0 %
Data Only Lock Table 13.2 1.2 7918 0.0 %
Fast Bulk Insert 0.0 0.0 0 0.0 %
Fast Log Bulk Insert 0.0 0.0 0 0.0 %
Minimally Logged
APL Heap Table 0.0 0.0 0 0.0 %
APL Clustered Table 0.0 0.0 0 0.0 %
Data Only Lock Table 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 58679.0 5509.8 35207373 100.0 %
It looks like inserts are happening in tempdb however these many inserts are a bit bothering, Appreciate to have expert advice on this.
Best Answer
I found an indirect way of addressing the issue posted in my question as I couldn't find any tool or script which could monitor all the inserts happening at database level, I analyzed the procedure(query) which runs 24 hours a day and is using maximum resource at Production database. To simulate, I executed that procedure manually in Test environment with all its parameters and kept sysmon running in the background for the same period as much it takes for procedure to execute. And I managed to gather close to same number of inserts in the test environment.
In order to prove that this is the only procedure which is doing this much high number of inserts every seconds, I also ran sysmon before and after the problematic procedure and captured that number of inserts too, which is in hundreds and not even in thousands.
Sysmon During procedure execution:
Sysmon Before/After procedure execution(when its not running):
This way, I managed to find the procedure which was doing 60k inserts every second. Also to add, these inserts were because of improper joins of table(containing huge records) and number of inserts were in the form of worktable as below:
I hope above will help others in case they face similar issue which I faced.